com.project.bi.exceptions.CompilationException Java Examples

@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 parseWhereInOneCondition() throws CompilationException {
	stmtTest(
			"SELECT customer_city as customer_city FROM ecommerce WHERE product_id IN ( __FLAIR_CAST(timestamp, 121) ) GROUP BY customer_city",
			"SELECT customer_city as customer_city FROM ecommerce WHERE product_id IN (to_timestamp(121,'YYYY-MM-DD HH24:MI:SS.US')) GROUP BY customer_city");
}
 

@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 CAST(inserted_on as date) from transactions where price = 500 and CAST(udpated_on as date) > 0");
}
 

@Test
public void selectHaving() throws CompilationException {
	stmtTest(
			"SELECT product_name as product_name, COUNT(product_price) as product_price FROM Ecommerce GROUP BY product_name HAVING (COUNT(product_price) > 1000) LIMIT 20",
			"SELECT product_name as product_name, COUNT(product_price) as product_price FROM Ecommerce GROUP BY product_name HAVING (COUNT(product_price) > 1000) LIMIT 20");
}
 

@Test
public void randTest() throws CompilationException {
	stmtTest("select sum(price * random()) as price from transactions where data like '%pera%'",
			"select sum(price * random()) as price from transactions where data like '%pera%'");
}
 

@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 parseFlairIntervalAndCastOperation() 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(__FLAIR_CAST(timestamp, '2019-11-03 22:00:00.000000'), '-', '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 NOW() AND (STR_TO_DATE('2019-11-03 22:00:00.000000','%Y-%m-%d %H:%i:%s.%f') - interval 4 hour) 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 whereTestCase7() throws CompilationException {
	stmtTest("select * from transactions where data not in ('1','2')");
}
 

@Test
public void parseWhereIn() throws CompilationException {
	stmtTest(
			"SELECT customer_city as customer_city,COUNT(order_item_quantity) as order_item_quantity FROM ecommerce WHERE product_id IN (1073) GROUP BY customer_city ORDER BY order_item_quantity DESC,customer_city DESC LIMIT 20 OFFSET 0",
			"SELECT customer_city as customer_city, COUNT(order_item_quantity) as order_item_quantity FROM ecommerce WHERE product_id IN (1073) GROUP BY customer_city ORDER BY order_item_quantity DESC,customer_city DESC LIMIT 20 OFFSET 0");
}
 

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

@Test
public void parseFlairTypeCastLike() throws CompilationException {
	stmtTest(
			"SELECT updated_on as updated_on,COUNT(transaction_quantity) as transaction_quantity FROM shipment3 WHERE UPPER(__FLAIR_CAST(flair_string, product_id)) LIKE UPPER('%123%') 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 UPPER(CAST(product_id as TEXT)) LIKE UPPER('%123%') GROUP BY updated_on ORDER BY transaction_quantity DESC,updated_on DESC LIMIT 20 OFFSET 0");
}
 

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

@Test
public void parseWhereIn() throws CompilationException {
	stmtTest(
			"SELECT customer_city as customer_city,COUNT(order_item_quantity) as order_item_quantity FROM ecommerce WHERE product_id IN (1073) GROUP BY customer_city ORDER BY order_item_quantity DESC,customer_city DESC LIMIT 20 OFFSET 0",
			"SELECT customer_city as customer_city, COUNT(order_item_quantity) as order_item_quantity FROM ecommerce WHERE product_id IN (1073) GROUP BY customer_city ORDER BY order_item_quantity DESC,customer_city DESC LIMIT 20 OFFSET 0");
}
 

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

@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 inserted_on from transactions where price = 500 and udpated_on > 0");
}
 

@Test
public void parseWhereIn() throws CompilationException {
	stmtTest(
			"SELECT customer_city as customer_city,COUNT(order_item_quantity) as order_item_quantity FROM ecommerce WHERE product_id IN (1073) GROUP BY customer_city ORDER BY order_item_quantity DESC,customer_city DESC LIMIT 20 OFFSET 0",
			"SELECT customer_city as customer_city, COUNT(order_item_quantity) as order_item_quantity FROM ecommerce WHERE product_id IN (1073) GROUP BY customer_city ORDER BY order_item_quantity DESC,customer_city DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY");
}
 

@Test
public void parseFlairIntervalAndCastOperation() 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(__FLAIR_CAST(timestamp,'2019-11-03 22:00:00.000000'), '-', '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 sysdate AND (to_timestamp('2019-11-03 22:00:00.000000','YYYY-MM-DD HH24:MI:SS.FF6') - interval '4' hour(1)) GROUP BY updated_on ORDER BY transaction_quantity DESC,updated_on DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY");
}
 

@Test
public void showTablesLikeLimit() throws CompilationException {
	stmtTest("show tables like '%pera%' limit 5",
			"SELECT tablename FROM pg_catalog.pg_tables WHERE tablename LIKE '%pera%' LIMIT 5");
}
 

@Test
public void showTablesLike() throws CompilationException {
	stmtTest("show tables like '%pera%'",
			"SELECT tablename FROM pg_catalog.pg_tables WHERE tablename LIKE '%pera%'");
}
 

@Test
public void parseFlairNowFunction() throws CompilationException {
	stmtTest("select column1, __FLAIR_NOW(test) from my_table where a = 1",
			"select column1, sysdate from my_table where a = 1");
}
 

@Test
public void parseNowFunction() throws CompilationException {
	stmtTest("select column1, now() from my_table where a = 1",
			"select column1, sysdate from my_table where a = 1");
}
 

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

@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 parseFlairTypeCast() throws CompilationException {
	stmtTest(
			"SELECT updated_on as updated_on,COUNT(transaction_quantity) as transaction_quantity FROM shipment3 WHERE updated_on >= __FLAIR_CAST(timestamp, '2019-11-03 22:00:00.000000') 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 >= STR_TO_DATE('2019-11-03 22:00:00.000000','%Y-%m-%d %H:%i:%s.%f') GROUP BY updated_on ORDER BY transaction_quantity DESC,updated_on DESC LIMIT 20 OFFSET 0");
}
 

@Test
public void selectWithLimit() throws CompilationException {
	stmtTest("SELECT * FROM ORDERS LIMIT 1", "SELECT * FROM ORDERS FETCH NEXT 1 ROWS ONLY");
}
 

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

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

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