You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@kylin.apache.org by lu...@apache.org on 2015/01/07 15:46:27 UTC
[06/51] [partial] incubator-kylin git commit: migrate repo from
github.com to apache git
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query54.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query54.sql b/query/src/test/resources/query/sql/query54.sql
new file mode 100644
index 0000000..8404d06
--- /dev/null
+++ b/query/src/test/resources/query/sql/query54.sql
@@ -0,0 +1,7 @@
+select test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ where 1 <> 1
+ group by test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query55.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query55.sql b/query/src/test/resources/query/sql/query55.sql
new file mode 100644
index 0000000..05c115b
--- /dev/null
+++ b/query/src/test/resources/query/sql/query55.sql
@@ -0,0 +1 @@
+select count(*) as c from test_cal_dt where extract(YEAR from test_cal_dt.cal_dt) = 2012
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query56.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query56.sql b/query/src/test/resources/query/sql/query56.sql
new file mode 100644
index 0000000..62fb8b3
--- /dev/null
+++ b/query/src/test/resources/query/sql/query56.sql
@@ -0,0 +1,8 @@
+select test_cal_dt.week_beg_dt, sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ where
+ extract(MONTH from test_cal_dt.week_beg_dt) = 12
+ group by test_cal_dt.week_beg_dt
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query57.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query57.sql b/query/src/test/resources/query/sql/query57.sql
new file mode 100644
index 0000000..492a5b2
--- /dev/null
+++ b/query/src/test/resources/query/sql/query57.sql
@@ -0,0 +1,8 @@
+select test_cal_dt.week_beg_dt, sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ where
+ (test_kylin_fact.lstg_format_name='FP-GTC') and extract(MONTH from test_cal_dt.week_beg_dt) = 12
+ group by test_cal_dt.week_beg_dt
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query58.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query58.sql b/query/src/test/resources/query/sql/query58.sql
new file mode 100644
index 0000000..d25fd23
--- /dev/null
+++ b/query/src/test/resources/query/sql/query58.sql
@@ -0,0 +1,8 @@
+select sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ where
+ (test_kylin_fact.lstg_format_name='FP-GTC') and extract(MONTH from test_cal_dt.week_beg_dt) = 12
+ group by test_kylin_fact.lstg_format_name
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query59.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query59.sql b/query/src/test/resources/query/sql/query59.sql
new file mode 100644
index 0000000..d2db28c
--- /dev/null
+++ b/query/src/test/resources/query/sql/query59.sql
@@ -0,0 +1 @@
+select sum(price) as GVM,lstg_format_name from test_kylin_fact group by lstg_format_name
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query60.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query60.sql b/query/src/test/resources/query/sql/query60.sql
new file mode 100644
index 0000000..73283f0
--- /dev/null
+++ b/query/src/test/resources/query/sql/query60.sql
@@ -0,0 +1,6 @@
+select test_kylin_fact.cal_dt, sum(test_kylin_fact.price) as sum_price, count(1) as cnt_1
+from test_kylin_fact
+left join test_cal_dt on test_kylin_fact.cal_dt=test_cal_dt.cal_dt
+group by test_kylin_fact.cal_dt
+order by 2 desc
+limit 3
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query61.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query61.sql b/query/src/test/resources/query/sql/query61.sql
new file mode 100644
index 0000000..acc3635
--- /dev/null
+++ b/query/src/test/resources/query/sql/query61.sql
@@ -0,0 +1,6 @@
+select count(1) as cnt_1
+from test_kylin_fact
+left join test_cal_dt on test_kylin_fact.cal_dt=test_cal_dt.cal_dt
+group by test_kylin_fact.cal_dt
+order by 1 desc
+limit 4
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query62.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query62.sql b/query/src/test/resources/query/sql/query62.sql
new file mode 100644
index 0000000..b9bbd0b
--- /dev/null
+++ b/query/src/test/resources/query/sql/query62.sql
@@ -0,0 +1,3 @@
+select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV , min(cal_dt) as min_cal_dt
+ , count(*) as TRANS_CNT from test_kylin_fact
+ group by test_kylin_fact.lstg_format_name having sum(price)>5000 and count(*)>72
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query63.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query63.sql b/query/src/test/resources/query/sql/query63.sql
new file mode 100644
index 0000000..13a26bc
--- /dev/null
+++ b/query/src/test/resources/query/sql/query63.sql
@@ -0,0 +1,2 @@
+select min(cal_dt) as min_cal_dt
+ from test_kylin_fact
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query64.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query64.sql b/query/src/test/resources/query/sql/query64.sql
new file mode 100644
index 0000000..bbf99a8
--- /dev/null
+++ b/query/src/test/resources/query/sql/query64.sql
@@ -0,0 +1,3 @@
+ select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV , min(cal_dt) as min_cal_dt
+ , count(*) as TRANS_CNT from test_kylin_fact
+ group by test_kylin_fact.lstg_format_name
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query65.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query65.sql b/query/src/test/resources/query/sql/query65.sql
new file mode 100644
index 0000000..b44cb1b
--- /dev/null
+++ b/query/src/test/resources/query/sql/query65.sql
@@ -0,0 +1 @@
+select min(cal_dt) as min_cal_dt, max(cal_dt) as max_cal_dt from test_kylin_fact
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query66.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query66.sql b/query/src/test/resources/query/sql/query66.sql
new file mode 100644
index 0000000..5efc8eb
--- /dev/null
+++ b/query/src/test/resources/query/sql/query66.sql
@@ -0,0 +1,2 @@
+select test_kylin_fact.cal_dt, max(test_kylin_fact.cal_dt) as mmm from test_kylin_fact left join test_cal_dt
+ on test_kylin_fact.cal_dt=test_cal_dt.cal_dt group by test_kylin_fact.cal_dt order by 2 desc limit 7
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query67.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query67.sql b/query/src/test/resources/query/sql/query67.sql
new file mode 100644
index 0000000..b50b518
--- /dev/null
+++ b/query/src/test/resources/query/sql/query67.sql
@@ -0,0 +1,4 @@
+SELECT (CASE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" WHEN 'Auction' THEN '111' ELSE '222' END) AS "LSTG_FORMAT_NAME__group_",
+ SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok"
+FROM "TEST_KYLIN_FACT" "TEST_KYLIN_FACT"
+group by (CASE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" WHEN 'Auction' THEN '111' ELSE '222' END) ORDER BY 1 ASC
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query68.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query68.sql b/query/src/test/resources/query/sql/query68.sql
new file mode 100644
index 0000000..635c131
--- /dev/null
+++ b/query/src/test/resources/query/sql/query68.sql
@@ -0,0 +1,8 @@
+select cal_dt as bb , sum(price) as aa from test_kylin_fact fact
+inner join (
+
+
+select count(1) as cnt, min(cal_dt) as "mmm", cal_dt as dt from test_kylin_fact group by cal_dt order by 2 desc limit 10
+
+
+) t0 on (fact.cal_dt = t0.dt) group by cal_dt
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query69.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query69.sql b/query/src/test/resources/query/sql/query69.sql
new file mode 100644
index 0000000..5491078
--- /dev/null
+++ b/query/src/test/resources/query/sql/query69.sql
@@ -0,0 +1,7 @@
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT
+ from test_kylin_fact
+ where (CASE WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" IN ('Auction', 'FP-GTC')) THEN 'Auction' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END) = 'Auction'
+ group by lstg_format_name
+ order by sum(price)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query70.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query70.sql b/query/src/test/resources/query/sql/query70.sql
new file mode 100644
index 0000000..413fb12
--- /dev/null
+++ b/query/src/test/resources/query/sql/query70.sql
@@ -0,0 +1,6 @@
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT
+ from test_kylin_fact
+ where "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" = 'Auction' and (CASE WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" IN ('Auction', 'FP-GTC')) THEN 'Auction' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END) = 'Auction'
+ group by lstg_format_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query71.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query71.sql b/query/src/test/resources/query/sql/query71.sql
new file mode 100644
index 0000000..e6b69c9
--- /dev/null
+++ b/query/src/test/resources/query/sql/query71.sql
@@ -0,0 +1,6 @@
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT
+ from test_kylin_fact
+ where "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" in ('Auction', 'ABIN') and (CASE WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" IN ('Auction', 'FP-GTC')) THEN 'Auction' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END) = 'Auction'
+ group by lstg_format_name
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query72.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query72.sql b/query/src/test/resources/query/sql/query72.sql
new file mode 100644
index 0000000..14ea2e9
--- /dev/null
+++ b/query/src/test/resources/query/sql/query72.sql
@@ -0,0 +1,6 @@
+select test_cal_dt.week_beg_dt, test_kylin_fact.lstg_format_name, sum(test_kylin_fact.price) as GMV, count(*) as TRANS_CNT
+ from test_kylin_fact
+ inner join test_cal_dt
+ on test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ where test_kylin_fact.lstg_format_name='FP-GTC' or extract(MONTH from test_cal_dt.week_beg_dt) = 12
+ group by test_cal_dt.week_beg_dt, test_kylin_fact.lstg_format_name
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query73.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query73.sql b/query/src/test/resources/query/sql/query73.sql
new file mode 100644
index 0000000..d5f1cbb
--- /dev/null
+++ b/query/src/test/resources/query/sql/query73.sql
@@ -0,0 +1 @@
+select min("t"."s") as min_pr from (select max(price) as "s" from test_kylin_fact group by lstg_format_name) "t" having (count(1) > 0)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query74.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query74.sql b/query/src/test/resources/query/sql/query74.sql
new file mode 100644
index 0000000..111804e
--- /dev/null
+++ b/query/src/test/resources/query/sql/query74.sql
@@ -0,0 +1 @@
+select min("t"."s") as min_x, max("t"."s") as max_x from (select max(price) as "s" from test_kylin_fact group by lstg_format_name) "t" having ( count(1) > 0)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query75.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query75.sql b/query/src/test/resources/query/sql/query75.sql
new file mode 100644
index 0000000..d47f65e
--- /dev/null
+++ b/query/src/test/resources/query/sql/query75.sql
@@ -0,0 +1,4 @@
+select lstg_format_name, sum(price) as GMV
+ from test_kylin_fact
+ where (NOT ((CASE WHEN (lstg_format_name IS NULL) THEN 1 WHEN NOT (lstg_format_name IS NULL) THEN 0 ELSE NULL END) <> 0))
+ group by lstg_format_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql/query76.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql/query76.sql b/query/src/test/resources/query/sql/query76.sql
new file mode 100644
index 0000000..32b1582
--- /dev/null
+++ b/query/src/test/resources/query/sql/query76.sql
@@ -0,0 +1,4 @@
+select lstg_format_name, sum(price) as GMV
+ from test_kylin_fact
+ group by lstg_format_name, SLR_SEGMENT_CD
+ having SLR_SEGMENT_CD > 0
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_all_types/query00.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_all_types/query00.sql b/query/src/test/resources/query/sql_all_types/query00.sql
new file mode 100644
index 0000000..3e02614
--- /dev/null
+++ b/query/src/test/resources/query/sql_all_types/query00.sql
@@ -0,0 +1 @@
+select ML_tinyint, sum(price) as GMV from test_kylin_fact group by ML_tinyint order by ML_tinyint
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_all_types/query01.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_all_types/query01.sql b/query/src/test/resources/query/sql_all_types/query01.sql
new file mode 100644
index 0000000..c3a5bef
--- /dev/null
+++ b/query/src/test/resources/query/sql_all_types/query01.sql
@@ -0,0 +1 @@
+select ML_smallint, sum(price) as GMV from test_kylin_fact group by ML_smallint order by ML_smallint
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_all_types/query02.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_all_types/query02.sql b/query/src/test/resources/query/sql_all_types/query02.sql
new file mode 100644
index 0000000..710d991
--- /dev/null
+++ b/query/src/test/resources/query/sql_all_types/query02.sql
@@ -0,0 +1 @@
+select ML_int, sum(price) as GMV from test_kylin_fact group by ML_int order by ML_int
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_all_types/query03.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_all_types/query03.sql b/query/src/test/resources/query/sql_all_types/query03.sql
new file mode 100644
index 0000000..34dd85e
--- /dev/null
+++ b/query/src/test/resources/query/sql_all_types/query03.sql
@@ -0,0 +1 @@
+select ML_bigint, sum(price) as GMV from test_kylin_fact group by ML_bigint order by ML_bigint
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_all_types/query04.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_all_types/query04.sql b/query/src/test/resources/query/sql_all_types/query04.sql
new file mode 100644
index 0000000..23f9081
--- /dev/null
+++ b/query/src/test/resources/query/sql_all_types/query04.sql
@@ -0,0 +1 @@
+select ML_float, sum(price) as GMV from test_kylin_fact group by ML_float order by ML_float
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_all_types/query05.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_all_types/query05.sql b/query/src/test/resources/query/sql_all_types/query05.sql
new file mode 100644
index 0000000..5614706
--- /dev/null
+++ b/query/src/test/resources/query/sql_all_types/query05.sql
@@ -0,0 +1 @@
+select ML_double, sum(price) as GMV from test_kylin_fact group by ML_double order by ML_double
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_all_types/query06.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_all_types/query06.sql b/query/src/test/resources/query/sql_all_types/query06.sql
new file mode 100644
index 0000000..1f49d51
--- /dev/null
+++ b/query/src/test/resources/query/sql_all_types/query06.sql
@@ -0,0 +1 @@
+select ML_decimal, sum(price) as GMV from test_kylin_fact group by ML_decimal order by ML_decimal
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_all_types/query07.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_all_types/query07.sql b/query/src/test/resources/query/sql_all_types/query07.sql
new file mode 100644
index 0000000..9a4b4af
--- /dev/null
+++ b/query/src/test/resources/query/sql_all_types/query07.sql
@@ -0,0 +1 @@
+select ML_boolean, sum(price) as GMV from test_kylin_fact group by ML_boolean order by ML_boolean
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_all_types/query08.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_all_types/query08.sql b/query/src/test/resources/query/sql_all_types/query08.sql
new file mode 100644
index 0000000..dd8fef0
--- /dev/null
+++ b/query/src/test/resources/query/sql_all_types/query08.sql
@@ -0,0 +1 @@
+select ML_varchar, sum(price) as GMV from test_kylin_fact group by ML_varchar order by ML_varchar
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_all_types/query09.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_all_types/query09.sql b/query/src/test/resources/query/sql_all_types/query09.sql
new file mode 100644
index 0000000..d2cc8d8
--- /dev/null
+++ b/query/src/test/resources/query/sql_all_types/query09.sql
@@ -0,0 +1 @@
+select ML_char, sum(price) as GMV from test_kylin_fact group by ML_char order by ML_char
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_casewhen/query01.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_casewhen/query01.sql b/query/src/test/resources/query/sql_casewhen/query01.sql
new file mode 100644
index 0000000..825cad6
--- /dev/null
+++ b/query/src/test/resources/query/sql_casewhen/query01.sql
@@ -0,0 +1,8 @@
+SELECT lstg_format_name
+FROM test_kylin_fact
+WHERE ( NOT ( ( CASE
+ WHEN ( lstg_format_name IS NULL ) THEN 1
+ WHEN NOT ( lstg_format_name IS NULL ) THEN 0
+ ELSE NULL
+ END ) <> 0 ) )
+GROUP BY lstg_format_name
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_casewhen/query02.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_casewhen/query02.sql b/query/src/test/resources/query/sql_casewhen/query02.sql
new file mode 100644
index 0000000..986fea1
--- /dev/null
+++ b/query/src/test/resources/query/sql_casewhen/query02.sql
@@ -0,0 +1,4 @@
+SELECT "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME"
+FROM TEST_KYLIN_FACT
+WHERE ((CASE WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" = 'Auction') THEN 'Auction1' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END) = 'Auction1')
+GROUP BY "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME"
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_casewhen/query03.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_casewhen/query03.sql b/query/src/test/resources/query/sql_casewhen/query03.sql
new file mode 100644
index 0000000..2c3881f
--- /dev/null
+++ b/query/src/test/resources/query/sql_casewhen/query03.sql
@@ -0,0 +1,7 @@
+SELECT "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME"
+FROM TEST_KYLIN_FACT
+WHERE (CASE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME"
+ WHEN 'Auction' THEN '111'
+ WHEN 'FP-GTC' THEN '222'
+ ELSE '999' END) = '111'
+GROUP BY "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME"
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_casewhen/query55.sql.disable
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_casewhen/query55.sql.disable b/query/src/test/resources/query/sql_casewhen/query55.sql.disable
new file mode 100644
index 0000000..ea73cd8
--- /dev/null
+++ b/query/src/test/resources/query/sql_casewhen/query55.sql.disable
@@ -0,0 +1,4 @@
+SELECT (CASE WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" IN ('Auction', 'FP-GTC')) THEN 'Auction' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END) AS "LSTG_FORMAT_NAME__group_",
+ SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok"
+FROM "TEST_KYLIN_FACT" "TEST_KYLIN_FACT"
+GROUP BY (CASE WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" IN ('Auction', 'FP-GTC')) THEN 'Auction' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_casewhen/query56.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_casewhen/query56.sql b/query/src/test/resources/query/sql_casewhen/query56.sql
new file mode 100644
index 0000000..07f5db6
--- /dev/null
+++ b/query/src/test/resources/query/sql_casewhen/query56.sql
@@ -0,0 +1,4 @@
+ SELECT (CASE WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" = 'Auction') THEN 'Auction2' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END) AS "LSTG_FORMAT_NAME__group_",
+ SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok"
+FROM "TEST_KYLIN_FACT" "TEST_KYLIN_FACT"
+GROUP BY (CASE WHEN ("TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" = 'Auction') THEN 'Auction2' ELSE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" END)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_derived/query01.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_derived/query01.sql b/query/src/test/resources/query/sql_derived/query01.sql
new file mode 100644
index 0000000..c471835
--- /dev/null
+++ b/query/src/test/resources/query/sql_derived/query01.sql
@@ -0,0 +1,29 @@
+SELECT
+ test_kylin_fact.seller_id
+ ,test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_sites.site_id
+ ,test_sites.cre_user
+ ,sum(test_kylin_fact.price) as GMV, count(*) as TRANS_CNT
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ inner JOIN test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ where test_kylin_fact.seller_id = 10000002
+ group by
+ test_kylin_fact.seller_id
+ ,test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_sites.site_id
+ ,test_sites.cre_user
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_derived/query02.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_derived/query02.sql b/query/src/test/resources/query/sql_derived/query02.sql
new file mode 100644
index 0000000..6c8b2da
--- /dev/null
+++ b/query/src/test/resources/query/sql_derived/query02.sql
@@ -0,0 +1,29 @@
+SELECT
+ test_kylin_fact.seller_id
+ ,test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_sites.site_id
+ ,test_sites.cre_user
+ ,sum(test_kylin_fact.price) as GMV, count(*) as TRANS_CNT
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ inner JOIN test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ where test_kylin_fact.seller_id = 10000002 and test_sites.site_id=0
+ group by
+ test_kylin_fact.seller_id
+ ,test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_sites.site_id
+ ,test_sites.cre_user
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_derived/query03.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_derived/query03.sql b/query/src/test/resources/query/sql_derived/query03.sql
new file mode 100644
index 0000000..8c7938a
--- /dev/null
+++ b/query/src/test/resources/query/sql_derived/query03.sql
@@ -0,0 +1,3 @@
+SELECT distinct LSTG_FORMAT_NAME from test_kylin_fact
+
+
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_derived/query04.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_derived/query04.sql b/query/src/test/resources/query/sql_derived/query04.sql
new file mode 100644
index 0000000..8ab1f88
--- /dev/null
+++ b/query/src/test/resources/query/sql_derived/query04.sql
@@ -0,0 +1,2 @@
+SELECT site_id, site_name, cre_user from test_sites
+
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_derived/query05.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_derived/query05.sql b/query/src/test/resources/query/sql_derived/query05.sql
new file mode 100644
index 0000000..9318e05
--- /dev/null
+++ b/query/src/test/resources/query/sql_derived/query05.sql
@@ -0,0 +1,5 @@
+SELECT test_sites.site_name, test_kylin_fact.lstg_format_name, sum(test_kylin_fact.price) as GMV, count(*) as TRANS_CNT
+ FROM test_kylin_fact
+ inner JOIN test_sites ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ GROUP BY
+ test_sites.site_name, test_kylin_fact.lstg_format_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_derived/query06.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_derived/query06.sql b/query/src/test/resources/query/sql_derived/query06.sql
new file mode 100644
index 0000000..2d70f45
--- /dev/null
+++ b/query/src/test/resources/query/sql_derived/query06.sql
@@ -0,0 +1,2 @@
+SELECT distinct cal_dt from test_kylin_fact
+
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_derived/query07.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_derived/query07.sql b/query/src/test/resources/query/sql_derived/query07.sql
new file mode 100644
index 0000000..e3ef635
--- /dev/null
+++ b/query/src/test/resources/query/sql_derived/query07.sql
@@ -0,0 +1,4 @@
+select cal_dt, lstg_format_name, sum(price) as GMV
+ from test_kylin_fact
+ where cal_dt=date '2013-05-06'
+ group by cal_dt, lstg_format_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_derived/query08.sql.disabled
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_derived/query08.sql.disabled b/query/src/test/resources/query/sql_derived/query08.sql.disabled
new file mode 100644
index 0000000..17cdc4e
--- /dev/null
+++ b/query/src/test/resources/query/sql_derived/query08.sql.disabled
@@ -0,0 +1 @@
+select distinct leaf_categ_id, lstg_site_id from test_kylin_fact
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_derived/query09.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_derived/query09.sql b/query/src/test/resources/query/sql_derived/query09.sql
new file mode 100644
index 0000000..0ecbb5b
--- /dev/null
+++ b/query/src/test/resources/query/sql_derived/query09.sql
@@ -0,0 +1 @@
+select distinct leaf_categ_id, site_id from test_category_groupings
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_derived/query10.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_derived/query10.sql b/query/src/test/resources/query/sql_derived/query10.sql
new file mode 100644
index 0000000..5505b67
--- /dev/null
+++ b/query/src/test/resources/query/sql_derived/query10.sql
@@ -0,0 +1,26 @@
+SELECT
+ test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.upd_user
+ ,test_category_groupings.upd_date
+ ,test_kylin_fact.leaf_categ_id
+ ,test_category_groupings.leaf_categ_id
+ ,test_kylin_fact.lstg_site_id
+ ,test_category_groupings.site_id
+ ,sum(price) as GMV, count(*) as TRANS_CNT
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id
+ AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ where
+ test_category_groupings.upd_date='2012-09-11 20:26:04'
+ group by test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.upd_user
+ ,test_category_groupings.upd_date
+ ,test_kylin_fact.leaf_categ_id
+ ,test_category_groupings.leaf_categ_id
+ ,test_kylin_fact.lstg_site_id
+ ,test_category_groupings.site_id
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_distinct/query00.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_distinct/query00.sql b/query/src/test/resources/query/sql_distinct/query00.sql
new file mode 100644
index 0000000..021caf4
--- /dev/null
+++ b/query/src/test/resources/query/sql_distinct/query00.sql
@@ -0,0 +1,6 @@
+select lstg_format_name, cal_dt,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT,
+ count(distinct seller_id) as DIST_SELLER
+ from test_kylin_fact
+ group by lstg_format_name, cal_dt
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_distinct/query01.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_distinct/query01.sql b/query/src/test/resources/query/sql_distinct/query01.sql
new file mode 100644
index 0000000..d57ef11
--- /dev/null
+++ b/query/src/test/resources/query/sql_distinct/query01.sql
@@ -0,0 +1,7 @@
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT,
+ count(distinct seller_id) as DIST_SELLER
+ from test_kylin_fact
+ where lstg_format_name='FP-GTC'
+ group by lstg_format_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_distinct/query02.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_distinct/query02.sql b/query/src/test/resources/query/sql_distinct/query02.sql
new file mode 100644
index 0000000..3c5589c
--- /dev/null
+++ b/query/src/test/resources/query/sql_distinct/query02.sql
@@ -0,0 +1,8 @@
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT,
+ count(distinct seller_id) as DIST_SELLER
+ from test_kylin_fact
+ where lstg_format_name='FP-GTC'
+ group by lstg_format_name
+ having count(distinct seller_id) > 50
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_distinct/query03.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_distinct/query03.sql b/query/src/test/resources/query/sql_distinct/query03.sql
new file mode 100644
index 0000000..e3c91f1
--- /dev/null
+++ b/query/src/test/resources/query/sql_distinct/query03.sql
@@ -0,0 +1,8 @@
+select test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV
+ , count(1) as TRANS_CNT, count(distinct seller_id) as DIST_SELLER
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ where test_kylin_fact.lstg_format_name='FP-GTC'
+ and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01'
+ group by test_cal_dt.week_beg_dt
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_distinct/query04.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_distinct/query04.sql b/query/src/test/resources/query/sql_distinct/query04.sql
new file mode 100644
index 0000000..3bc7c27
--- /dev/null
+++ b/query/src/test/resources/query/sql_distinct/query04.sql
@@ -0,0 +1,9 @@
+select test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV
+ , count(1) as TRANS_CNT, count(distinct seller_id) as DIST_SELLER
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ where test_kylin_fact.lstg_format_name='FP-GTC'
+ and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01'
+ group by test_cal_dt.week_beg_dt
+ having count(distinct seller_id) > 2
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_distinct/query05.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_distinct/query05.sql b/query/src/test/resources/query/sql_distinct/query05.sql
new file mode 100644
index 0000000..4744c4b
--- /dev/null
+++ b/query/src/test/resources/query/sql_distinct/query05.sql
@@ -0,0 +1,7 @@
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT,
+ count(distinct seller_id) as DIST_SELLER
+ from test_kylin_fact
+ group by lstg_format_name
+ order by lstg_format_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_distinct/query06.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_distinct/query06.sql b/query/src/test/resources/query/sql_distinct/query06.sql
new file mode 100644
index 0000000..b4ca929
--- /dev/null
+++ b/query/src/test/resources/query/sql_distinct/query06.sql
@@ -0,0 +1,8 @@
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT,
+ count(distinct seller_id) as DIST_SELLER
+ from test_kylin_fact
+ where lstg_format_name='FP-GTC'
+ group by lstg_format_name
+ order by lstg_format_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_distinct/query07.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_distinct/query07.sql b/query/src/test/resources/query/sql_distinct/query07.sql
new file mode 100644
index 0000000..1f48c52
--- /dev/null
+++ b/query/src/test/resources/query/sql_distinct/query07.sql
@@ -0,0 +1,6 @@
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT,
+ count(distinct seller_id) as DIST_SELLER
+ from test_kylin_fact
+ group by lstg_format_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_dynamic/query01.dat
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_dynamic/query01.dat b/query/src/test/resources/query/sql_dynamic/query01.dat
new file mode 100644
index 0000000..e810421
--- /dev/null
+++ b/query/src/test/resources/query/sql_dynamic/query01.dat
@@ -0,0 +1,2 @@
+FP-GTC
+Collectibles
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_dynamic/query01.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_dynamic/query01.sql b/query/src/test/resources/query/sql_dynamic/query01.sql
new file mode 100644
index 0000000..f481edf
--- /dev/null
+++ b/query/src/test/resources/query/sql_dynamic/query01.sql
@@ -0,0 +1,10 @@
+select test_cal_dt.week_beg_dt, test_kylin_fact.lstg_format_name, test_category_groupings.meta_categ_name, sum(test_kylin_fact.price) as gmv, count(*) as trans_cnt
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ where test_kylin_fact.lstg_format_name = ?
+ and test_category_groupings.meta_categ_name = ?
+ and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-10-01'
+ group by test_cal_dt.week_beg_dt, test_kylin_fact.lstg_format_name, test_category_groupings.meta_categ_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_dynamic/query02.dat
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_dynamic/query02.dat b/query/src/test/resources/query/sql_dynamic/query02.dat
new file mode 100644
index 0000000..e810421
--- /dev/null
+++ b/query/src/test/resources/query/sql_dynamic/query02.dat
@@ -0,0 +1,2 @@
+FP-GTC
+Collectibles
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_dynamic/query02.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_dynamic/query02.sql b/query/src/test/resources/query/sql_dynamic/query02.sql
new file mode 100644
index 0000000..701e6e4
--- /dev/null
+++ b/query/src/test/resources/query/sql_dynamic/query02.sql
@@ -0,0 +1,12 @@
+select sum(1) as "col" from (
+ select test_cal_dt.week_beg_dt, test_kylin_fact.lstg_format_name, test_category_groupings.meta_categ_name, sum(test_kylin_fact.price) as gmv, count(*) as trans_cnt
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ where test_kylin_fact.lstg_format_name = ?
+ and test_category_groupings.meta_categ_name = ?
+ and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-10-01'
+ group by test_cal_dt.week_beg_dt, test_kylin_fact.lstg_format_name, test_category_groupings.meta_categ_name
+ ) "tableausql" having count(1)>0
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_hive/query01.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_hive/query01.sql b/query/src/test/resources/query/sql_hive/query01.sql
new file mode 100644
index 0000000..322c260
--- /dev/null
+++ b/query/src/test/resources/query/sql_hive/query01.sql
@@ -0,0 +1,2 @@
+select cal_dt, sum(price)as GMV, count(1) as trans_cnt from test_kylin_fact
+ group by cal_dt
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_hive/query10.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_hive/query10.sql b/query/src/test/resources/query/sql_hive/query10.sql
new file mode 100644
index 0000000..8379174
--- /dev/null
+++ b/query/src/test/resources/query/sql_hive/query10.sql
@@ -0,0 +1,8 @@
+select test_cal_dt.QTR_BEG_DT,sum(test_kylin_fact.price) as gmv
+ , count(*) as trans_cnt
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ where test_kylin_fact.lstg_format_name='FP-GTC'
+ and test_cal_dt.week_beg_dt between '2013-05-01' and '2013-08-01'
+ group by test_cal_dt.QTR_BEG_DT
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_invalid/query29_invalid_SQL.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_invalid/query29_invalid_SQL.sql b/query/src/test/resources/query/sql_invalid/query29_invalid_SQL.sql
new file mode 100644
index 0000000..7672a93
--- /dev/null
+++ b/query/src/test/resources/query/sql_invalid/query29_invalid_SQL.sql
@@ -0,0 +1,37 @@
+SELECT
+ test_cal_dt.week_beg_dt_test
+ ,test_cal_dt.retail_year
+ ,test_cal_dt.rtl_month_of_rtl_year_id
+ ,test_cal_dt.retail_week
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_seller_type_dim.seller_type_desc
+ ,sum(test_kylin_fact.price) as gmv
+ , count(*) as trans_cnt
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ inner JOIN test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ inner JOIN test_seller_type_dim
+ ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd
+ where test_cal_dt.retail_year='2013'
+ and retail_week in(1,2,3,4,5,6,7,7,7)
+ and (test_category_groupings.meta_categ_name='Collectibles' or test_category_groupings.categ_lvl3_name='Dresses')
+ and test_sites.site_name='Ebay'
+ and test_cal_dt.retail_year not in ('2014')
+ group by test_cal_dt.week_beg_dt
+ ,test_cal_dt.retail_year
+ ,test_cal_dt.rtl_month_of_rtl_year_id
+ ,test_cal_dt.retail_week
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_seller_type_dim.seller_type_desc
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_invalid/query30_invalid_SQL.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_invalid/query30_invalid_SQL.sql b/query/src/test/resources/query/sql_invalid/query30_invalid_SQL.sql
new file mode 100644
index 0000000..decdc76
--- /dev/null
+++ b/query/src/test/resources/query/sql_invalid/query30_invalid_SQL.sql
@@ -0,0 +1,36 @@
+SELECT
+ test_cal_dt.week_beg_dt_test
+ ,test_cal_dt.retail_year
+ ,test_cal_dt.rtl_month_of_rtl_year_id
+ ,test_cal_dt.retail_week
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_seller_type_dim.seller_type_desc
+ ,sum(test_kylin_fact.price) as gmv
+ , count(*) as trans_cnt
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ inner JOIN test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ inner JOIN test_seller_type_dim
+ ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd
+ where test_cal_dt.retail_year='2013'
+ and retail_week in(1,2,3,4,5,6,7,7,7)
+ and (test_category_groupings.meta_categ_name='Collectibles' or test_category_groupings.categ_lvl3_name='Dresses')
+ and test_sites.site_name='Ebay'
+ and test_cal_dt.retail_year not in ('2014')
+ group by test_cal_dt.week_beg_dt
+ ,test_cal_dt.retail_year
+ ,test_cal_dt.rtl_month_of_rtl_year_id
+ ,test_cal_dt.retail_week
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_invalid/query31_invalid_SQL.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_invalid/query31_invalid_SQL.sql b/query/src/test/resources/query/sql_invalid/query31_invalid_SQL.sql
new file mode 100644
index 0000000..e479f78
--- /dev/null
+++ b/query/src/test/resources/query/sql_invalid/query31_invalid_SQL.sql
@@ -0,0 +1,37 @@
+SELECT
+ test_cal_dt.week_beg_dt_test
+ ,test_cal_dt.retail_year
+ ,test_cal_dt.rtl_month_of_rtl_year_id
+ ,test_cal_dt.retail_week
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_seller_type_dim.seller_type_desc
+ ,sum(test_kylin_fact.price_amt) as gmv
+ , count(*) as trans_cnt
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ inner JOIN test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ inner JOIN test_seller_type_dim
+ ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd
+ where test_cal_dt.retail_year='2013'
+ and retail_week in(1,2,3,4,5,6,7,7,7)
+ and (test_category_groupings.meta_categ_name='Collectibles' or test_category_groupings.categ_lvl3_name='Dresses')
+ and test_sites.site_name='Ebay'
+ and test_cal_dt.retail_year not in ('2014')
+ group by test_cal_dt.week_beg_dt
+ ,test_cal_dt.retail_year
+ ,test_cal_dt.rtl_month_of_rtl_year_id
+ ,test_cal_dt.retail_week
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_seller_type_dim.seller_type_desc
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_invalid/query32_invalid_SQL.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_invalid/query32_invalid_SQL.sql b/query/src/test/resources/query/sql_invalid/query32_invalid_SQL.sql
new file mode 100644
index 0000000..e8cb459
--- /dev/null
+++ b/query/src/test/resources/query/sql_invalid/query32_invalid_SQL.sql
@@ -0,0 +1,38 @@
+SELECT
+ test_cal_dt.week_beg_dt_test
+ ,test_cal_dt.retail_year
+ ,test_cal_dt.rtl_month_of_rtl_year_id
+ ,test_cal_dt.retail_week
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_seller_type_dim.seller_type_desc
+ ,sum(test_kylin_fact.price) as gmv
+ , count(*) as trans_cnt
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ inner JOIN test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ inner JOIN test_seller_type_dim
+ ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd
+ where test_cal_dt.retail_year='2013'
+ and retail_week in(1,2,3,4,5,6,7,7,7)
+ and (test_category_groupings.meta_categ_name='Collectibles' or test_category_groupings.categ_lvl3_name='Dresses')
+ and test_sites.site_name='Ebay'
+ and test_cal_dt.retail_year not in ('2014')
+ and test_kylin_fact.price<100
+ group by test_cal_dt.week_beg_dt
+ ,test_cal_dt.retail_year
+ ,test_cal_dt.rtl_month_of_rtl_year_id
+ ,test_cal_dt.retail_week
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_seller_type_dim.seller_type_desc
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_invalid/query33_invalid_SQL.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_invalid/query33_invalid_SQL.sql b/query/src/test/resources/query/sql_invalid/query33_invalid_SQL.sql
new file mode 100644
index 0000000..0202dce
--- /dev/null
+++ b/query/src/test/resources/query/sql_invalid/query33_invalid_SQL.sql
@@ -0,0 +1,38 @@
+SELECT
+ test_cal_dt.week_beg_dt_test
+ ,test_cal_dt.retail_year
+ ,test_cal_dt.rtl_month_of_rtl_year_id
+ ,test_cal_dt.retail_week
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_seller_type_dim.seller_type_desc
+ ,sum(test_kylin_fact.price) as gmv
+ , count(*) as trans_cnt
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ inner JOIN test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ inner JOIN test_seller_type_dim
+ ON test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd
+ where test_cal_dt.retail_year='2013'
+ and retail_week in(1,2,3,4,5,6,7,7,7)
+ and (test_category_groupings.meta_categ_name='Collectibles' or test_category_groupings.categ_lvl3_name='Dresses')
+ and test_sites.site_name='Ebay'
+ and test_cal_dt.retail_year not in ('2014')
+ and test_kylin_fact.trans_id=1000000001
+ group by test_cal_dt.week_beg_dt
+ ,test_cal_dt.retail_year
+ ,test_cal_dt.rtl_month_of_rtl_year_id
+ ,test_cal_dt.retail_week
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,test_kylin_fact.lstg_format_name
+ ,test_sites.site_name
+ ,test_seller_type_dim.seller_type_desc
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_invalid/query34_invalid_SQL.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_invalid/query34_invalid_SQL.sql b/query/src/test/resources/query/sql_invalid/query34_invalid_SQL.sql
new file mode 100644
index 0000000..0bcc5b4
--- /dev/null
+++ b/query/src/test/resources/query/sql_invalid/query34_invalid_SQL.sql
@@ -0,0 +1,6 @@
+SELECT COUNT(DISTINCT "TableauSQL"."TRANS_CNT") AS "ctd_TRANS_CNT_qk", "TableauSQL"."LSTG_FORMAT_NAME" AS "none_LSTG_FORMAT_NAME_nk"
+ FROM ( select test_kylin_fact.lstg_format_name, sum(price) as GMV, count(seller_id) as TRANS_CNT
+ from test_kylin_fact
+ group by test_kylin_fact.lstg_format_name
+ ) "TableauSQL"
+ GROUP BY "TableauSQL"."LSTG_FORMAT_NAME"
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_invalid/query_count_distinct_on_dimension.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_invalid/query_count_distinct_on_dimension.sql b/query/src/test/resources/query/sql_invalid/query_count_distinct_on_dimension.sql
new file mode 100644
index 0000000..30bbd76
--- /dev/null
+++ b/query/src/test/resources/query/sql_invalid/query_count_distinct_on_dimension.sql
@@ -0,0 +1 @@
+select count(distinct cal_dt) from test_kylin_fact
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_lookup/query01.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_lookup/query01.sql b/query/src/test/resources/query/sql_lookup/query01.sql
new file mode 100644
index 0000000..724c4fe
--- /dev/null
+++ b/query/src/test/resources/query/sql_lookup/query01.sql
@@ -0,0 +1,6 @@
+select CAL_DT, WEEK_BEG_DT from test_cal_dt
+
+
+
+
+
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_lookup/query02.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_lookup/query02.sql b/query/src/test/resources/query/sql_lookup/query02.sql
new file mode 100644
index 0000000..bfc2beb
--- /dev/null
+++ b/query/src/test/resources/query/sql_lookup/query02.sql
@@ -0,0 +1 @@
+select CATEG_LVL3_NAME, CATEG_LVL2_NAME, SITE_ID, META_CATEG_NAME, LEAF_CATEG_ID from test_category_groupings
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_lookup/query03.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_lookup/query03.sql b/query/src/test/resources/query/sql_lookup/query03.sql
new file mode 100644
index 0000000..3186374
--- /dev/null
+++ b/query/src/test/resources/query/sql_lookup/query03.sql
@@ -0,0 +1 @@
+select SELLER_TYPE_DESC, SELLER_TYPE_CD from test_seller_type_dim
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_lookup/query04.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_lookup/query04.sql b/query/src/test/resources/query/sql_lookup/query04.sql
new file mode 100644
index 0000000..83598c7
--- /dev/null
+++ b/query/src/test/resources/query/sql_lookup/query04.sql
@@ -0,0 +1 @@
+select SITE_NAME, SITE_ID from test_sites
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_optimize/enable-limit01.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_optimize/enable-limit01.sql b/query/src/test/resources/query/sql_optimize/enable-limit01.sql
new file mode 100644
index 0000000..a17869a
--- /dev/null
+++ b/query/src/test/resources/query/sql_optimize/enable-limit01.sql
@@ -0,0 +1 @@
+select * from test_kylin_fact limit 10
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_orderby/query01.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_orderby/query01.sql b/query/src/test/resources/query/sql_orderby/query01.sql
new file mode 100644
index 0000000..afbde8e
--- /dev/null
+++ b/query/src/test/resources/query/sql_orderby/query01.sql
@@ -0,0 +1,10 @@
+select test_cal_dt.Week_Beg_Dt, sum(price) as c1, count(1) as c2
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ where test_kylin_fact.lstg_format_name='ABIN'
+ and test_cal_dt.week_beg_dt >= DATE '2013-06-09'
+ group by test_cal_dt.week_beg_dt
+ order by test_cal_dt.week_beg_dt
+
+ -- optiq 0.8 reports varchar instead of date on week_beg_dt and fail test case
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_orderby/query02.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_orderby/query02.sql b/query/src/test/resources/query/sql_orderby/query02.sql
new file mode 100644
index 0000000..44485c5
--- /dev/null
+++ b/query/src/test/resources/query/sql_orderby/query02.sql
@@ -0,0 +1,7 @@
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT
+ from test_kylin_fact
+ where lstg_format_name='FP-GTC'
+ group by lstg_format_name
+ order by lstg_format_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_orderby/query03.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_orderby/query03.sql b/query/src/test/resources/query/sql_orderby/query03.sql
new file mode 100644
index 0000000..4093650
--- /dev/null
+++ b/query/src/test/resources/query/sql_orderby/query03.sql
@@ -0,0 +1,6 @@
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT
+ from test_kylin_fact
+ group by lstg_format_name
+ order by sum(price)
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_subquery/query00.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_subquery/query00.sql b/query/src/test/resources/query/sql_subquery/query00.sql
new file mode 100644
index 0000000..e2737ac
--- /dev/null
+++ b/query/src/test/resources/query/sql_subquery/query00.sql
@@ -0,0 +1,4 @@
+select test_cal_dt.week_beg_dt, sum(test_kylin_fact.price) as sum_price, count(1) as cnt_1
+ from test_kylin_fact
+ inner join test_cal_dt ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ group by test_cal_dt.week_beg_dt
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_subquery/query01.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_subquery/query01.sql b/query/src/test/resources/query/sql_subquery/query01.sql
new file mode 100644
index 0000000..b5c93b2
--- /dev/null
+++ b/query/src/test/resources/query/sql_subquery/query01.sql
@@ -0,0 +1,8 @@
+SELECT sum(sum_price) AS "COL"
+ FROM (
+ select test_cal_dt.week_beg_dt, sum(test_kylin_fact.price) as sum_price, count(1) as cnt_1
+ from test_kylin_fact
+ inner join test_cal_dt ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ group by test_cal_dt.week_beg_dt
+ ) "TableauSQL"
+ HAVING COUNT(1)>0
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_subquery/query02.sql.disable
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_subquery/query02.sql.disable b/query/src/test/resources/query/sql_subquery/query02.sql.disable
new file mode 100644
index 0000000..6e70b77
--- /dev/null
+++ b/query/src/test/resources/query/sql_subquery/query02.sql.disable
@@ -0,0 +1,7 @@
+SELECT sum(1) AS "COL"
+ FROM (
+ select test_cal_dt.week_beg_dt, sum(test_kylin_fact.price) as sum_price
+ from test_kylin_fact
+ inner join test_cal_dt ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ group by test_cal_dt.week_beg_dt
+ ) "TableauSQL"
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_subquery/query03.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_subquery/query03.sql b/query/src/test/resources/query/sql_subquery/query03.sql
new file mode 100644
index 0000000..e74cc58
--- /dev/null
+++ b/query/src/test/resources/query/sql_subquery/query03.sql
@@ -0,0 +1,9 @@
+select fact.cal_dt, sum(fact.price) as sum_price, count(1) as cnt_1
+from test_kylin_fact fact
+left join test_cal_dt cal on fact.cal_dt=cal.cal_dt
+inner join
+(
+ select test_kylin_fact.cal_dt, sum(test_kylin_fact.price) from test_kylin_fact left join test_cal_dt
+ on test_kylin_fact.cal_dt=test_cal_dt.cal_dt group by test_kylin_fact.cal_dt order by 2 desc limit 7
+) cal_2 on fact.cal_dt = cal_2.cal_dt
+group by fact.cal_dt
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_subquery/query04.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_subquery/query04.sql b/query/src/test/resources/query/sql_subquery/query04.sql
new file mode 100644
index 0000000..83d8ca6
--- /dev/null
+++ b/query/src/test/resources/query/sql_subquery/query04.sql
@@ -0,0 +1,9 @@
+select fact.cal_dt, sum(fact.price) as sum_price, count(1) as cnt_1
+from test_kylin_fact fact
+left join test_cal_dt cal on fact.cal_dt=cal.cal_dt
+inner join
+(
+ select test_kylin_fact.cal_dt, max(test_kylin_fact.cal_dt) as mmm from test_kylin_fact left join test_cal_dt
+ on test_kylin_fact.cal_dt=test_cal_dt.cal_dt group by test_kylin_fact.cal_dt order by 2 desc limit 7
+) cal_2 on fact.cal_dt = cal_2.mmm
+group by fact.cal_dt
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_subquery/query05.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_subquery/query05.sql b/query/src/test/resources/query/sql_subquery/query05.sql
new file mode 100644
index 0000000..1ae2005
--- /dev/null
+++ b/query/src/test/resources/query/sql_subquery/query05.sql
@@ -0,0 +1,10 @@
+SELECT SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok"
+ FROM "TEST_KYLIN_FACT"
+ INNER JOIN "TEST_CAL_DT" ON ("TEST_KYLIN_FACT"."CAL_DT" = "TEST_CAL_DT"."CAL_DT")
+ INNER JOIN (
+ SELECT COUNT(1) AS "XTableau_join_flag",
+ SUM("TEST_KYLIN_FACT"."PRICE") AS "X__alias__A",
+ "TEST_KYLIN_FACT"."CAL_DT" AS "none_CAL_DT_ok" FROM "TEST_KYLIN_FACT"
+ INNER JOIN "TEST_CAL_DT" ON ("TEST_KYLIN_FACT"."CAL_DT" = "TEST_CAL_DT"."CAL_DT")
+ GROUP BY "TEST_KYLIN_FACT"."CAL_DT" ORDER BY 2 DESC LIMIT 10 ) "t0" ON ("TEST_KYLIN_FACT"."CAL_DT" = "t0"."none_CAL_DT_ok")
+ GROUP BY "TEST_KYLIN_FACT"."CAL_DT"
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_subquery/query06.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_subquery/query06.sql b/query/src/test/resources/query/sql_subquery/query06.sql
new file mode 100644
index 0000000..724eb2d
--- /dev/null
+++ b/query/src/test/resources/query/sql_subquery/query06.sql
@@ -0,0 +1,6 @@
+SELECT "TEST_KYLIN_FACT"."CAL_DT", SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok" FROM "TEST_KYLIN_FACT" "TEST_KYLIN_FACT"
+ INNER JOIN (
+ SELECT COUNT(1) AS "XTableau_join_flag", SUM("TEST_KYLIN_FACT"."PRICE") AS "X__alias__A", "TEST_KYLIN_FACT"."CAL_DT" AS "none_CAL_DT_ok" FROM "TEST_KYLIN_FACT" "TEST_KYLIN_FACT"
+ GROUP BY "TEST_KYLIN_FACT"."CAL_DT" ORDER BY 2 DESC LIMIT 7 )
+
+ "t0" ON ("TEST_KYLIN_FACT"."CAL_DT" = "t0"."none_CAL_DT_ok") GROUP BY "TEST_KYLIN_FACT"."CAL_DT"
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_subquery/query07.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_subquery/query07.sql b/query/src/test/resources/query/sql_subquery/query07.sql
new file mode 100644
index 0000000..627e59b
--- /dev/null
+++ b/query/src/test/resources/query/sql_subquery/query07.sql
@@ -0,0 +1,6 @@
+select cal_dt, sum(price) as sum_price
+from test_kylin_fact fact
+inner join (
+select count(1) as cnt, min(cal_dt) as "mmm", cal_dt as dt from test_kylin_fact group by cal_dt order by 2 desc limit 10
+) t0 on (fact.cal_dt = t0.dt)
+group by cal_dt
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query00.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query00.sql b/query/src/test/resources/query/sql_tableau/query00.sql
new file mode 100644
index 0000000..790e348
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query00.sql
@@ -0,0 +1,5 @@
+select test_cal_dt.week_beg_dt, sum(test_kylin_fact.price)
+ from test_kylin_fact
+ inner join test_cal_dt ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ group by test_cal_dt.week_beg_dt
+
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query01.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query01.sql b/query/src/test/resources/query/sql_tableau/query01.sql
new file mode 100644
index 0000000..44e4c63
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query01.sql
@@ -0,0 +1,15 @@
+SELECT SUM(1) AS "COL",
+ 2 AS "COL2"
+ FROM (
+ select test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ where test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01'
+ group by test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt
+ having sum(price)>500
+ ) "TableauSQL"
+ GROUP BY 2
+ HAVING COUNT(1)>0
+
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query02.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query02.sql b/query/src/test/resources/query/sql_tableau/query02.sql
new file mode 100644
index 0000000..ca40f11
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query02.sql
@@ -0,0 +1,12 @@
+SELECT *
+ FROM (
+ select test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ where test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01'
+ group by test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt
+ having sum(price)>500
+ ) "TableauSQL"
+ LIMIT 1
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query03.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query03.sql b/query/src/test/resources/query/sql_tableau/query03.sql
new file mode 100644
index 0000000..b78a587
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query03.sql
@@ -0,0 +1,17 @@
+SELECT
+ test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id
+ AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ group by test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query04.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query04.sql b/query/src/test/resources/query/sql_tableau/query04.sql
new file mode 100644
index 0000000..b7c8a99
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query04.sql
@@ -0,0 +1,21 @@
+SELECT "TableauSQL"."META_CATEG_NAME" AS "none_META_CATEG_NAME_nk"
+ FROM (
+ SELECT
+ test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id
+ AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ group by test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ) "TableauSQL"
+ GROUP BY "TableauSQL"."META_CATEG_NAME"
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query05.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query05.sql b/query/src/test/resources/query/sql_tableau/query05.sql
new file mode 100644
index 0000000..25e8f89
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query05.sql
@@ -0,0 +1,22 @@
+SELECT SUM("TableauSQL"."GMV") AS "sum_GMV_ok",
+ SUM("TableauSQL"."TRANS_CNT") AS "sum_TRANS_CNT_ok"
+ FROM (
+ SELECT
+ test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id
+ AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ group by test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ) "TableauSQL"
+ HAVING (COUNT(1) > 0)
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query06.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query06.sql b/query/src/test/resources/query/sql_tableau/query06.sql
new file mode 100644
index 0000000..25e8f89
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query06.sql
@@ -0,0 +1,22 @@
+SELECT SUM("TableauSQL"."GMV") AS "sum_GMV_ok",
+ SUM("TableauSQL"."TRANS_CNT") AS "sum_TRANS_CNT_ok"
+ FROM (
+ SELECT
+ test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id
+ AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ group by test_cal_dt.week_beg_dt
+ ,test_category_groupings.meta_categ_name
+ ,test_category_groupings.categ_lvl2_name
+ ,test_category_groupings.categ_lvl3_name
+ ) "TableauSQL"
+ HAVING (COUNT(1) > 0)
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query07.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query07.sql b/query/src/test/resources/query/sql_tableau/query07.sql
new file mode 100644
index 0000000..5cf3bae
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query07.sql
@@ -0,0 +1,8 @@
+SELECT SUM("TableauSQL"."GMV") AS "sum_GMV_ok", SUM("TableauSQL"."TRANS_CNT") AS "sum_TRANS_CNT_ok"
+ FROM (
+ SELECT test_cal_dt.week_beg_dt ,test_category_groupings.meta_categ_name ,test_category_groupings.categ_lvl2_name ,test_category_groupings.categ_lvl3_name ,sum(test_kylin_fact.price) as GMV , count(*) as TRANS_CNT
+ FROM test_kylin_fact
+ inner JOIN test_cal_dt ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+ group by test_cal_dt.week_beg_dt ,test_category_groupings.meta_categ_name ,test_category_groupings.categ_lvl2_name ,test_category_groupings.categ_lvl3_name
+ ) "TableauSQL" HAVING (COUNT(1) > 0)
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query08.sql.disabled
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query08.sql.disabled b/query/src/test/resources/query/sql_tableau/query08.sql.disabled
new file mode 100644
index 0000000..c5deb9c
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query08.sql.disabled
@@ -0,0 +1,6 @@
+-- LEAF_CATEG_ID is a join field on fact table, but no on cuboid. We really support this?
+
+SELECT "TEST_KYLIN_FACT"."LEAF_CATEG_ID" AS "NONE_LEAF_CATEG_ID_OK"
+FROM "olap"."TEST_KYLIN_FACT" "TEST_KYLIN_FACT"
+GROUP BY "TEST_KYLIN_FACT"."LEAF_CATEG_ID"
+
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query09.sql.disabled
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query09.sql.disabled b/query/src/test/resources/query/sql_tableau/query09.sql.disabled
new file mode 100644
index 0000000..5814c81
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query09.sql.disabled
@@ -0,0 +1,39 @@
+SELECT "TEST_CATEGORY_GROUPINGS"."ADULT_CATEG_YN" AS "ADULT_CATEG_YN",
+ "TEST_CATEGORY_GROUPINGS"."BSNS_VRTCL_NAME" AS "BSNS_VRTCL_NAME",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_BUSN_MGR" AS "CATEG_BUSN_MGR",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_BUSN_UNIT" AS "CATEG_BUSN_UNIT",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_FLAGS" AS "CATEG_FLAGS",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_LVL2_ID" AS "CATEG_LVLC_ID",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_LVL2_NAME" AS "CATEG_LVLC_NAME",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_LVL3_ID" AS "CATEG_LVLD_ID",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_LVL3_NAME" AS "CATEG_LVLD_NAME",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_LVL4_ID" AS "CATEG_LVLE_ID",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_LVL4_NAME" AS "CATEG_LVLE_NAME",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_LVL5_ID" AS "CATEG_LVLF_ID",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_LVL5_NAME" AS "CATEG_LVLF_NAME",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_LVL6_ID" AS "CATEG_LVLG_ID",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_LVL6_NAME" AS "CATEG_LVLG_NAME",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_LVL7_ID" AS "CATEG_LVLH_ID",
+ "TEST_CATEGORY_GROUPINGS"."CATEG_LVL7_NAME" AS "CATEG_LVLH_NAME",
+ "TEST_CATEGORY_GROUPINGS"."CRE_DATE" AS "CRE_DATE",
+ "TEST_CATEGORY_GROUPINGS"."CRE_USER" AS "CRE_USER",
+ "TEST_CATEGORY_GROUPINGS"."DOMAIN_ID" AS "DOMAIN_ID",
+ "TEST_CATEGORY_GROUPINGS"."GCS_ID" AS "GCS_ID",
+ "TEST_CATEGORY_GROUPINGS"."LEAF_CATEG_ID" AS "LEAF_CATEG_ID",
+ "TEST_CATEGORY_GROUPINGS"."LEAF_CATEG_NAME" AS "LEAF_CATEG_NAME",
+ "TEST_CATEGORY_GROUPINGS"."META_CATEG_ID" AS "META_CATEG_ID",
+ "TEST_CATEGORY_GROUPINGS"."META_CATEG_NAME" AS "META_CATEG_NAME",
+ "TEST_CATEGORY_GROUPINGS"."MOVE_TO" AS "MOVE_TO",
+ 1 AS "Number_of_Records",
+ "TEST_CATEGORY_GROUPINGS"."REGN_CATEG" AS "REGN_CATEG",
+ "TEST_CATEGORY_GROUPINGS"."SAP_CATEGORY_ID" AS "SAP_CATEGORY_ID",
+ "TEST_CATEGORY_GROUPINGS"."SITE_ID" AS "SITE_ID",
+ "TEST_CATEGORY_GROUPINGS"."SRC_ID" AS "SRC_ID",
+ "TEST_CATEGORY_GROUPINGS"."UPD_DATE" AS "UPD_DATE",
+ "TEST_CATEGORY_GROUPINGS"."UPD_USER" AS "UPD_USER",
+ "TEST_CATEGORY_GROUPINGS"."USER_DEFINED_FIELD1" AS "USER_DEFINED_FIELDB",
+ "TEST_CATEGORY_GROUPINGS"."USER_DEFINED_FIELD3" AS "USER_DEFINED_FIELDD",
+ "TEST_CATEGORY_GROUPINGS"."USER_DEFINED_FIELD5" AS "USER_DEFINED_FIELDF",
+ "TEST_CATEGORY_GROUPINGS"."VCS_ID" AS "VCS_ID"
+FROM "olap"."TEST_CATEGORY_GROUPINGS" "TEST_CATEGORY_GROUPINGS"
+LIMIT 10000
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query10.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query10.sql b/query/src/test/resources/query/sql_tableau/query10.sql
new file mode 100644
index 0000000..26d29a3
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query10.sql
@@ -0,0 +1,5 @@
+SELECT "TEST_CAL_DT"."WEEK_BEG_DT" AS "none_WEEK_BEG_DT_nk",
+ SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok"
+ FROM "TEST_KYLIN_FACT"
+ inner JOIN "TEST_CAL_DT" ON ("TEST_KYLIN_FACT"."CAL_DT" = "TEST_CAL_DT"."CAL_DT")
+ GROUP BY "TEST_CAL_DT"."WEEK_BEG_DT"
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query11.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query11.sql b/query/src/test/resources/query/sql_tableau/query11.sql
new file mode 100644
index 0000000..d7e70c1
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query11.sql
@@ -0,0 +1,6 @@
+SELECT COUNT(1) AS cnt_ITEM_COUNT_ok,
+ TEST_CAL_DT.WEEK_BEG_DT AS none_WEEK_BEG_DT_nk
+ FROM "TEST_KYLIN_FACT"
+ inner JOIN "TEST_CAL_DT" ON ("TEST_KYLIN_FACT"."CAL_DT" = "TEST_CAL_DT"."CAL_DT")
+ GROUP BY "TEST_CAL_DT"."WEEK_BEG_DT"
+
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query12.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query12.sql b/query/src/test/resources/query/sql_tableau/query12.sql
new file mode 100644
index 0000000..26d29a3
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query12.sql
@@ -0,0 +1,5 @@
+SELECT "TEST_CAL_DT"."WEEK_BEG_DT" AS "none_WEEK_BEG_DT_nk",
+ SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok"
+ FROM "TEST_KYLIN_FACT"
+ inner JOIN "TEST_CAL_DT" ON ("TEST_KYLIN_FACT"."CAL_DT" = "TEST_CAL_DT"."CAL_DT")
+ GROUP BY "TEST_CAL_DT"."WEEK_BEG_DT"
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query13.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query13.sql b/query/src/test/resources/query/sql_tableau/query13.sql
new file mode 100644
index 0000000..28c0e05
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query13.sql
@@ -0,0 +1,4 @@
+SELECT EXTRACT(YEAR FROM TEST_CAL_DT.WEEK_BEG_DT) AS yr_WEEK_BEG_DT_ok
+ FROM TEST_KYLIN_FACT
+ inner JOIN TEST_CAL_DT ON (TEST_KYLIN_FACT.CAL_DT = TEST_CAL_DT.CAL_DT)
+ GROUP BY EXTRACT(YEAR FROM TEST_CAL_DT.WEEK_BEG_DT)
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query14.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query14.sql b/query/src/test/resources/query/sql_tableau/query14.sql
new file mode 100644
index 0000000..0f5d99c
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query14.sql
@@ -0,0 +1 @@
+SELECT QUARTER("TEST_CAL_DT"."WEEK_BEG_DT") AS "qr_WEEK_BEG_DT_ok", EXTRACT(YEAR FROM "TEST_CAL_DT"."WEEK_BEG_DT") AS "yr_WEEK_BEG_DT_ok" FROM "TEST_KYLIN_FACT" inner JOIN "TEST_CAL_DT" ON ("TEST_KYLIN_FACT"."CAL_DT" = "TEST_CAL_DT"."CAL_DT") GROUP BY QUARTER("TEST_CAL_DT"."WEEK_BEG_DT"), EXTRACT(YEAR FROM "TEST_CAL_DT"."WEEK_BEG_DT")
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query15.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query15.sql b/query/src/test/resources/query/sql_tableau/query15.sql
new file mode 100644
index 0000000..6bebae6
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query15.sql
@@ -0,0 +1,4 @@
+SELECT QUARTER(TEST_CAL_DT.WEEK_BEG_DT) AS qr_WEEK_BEG_DT_ok
+ FROM TEST_KYLIN_FACT
+ inner JOIN TEST_CAL_DT ON (TEST_KYLIN_FACT.CAL_DT = TEST_CAL_DT.CAL_DT)
+ GROUP BY QUARTER(TEST_CAL_DT.WEEK_BEG_DT)
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query16.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query16.sql b/query/src/test/resources/query/sql_tableau/query16.sql
new file mode 100644
index 0000000..28d2dbb
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query16.sql
@@ -0,0 +1,4 @@
+SELECT EXTRACT(YEAR FROM TEST_CAL_DT.WEEK_BEG_DT) AS yr_WEEK_BEG_DT_ok, QUARTER(TEST_CAL_DT.WEEK_BEG_DT) AS qr_WEEK_BEG_DT_ok
+ FROM TEST_KYLIN_FACT
+ inner JOIN TEST_CAL_DT ON (TEST_KYLIN_FACT.CAL_DT = TEST_CAL_DT.CAL_DT)
+ GROUP BY EXTRACT(YEAR FROM TEST_CAL_DT.WEEK_BEG_DT), QUARTER(TEST_CAL_DT.WEEK_BEG_DT)
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query17.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query17.sql b/query/src/test/resources/query/sql_tableau/query17.sql
new file mode 100644
index 0000000..c4dfba3
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query17.sql
@@ -0,0 +1 @@
+SELECT EXTRACT(MONTH FROM "TEST_CAL_DT"."WEEK_BEG_DT") AS "mn_WEEK_BEG_DT_ok", (( EXTRACT(YEAR FROM "TEST_CAL_DT"."WEEK_BEG_DT") * 100) + EXTRACT(MONTH FROM "TEST_CAL_DT"."WEEK_BEG_DT")) AS "my_WEEK_BEG_DT_ok", QUARTER("TEST_CAL_DT"."WEEK_BEG_DT") AS "qr_WEEK_BEG_DT_ok", EXTRACT(YEAR FROM "TEST_CAL_DT"."WEEK_BEG_DT") AS "yr_WEEK_BEG_DT_ok" FROM "TEST_KYLIN_FACT" inner JOIN "TEST_CAL_DT" ON ("TEST_KYLIN_FACT"."CAL_DT" = "TEST_CAL_DT"."CAL_DT") GROUP BY EXTRACT(YEAR FROM "TEST_CAL_DT"."WEEK_BEG_DT"), QUARTER("TEST_CAL_DT"."WEEK_BEG_DT"), (( EXTRACT(YEAR FROM "TEST_CAL_DT"."WEEK_BEG_DT") * 100) + EXTRACT(MONTH FROM "TEST_CAL_DT"."WEEK_BEG_DT")), EXTRACT(MONTH FROM "TEST_CAL_DT"."WEEK_BEG_DT")
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query18.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query18.sql b/query/src/test/resources/query/sql_tableau/query18.sql
new file mode 100644
index 0000000..c1041b4
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query18.sql
@@ -0,0 +1,3 @@
+select test_kylin_fact.lstg_format_name, sum(price) as GMV, count(seller_id) as TRANS_CNT
+ from test_kylin_fact
+ group by test_kylin_fact.lstg_format_name
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query19.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query19.sql b/query/src/test/resources/query/sql_tableau/query19.sql
new file mode 100644
index 0000000..83311f1
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query19.sql
@@ -0,0 +1,8 @@
+
+
+
+ SELECT "TableauSQL"."LSTG_FORMAT_NAME" AS "none_LSTG_FORMAT_NAME_nk", SUM("TableauSQL"."TRANS_CNT") AS "sum_TRANS_CNT_qk"
+ FROM ( select test_kylin_fact.lstg_format_name, sum(price) as GMV, count(seller_id) as TRANS_CNT
+ from test_kylin_fact
+ group by test_kylin_fact.lstg_format_name ) "TableauSQL"
+ GROUP BY "TableauSQL"."LSTG_FORMAT_NAME"
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query20.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query20.sql b/query/src/test/resources/query/sql_tableau/query20.sql
new file mode 100644
index 0000000..baa4547
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query20.sql
@@ -0,0 +1,8 @@
+
+
+
+ SELECT "TableauSQL"."LSTG_FORMAT_NAME" AS "none_LSTG_FORMAT_NAME_nk", SUM("TableauSQL"."TRANS_CNT") AS "sum_TRANS_CNT_qk"
+ FROM ( select test_kylin_fact.lstg_format_name, sum(price) as GMV, count(seller_id) as TRANS_CNT
+ from test_kylin_fact where test_kylin_fact.lstg_format_name > 'ab'
+ group by test_kylin_fact.lstg_format_name having count(seller_id) > 2 ) "TableauSQL"
+ GROUP BY "TableauSQL"."LSTG_FORMAT_NAME"
http://git-wip-us.apache.org/repos/asf/incubator-kylin/blob/4b631f92/query/src/test/resources/query/sql_tableau/query21.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/sql_tableau/query21.sql b/query/src/test/resources/query/sql_tableau/query21.sql
new file mode 100644
index 0000000..a31f1a7
--- /dev/null
+++ b/query/src/test/resources/query/sql_tableau/query21.sql
@@ -0,0 +1,9 @@
+ select test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+ inner JOIN test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ where test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01'
+ group by test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt
+ having sum(price)>500
+ limit 1