@Test
public void selectHavingWithInnerSelect() throws CompilationException {
	LocalDateTime now = LocalDateTime.now(clock);
	String formatted = now.format(ISO_LOCAL_DATE_TIME);
	int fourHours = 4 * 60 * 60 * 1000;
	stmtTest("SELECT product_name as product_name, COUNT(product_price) as product_price FROM Ecommerce GROUP BY product_name HAVING COUNT(product_price) > (SELECT avg(transaction_quantity) as avg_quantity FROM order_summary WHERE inserted_on BETWEEN __FLAIR_INTERVAL_OPERATION(NOW(), '-', '4 hours') AND NOW()) LIMIT 20",
			"SELECT product_name as product_name, COUNT(product_price) as product_price FROM Ecommerce GROUP BY product_name HAVING COUNT(product_price) > ( SELECT avg(transaction_quantity) as avg_quantity FROM order_summary WHERE inserted_on BETWEEN (STRINGTOTIMESTAMP('" + formatted + "Z','yyyy-MM-dd''T''HH:mm:ss''Z''') - " + fourHours + ") AND STRINGTOTIMESTAMP('" + formatted + "Z','yyyy-MM-dd''T''HH:mm:ss''Z''')) LIMIT 20");
}
 

@Test
public void testSimpleSelectStmt() throws CompilationException {
	stmtTest("Select data from transactions");
}
 

@Test
public void parseWhereInOneCondition() throws CompilationException {
	stmtTest(
			"SELECT customer_city as customer_city FROM ecommerce WHERE product_id IN ( __FLAIR_CAST(timestamp, '2019-11-03 22:00:00.000000') ) GROUP BY customer_city",
			"SELECT customer_city as customer_city FROM ecommerce WHERE product_id IN (timestamptz '2019-11-03 22:00:00.000000') GROUP BY customer_city");
}
 

protected void stmtTest(String stmt) throws CompilationException {
	stmtTest(stmt, stmt);
}
 

@Test(expected = CompilationException.class)
public void dropTableStatementShouldFail() throws CompilationException {
	stmtTest("drop table transactions");
}
 

@Test(expected = CompilationException.class)
public void dropTableAfterSelectShouldFail() throws CompilationException {
	stmtTest("select * from transactions; drop table transactions");
}
 

@Test
public void testSelectStmtOrderBy2() throws CompilationException {
	stmtTest("Select data, sum(price) as price from transactions order by price desc");
}
 

@Test
public void whereTestCase1() throws CompilationException {
	stmtTest("select * from transactions where data in ('a','b')");
}
 

@Test
public void whereTestCase3() throws CompilationException {
	stmtTest("select * from transactions where price between 100 and 200 and data in ('1','2')");
}
 

@Test
public void whereTestCase5() throws CompilationException {
	stmtTest("select * from transactions where price = 500");
}
 

@Test
public void testSelectStmtGroupByOrderBy1() throws CompilationException {
	stmtTest("Select data, sum(price) as price from transactions group by data order by price asc");
}
 

@Test
public void testSelectStmtGroupByOrderBy2() throws CompilationException {
	stmtTest("Select data, sum(price) as price from transactions group by data order by price desc");
}
 

@Test
public void whereTestCase1() throws CompilationException {
	stmtTest("select * from transactions where data in ('a','b')");
}
 

@Test
public void whereTestCase7() throws CompilationException {
	stmtTest("select * from transactions where data not in ('1','2')");
}
 

@Test
public void showTablesLikeLimit() throws CompilationException {
	stmtTest("show tables like '%pera%' limit 5", "SHOW_TABLES_AND_STREAMS");
}
 

@Test
public void parseFlairIntervalOperation() throws CompilationException {
	stmtTest(
			"SELECT updated_on as updated_on, COUNT(transaction_quantity) as transaction_quantity FROM shipment3 WHERE updated_on BETWEEN NOW() AND __FLAIR_INTERVAL_OPERATION(NOW(), '-', '4 hours') GROUP BY updated_on ORDER BY transaction_quantity DESC,updated_on DESC LIMIT 20 OFFSET 0",
			"SELECT updated_on as updated_on, COUNT(transaction_quantity) as transaction_quantity FROM shipment3 WHERE updated_on BETWEEN CURRENT_TIMESTAMP() AND DATEADD(hour, -4, CURRENT_TIMESTAMP()) GROUP BY updated_on ORDER BY transaction_quantity DESC,updated_on DESC LIMIT 20 OFFSET 0");
}
 

@Test
public void whereTestCase1() throws CompilationException {
	stmtTest("select * from transactions where data in ('a','b')");
}
 

@Test
public void whereTestCase2() throws CompilationException {
	stmtTest("select * from transactions where price between 100 and 200");
}
 

@Test
public void parseWhereInLongExpression() throws CompilationException {
	stmtTest(
			"SELECT customer_city as customer_city FROM ecommerce WHERE product_id IN (__FLAIR_CAST(timestamp, '2019-11-03 22:00:00.000000'), 1231) GROUP BY customer_city",
			"SELECT customer_city as customer_city FROM ecommerce WHERE product_id IN (timestamptz '2019-11-03 22:00:00.000000',1231) GROUP BY customer_city");
}
 

@Test
public void parseDistinctCountFunction() throws CompilationException {
	stmtTest("select column1, distinct_count(column2) from my_table where a = 1",
			"select column1, count(distinct column2) from my_table where a = 1");
}
 

@Test
public void whereTestCase5() throws CompilationException {
	stmtTest("select * from transactions where price = 500");
}
 

@Test
public void testSimpleSelectStmt() throws CompilationException {
	stmtTest("Select data from transactions");
}
 

@Test
public void parseLimitAndOffset() throws CompilationException {
	stmtTest("select column1 from my_table where a = 1 limit 10 offset 53",
			"select column1 from my_table where a = 1 limit 10 offset 53");
}
 

@Test
public void flairTruncWithTimestamp() throws CompilationException {
	stmtTest("select __FLAIR_TRUNC(inserted_on, timestamp) from transactions where price = 500 and __FLAIR_TRUNC(udpated_on, timestamp) > 0",
			"select date_trunc('second', inserted_on) from transactions where price = 500 and date_trunc('second', udpated_on) > 0");
}
 

@Test
public void flairRaw() throws CompilationException {
	stmtTest(
			"SELECT updated_on as updated_on FROM __FLAIR_RAW([[select * from myql_tables mt left join mysql_schema ms on ms.id = mt.request_id where mt.col = 123 order by limit 11]]) as subquery WHERE column1 = 123",
			"SELECT updated_on as updated_on FROM (select * from myql_tables mt left join mysql_schema ms on ms.id = mt.request_id where mt.col = 123 order by limit 11) as subquery WHERE column1 = 123");
}
 

@Test
public void testSelectStmtGroupBy() throws CompilationException {
	stmtTest("Select data, sum(price) as price from transactions group by data");
}
 

@Test
public void showTablesLike() throws CompilationException {
	stmtTest("show tables like '%pera%'", "SHOW TABLES like '*pera*'");
}
 

@Test
public void parseDistinctCountFunction() throws CompilationException {
	stmtTest("select column1, distinct_count(column2) from my_table where a = 1",
			"select column1, count(distinct column2) from my_table where a = 1");
}
 

@Test
public void selectWithQuotes() throws CompilationException {
	stmtTest("SELECT replace(product_name,'''','''Women''','Aster''isk','''More''test''') FROM ecommerce",
			"SELECT replace(product_name,'''','''Women''','Aster''isk','''More''test''') FROM ecommerce");
}
 

@Test
public void whereTestCase8() throws CompilationException {
	stmtTest("select * from transactions where data like '%pera%'");
}