You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@kylin.apache.org by ma...@apache.org on 2016/01/26 14:41:48 UTC
[12/17] kylin git commit: KYLIN-1356 move all it to a seprate module
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_all_types/query08.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_all_types/query08.sql b/kylin-it/src/test/resources/query/sql_all_types/query08.sql
new file mode 100644
index 0000000..a10ef8a
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_all_types/query08.sql
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_all_types/query09.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_all_types/query09.sql b/kylin-it/src/test/resources/query/sql_all_types/query09.sql
new file mode 100644
index 0000000..bd44d00
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_all_types/query09.sql
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_cache/query01.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_cache/query01.sql b/kylin-it/src/test/resources/query/sql_cache/query01.sql
new file mode 100644
index 0000000..308a5f3
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_cache/query01.sql
@@ -0,0 +1,11 @@
+select test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+inner JOIN edw.test_cal_dt as 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 edw.test_sites as test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ where test_kylin_fact.cal_dt < DATE '2012-09-10' AND test_kylin_fact.cal_dt > DATE '2012-01-01'
+ group by test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_cache/query02.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_cache/query02.sql b/kylin-it/src/test/resources/query/sql_cache/query02.sql
new file mode 100644
index 0000000..ae1a933
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_cache/query02.sql
@@ -0,0 +1,11 @@
+select test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+inner JOIN edw.test_cal_dt as 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 edw.test_sites as test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ where test_kylin_fact.cal_dt < DATE '2012-10-10' AND test_kylin_fact.cal_dt > DATE '2012-01-01'
+ group by test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_cache/query03.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_cache/query03.sql b/kylin-it/src/test/resources/query/sql_cache/query03.sql
new file mode 100644
index 0000000..421bde4
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_cache/query03.sql
@@ -0,0 +1,11 @@
+select test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+inner JOIN edw.test_cal_dt as 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 edw.test_sites as test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ where test_kylin_fact.cal_dt < DATE '2013-02-10' AND test_kylin_fact.cal_dt > DATE '2012-01-01'
+ group by test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_cache/query04.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_cache/query04.sql b/kylin-it/src/test/resources/query/sql_cache/query04.sql
new file mode 100644
index 0000000..c875e80
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_cache/query04.sql
@@ -0,0 +1,11 @@
+select test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+inner JOIN edw.test_cal_dt as 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 edw.test_sites as test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ where test_kylin_fact.cal_dt < DATE '2013-10-10' AND test_kylin_fact.cal_dt > DATE '2012-01-01'
+ group by test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_cache/query05.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_cache/query05.sql b/kylin-it/src/test/resources/query/sql_cache/query05.sql
new file mode 100644
index 0000000..2b5ff24
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_cache/query05.sql
@@ -0,0 +1,11 @@
+select test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+inner JOIN edw.test_cal_dt as 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 edw.test_sites as test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ where test_kylin_fact.cal_dt < DATE '2014-10-10' AND test_kylin_fact.cal_dt > DATE '2012-01-01'
+ group by test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_cache/query06.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_cache/query06.sql b/kylin-it/src/test/resources/query/sql_cache/query06.sql
new file mode 100644
index 0000000..e1bee95
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_cache/query06.sql
@@ -0,0 +1,11 @@
+select test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+inner JOIN edw.test_cal_dt as 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 edw.test_sites as test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ where test_kylin_fact.cal_dt < DATE '2013-10-10' AND test_kylin_fact.cal_dt > DATE '2013-09-10'
+ group by test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_cache/query07.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_cache/query07.sql b/kylin-it/src/test/resources/query/sql_cache/query07.sql
new file mode 100644
index 0000000..b9e2821
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_cache/query07.sql
@@ -0,0 +1,11 @@
+select test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+inner JOIN edw.test_cal_dt as 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 edw.test_sites as test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ where test_kylin_fact.cal_dt < DATE '2018-10-10' AND test_kylin_fact.cal_dt > DATE '2009-09-10'
+ group by test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_cache/query08.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_cache/query08.sql b/kylin-it/src/test/resources/query/sql_cache/query08.sql
new file mode 100644
index 0000000..308a5f3
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_cache/query08.sql
@@ -0,0 +1,11 @@
+select test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+inner JOIN edw.test_cal_dt as 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 edw.test_sites as test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ where test_kylin_fact.cal_dt < DATE '2012-09-10' AND test_kylin_fact.cal_dt > DATE '2012-01-01'
+ group by test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_cache/query09.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_cache/query09.sql b/kylin-it/src/test/resources/query/sql_cache/query09.sql
new file mode 100644
index 0000000..b9e2821
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_cache/query09.sql
@@ -0,0 +1,11 @@
+select test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt,sum(test_kylin_fact.price) as GMV
+ , count(*) as TRANS_CNT
+ from test_kylin_fact
+inner JOIN edw.test_cal_dt as 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 edw.test_sites as test_sites
+ ON test_kylin_fact.lstg_site_id = test_sites.site_id
+ where test_kylin_fact.cal_dt < DATE '2018-10-10' AND test_kylin_fact.cal_dt > DATE '2009-09-10'
+ group by test_kylin_fact.lstg_format_name, test_kylin_fact.cal_dt
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_casewhen/query01.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_casewhen/query01.sql b/kylin-it/src/test/resources/query/sql_casewhen/query01.sql
new file mode 100644
index 0000000..ce6e81a
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_casewhen/query01.sql
@@ -0,0 +1,26 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_casewhen/query02.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_casewhen/query02.sql b/kylin-it/src/test/resources/query/sql_casewhen/query02.sql
new file mode 100644
index 0000000..697f423
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_casewhen/query02.sql
@@ -0,0 +1,22 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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"
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_casewhen/query03.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_casewhen/query03.sql b/kylin-it/src/test/resources/query/sql_casewhen/query03.sql
new file mode 100644
index 0000000..85c662a
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_casewhen/query03.sql
@@ -0,0 +1,25 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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"
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_casewhen/query55.sql.disable
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_casewhen/query55.sql.disable b/kylin-it/src/test/resources/query/sql_casewhen/query55.sql.disable
new file mode 100644
index 0000000..d29b60d
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_casewhen/query55.sql.disable
@@ -0,0 +1,22 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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)
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_casewhen/query56.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_casewhen/query56.sql b/kylin-it/src/test/resources/query/sql_casewhen/query56.sql
new file mode 100644
index 0000000..7006fbf
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_casewhen/query56.sql
@@ -0,0 +1,22 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+ 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)
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_derived/query01.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_derived/query01.sql b/kylin-it/src/test/resources/query/sql_derived/query01.sql
new file mode 100644
index 0000000..c4b0aae
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_derived/query01.sql
@@ -0,0 +1,47 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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 edw.test_cal_dt as 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 edw.test_sites as 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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_derived/query02.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_derived/query02.sql b/kylin-it/src/test/resources/query/sql_derived/query02.sql
new file mode 100644
index 0000000..027cd9c
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_derived/query02.sql
@@ -0,0 +1,47 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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 edw.test_cal_dt as 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 edw.test_sites as 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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_derived/query03.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_derived/query03.sql b/kylin-it/src/test/resources/query/sql_derived/query03.sql
new file mode 100644
index 0000000..35736b9
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_derived/query03.sql
@@ -0,0 +1,21 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+SELECT distinct LSTG_FORMAT_NAME from test_kylin_fact
+
+
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_derived/query04.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_derived/query04.sql b/kylin-it/src/test/resources/query/sql_derived/query04.sql
new file mode 100644
index 0000000..dab5d0f
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_derived/query04.sql
@@ -0,0 +1,20 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+SELECT site_id, site_name, cre_user from edw.test_sites
+
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_derived/query05.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_derived/query05.sql b/kylin-it/src/test/resources/query/sql_derived/query05.sql
new file mode 100644
index 0000000..61a9b2c
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_derived/query05.sql
@@ -0,0 +1,28 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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 edw.test_cal_dt as 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 edw.test_sites as 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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_derived/query06.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_derived/query06.sql b/kylin-it/src/test/resources/query/sql_derived/query06.sql
new file mode 100644
index 0000000..0ea32b2
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_derived/query06.sql
@@ -0,0 +1,20 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+SELECT distinct cal_dt from test_kylin_fact
+
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_derived/query07.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_derived/query07.sql b/kylin-it/src/test/resources/query/sql_derived/query07.sql
new file mode 100644
index 0000000..a27fbd1
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_derived/query07.sql
@@ -0,0 +1,22 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_derived/query08.sql.disabled
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_derived/query08.sql.disabled b/kylin-it/src/test/resources/query/sql_derived/query08.sql.disabled
new file mode 100644
index 0000000..c4e7d0c
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_derived/query08.sql.disabled
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select distinct leaf_categ_id, lstg_site_id from test_kylin_fact
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_derived/query09.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_derived/query09.sql b/kylin-it/src/test/resources/query/sql_derived/query09.sql
new file mode 100644
index 0000000..c0aacef
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_derived/query09.sql
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select distinct leaf_categ_id, site_id from test_category_groupings
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_derived/query10.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_derived/query10.sql b/kylin-it/src/test/resources/query/sql_derived/query10.sql
new file mode 100644
index 0000000..dd29659
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_derived/query10.sql
@@ -0,0 +1,44 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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 edw.test_cal_dt as 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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct/query00.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct/query00.sql b/kylin-it/src/test/resources/query/sql_distinct/query00.sql
new file mode 100644
index 0000000..03a2ada
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct/query00.sql
@@ -0,0 +1,24 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct/query01.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct/query01.sql b/kylin-it/src/test/resources/query/sql_distinct/query01.sql
new file mode 100644
index 0000000..9f92497
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct/query01.sql
@@ -0,0 +1,25 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct/query02.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct/query02.sql b/kylin-it/src/test/resources/query/sql_distinct/query02.sql
new file mode 100644
index 0000000..a903bf3
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct/query02.sql
@@ -0,0 +1,26 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct/query03.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct/query03.sql b/kylin-it/src/test/resources/query/sql_distinct/query03.sql
new file mode 100644
index 0000000..61ab376
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct/query03.sql
@@ -0,0 +1,26 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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 edw.test_cal_dt as 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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct/query04.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct/query04.sql b/kylin-it/src/test/resources/query/sql_distinct/query04.sql
new file mode 100644
index 0000000..cb118fa
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct/query04.sql
@@ -0,0 +1,27 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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 edw.test_cal_dt as 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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct/query05.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct/query05.sql b/kylin-it/src/test/resources/query/sql_distinct/query05.sql
new file mode 100644
index 0000000..cee8ef4
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct/query05.sql
@@ -0,0 +1,25 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct/query06.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct/query06.sql b/kylin-it/src/test/resources/query/sql_distinct/query06.sql
new file mode 100644
index 0000000..4a46048
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct/query06.sql
@@ -0,0 +1,26 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct/query07.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct/query07.sql b/kylin-it/src/test/resources/query/sql_distinct/query07.sql
new file mode 100644
index 0000000..b76cdc8
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct/query07.sql
@@ -0,0 +1,24 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct_precisely/query00.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct_precisely/query00.sql b/kylin-it/src/test/resources/query/sql_distinct_precisely/query00.sql
new file mode 100644
index 0000000..e1e4a9e
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct_precisely/query00.sql
@@ -0,0 +1,24 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select lstg_format_name, cal_dt,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT,
+ count(distinct leaf_categ_id) as LEAF_CATEG_CNT
+ from test_kylin_fact
+ group by lstg_format_name, cal_dt
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct_precisely/query01.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct_precisely/query01.sql b/kylin-it/src/test/resources/query/sql_distinct_precisely/query01.sql
new file mode 100644
index 0000000..c1868b8
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct_precisely/query01.sql
@@ -0,0 +1,25 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT,
+ count(distinct leaf_categ_id) as LEAF_CATEG_CNT
+ from test_kylin_fact
+ where lstg_format_name='FP-GTC'
+ group by lstg_format_name
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct_precisely/query02.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct_precisely/query02.sql b/kylin-it/src/test/resources/query/sql_distinct_precisely/query02.sql
new file mode 100644
index 0000000..5a3527a
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct_precisely/query02.sql
@@ -0,0 +1,26 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT,
+ count(distinct leaf_categ_id) as LEAF_CATEG_CNT
+ 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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct_precisely/query03.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct_precisely/query03.sql b/kylin-it/src/test/resources/query/sql_distinct_precisely/query03.sql
new file mode 100644
index 0000000..dacdc87
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct_precisely/query03.sql
@@ -0,0 +1,33 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV
+ , count(1) as TRANS_CNT, count(distinct test_kylin_fact.leaf_categ_id) as LEAF_CATEG_CNT
+ from test_kylin_fact
+ inner JOIN edw.test_cal_dt as 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 edw.test_sites as test_sites
+ on test_kylin_fact.lstg_site_id = test_sites.site_id
+ inner JOIN edw.test_seller_type_dim as test_seller_type_dim
+ on test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd
+ 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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct_precisely/query04.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct_precisely/query04.sql b/kylin-it/src/test/resources/query/sql_distinct_precisely/query04.sql
new file mode 100644
index 0000000..ff511c3
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct_precisely/query04.sql
@@ -0,0 +1,34 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV
+ , count(1) as TRANS_CNT, count(distinct test_kylin_fact.leaf_categ_id) as LEAF_CATEG_CNT
+ from test_kylin_fact
+ inner JOIN edw.test_cal_dt as 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 edw.test_sites as test_sites
+ on test_kylin_fact.lstg_site_id = test_sites.site_id
+ inner JOIN edw.test_seller_type_dim as test_seller_type_dim
+ on test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd
+ 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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct_precisely/query05.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct_precisely/query05.sql b/kylin-it/src/test/resources/query/sql_distinct_precisely/query05.sql
new file mode 100644
index 0000000..3d5e5e8
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct_precisely/query05.sql
@@ -0,0 +1,25 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT,
+ count(distinct leaf_categ_id) as LEAF_CATEG_CNT
+ from test_kylin_fact
+ group by lstg_format_name
+ order by lstg_format_name
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct_precisely/query06.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct_precisely/query06.sql b/kylin-it/src/test/resources/query/sql_distinct_precisely/query06.sql
new file mode 100644
index 0000000..858c92e
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct_precisely/query06.sql
@@ -0,0 +1,26 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT,
+ count(distinct leaf_categ_id) as LEAF_CATEG_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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_distinct_precisely/query07.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_distinct_precisely/query07.sql b/kylin-it/src/test/resources/query/sql_distinct_precisely/query07.sql
new file mode 100644
index 0000000..41252c4
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_distinct_precisely/query07.sql
@@ -0,0 +1,24 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select lstg_format_name,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT,
+ count(distinct leaf_categ_id) as LEAF_CATEG_CNT
+ from test_kylin_fact
+ group by lstg_format_name
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_dynamic/query01.dat
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_dynamic/query01.dat b/kylin-it/src/test/resources/query/sql_dynamic/query01.dat
new file mode 100644
index 0000000..a72c741
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_dynamic/query01.dat
@@ -0,0 +1,2 @@
+FP-GTC
+Collectibles
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_dynamic/query01.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_dynamic/query01.sql b/kylin-it/src/test/resources/query/sql_dynamic/query01.sql
new file mode 100644
index 0000000..d777fdf
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_dynamic/query01.sql
@@ -0,0 +1,28 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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 edw.test_cal_dt as 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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_dynamic/query02.dat
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_dynamic/query02.dat b/kylin-it/src/test/resources/query/sql_dynamic/query02.dat
new file mode 100644
index 0000000..a72c741
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_dynamic/query02.dat
@@ -0,0 +1,2 @@
+FP-GTC
+Collectibles
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_dynamic/query02.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_dynamic/query02.sql b/kylin-it/src/test/resources/query/sql_dynamic/query02.sql
new file mode 100644
index 0000000..eaeccdd
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_dynamic/query02.sql
@@ -0,0 +1,30 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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 edw.test_cal_dt as 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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_fast_common/query00.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_fast_common/query00.sql b/kylin-it/src/test/resources/query/sql_fast_common/query00.sql
new file mode 100644
index 0000000..36249a8
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_fast_common/query00.sql
@@ -0,0 +1,23 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select lstg_format_name, cal_dt,
+ sum(price) as GMV,
+ count(1) as TRANS_CNT
+ from test_kylin_fact
+ group by lstg_format_name, cal_dt
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_hive/query01.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_hive/query01.sql b/kylin-it/src/test/resources/query/sql_hive/query01.sql
new file mode 100644
index 0000000..34b81e2
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_hive/query01.sql
@@ -0,0 +1,20 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_hive/query10.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_hive/query10.sql b/kylin-it/src/test/resources/query/sql_hive/query10.sql
new file mode 100644
index 0000000..ca77a41
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_hive/query10.sql
@@ -0,0 +1,26 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select test_cal_dt.QTR_BEG_DT,sum(test_kylin_fact.price) as gmv
+ , count(*) as trans_cnt
+ from test_kylin_fact
+ inner JOIN edw.test_cal_dt as 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/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_ii/query01.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_ii/query01.sql b/kylin-it/src/test/resources/query/sql_ii/query01.sql
new file mode 100644
index 0000000..be187f5
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_ii/query01.sql
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select count(*) as all_count from test_kylin_fact
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_ii/query02.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_ii/query02.sql b/kylin-it/src/test/resources/query/sql_ii/query02.sql
new file mode 100644
index 0000000..eb52d11
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_ii/query02.sql
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select sum(price) as price_sum, min(price) as price_min, max(price) as price_max from test_kylin_fact
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_ii/query03.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_ii/query03.sql b/kylin-it/src/test/resources/query/sql_ii/query03.sql
new file mode 100644
index 0000000..d20efec
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_ii/query03.sql
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select sum(price) as price_sum, min(price) as price_min , max(price) as price_max , LSTG_FORMAT_NAME from test_kylin_fact group by LSTG_FORMAT_NAME
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_ii/query04.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_ii/query04.sql b/kylin-it/src/test/resources/query/sql_ii/query04.sql
new file mode 100644
index 0000000..4a75fba
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_ii/query04.sql
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select TRANS_ID,CAL_DT,LSTG_FORMAT_NAME,LEAF_CATEG_ID,LSTG_SITE_ID,SLR_SEGMENT_CD,PRICE,SELLER_ID from test_kylin_fact where LSTG_FORMAT_NAME = 'ABIN'
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_ii/query05.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_ii/query05.sql b/kylin-it/src/test/resources/query/sql_ii/query05.sql
new file mode 100644
index 0000000..4043a2c
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_ii/query05.sql
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select sum(price) as price_sum, min(price) as price_min , max(price) as price_max , SELLER_ID from test_kylin_fact where SELLER_ID > 10000149 group by SELLER_ID
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_ii/query06.sql.disabled
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_ii/query06.sql.disabled b/kylin-it/src/test/resources/query/sql_ii/query06.sql.disabled
new file mode 100644
index 0000000..f97f440
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_ii/query06.sql.disabled
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select price from test_kylin_fact where price > 50
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_ii/query07.sql.disabled
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_ii/query07.sql.disabled b/kylin-it/src/test/resources/query/sql_ii/query07.sql.disabled
new file mode 100644
index 0000000..0471a47
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_ii/query07.sql.disabled
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select sum(price) as price_sum, min(price) as price_min , max(price) as price_max , LSTG_FORMAT_NAME from test_kylin_fact where price > 50 group by LSTG_FORMAT_NAME
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_ii/query08.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_ii/query08.sql b/kylin-it/src/test/resources/query/sql_ii/query08.sql
new file mode 100644
index 0000000..51a676d
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_ii/query08.sql
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select TRANS_ID,CAL_DT,LSTG_FORMAT_NAME,LEAF_CATEG_ID,LSTG_SITE_ID,SLR_SEGMENT_CD,PRICE,SELLER_ID from test_kylin_fact where LSTG_FORMAT_NAME > 'ABIN'
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_ii/query09.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_ii/query09.sql b/kylin-it/src/test/resources/query/sql_ii/query09.sql
new file mode 100644
index 0000000..1562288
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_ii/query09.sql
@@ -0,0 +1,19 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+select price, SELLER_ID from test_kylin_fact where SELLER_ID > 10000149
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_invalid/query29_invalid_SQL.sql
----------------------------------------------------------------------
diff --git a/kylin-it/src/test/resources/query/sql_invalid/query29_invalid_SQL.sql b/kylin-it/src/test/resources/query/sql_invalid/query29_invalid_SQL.sql
new file mode 100644
index 0000000..7d2b1b5
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_invalid/query29_invalid_SQL.sql
@@ -0,0 +1,55 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+
+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 edw.test_sites as 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