This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 3780487f17d Add more SQL test case for create-materialized-view
(#29081)
3780487f17d is described below
commit 3780487f17d7eee096ee0ee281beb84fe0a9baa9
Author: +7 <[email protected]>
AuthorDate: Mon Nov 20 08:59:07 2023 +0800
Add more SQL test case for create-materialized-view (#29081)
---
.../case/ddl/create-materialized-view.xml | 2 ++
.../sql/supported/ddl/create-materialized-view.xml | 33 ++++++++++++++++++++++
2 files changed, 35 insertions(+)
diff --git
a/test/it/parser/src/main/resources/case/ddl/create-materialized-view.xml
b/test/it/parser/src/main/resources/case/ddl/create-materialized-view.xml
index 9a6325092e1..9c0b1d84433 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-materialized-view.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-materialized-view.xml
@@ -31,4 +31,6 @@
<create-materialized-view
sql-case-id="create_materialized_view_for_update" />
<create-materialized-view
sql-case-id="create_materialized_view_with_refresh_force" />
<create-materialized-view
sql-case-id="create_materialized_view_with_refresh_fast_for_update_as_with_recursive"
/>
+ <create-materialized-view sql-case-id="create_materialized_view_scope_for"
/>
+ <create-materialized-view
sql-case-id="create_materialized_view_with_refresh_complete" />
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-materialized-view.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-materialized-view.xml
index b2754b3a647..c010002ed91 100644
---
a/test/it/parser/src/main/resources/sql/supported/ddl/create-materialized-view.xml
+++
b/test/it/parser/src/main/resources/sql/supported/ddl/create-materialized-view.xml
@@ -89,4 +89,37 @@
(SELECT * FROM [email protected] l
WHERE l.country_id = 'UK'
AND d.location_id = l.location_id));" db-types="Oracle" />
+ <sql-case id="create_materialized_view_scope_for" value="CREATE
MATERIALIZED VIEW oe.customers_with_ref_mv
+ (SCOPE FOR (cust_address) IS oe.cust_address_objtab_mv)
+ AS SELECT * FROM [email protected];"
db-types="Oracle" />
+ <sql-case id="create_materialized_view_with_refresh_complete"
value="CREATE MATERIALIZED VIEW mv_prod_time
+ REFRESH COMPLETE ON DEMAND AS
+ SELECT
+ (CASE
+ WHEN ((GROUPING(calendar_year)=0 )
+ AND (GROUPING(calendar_quarter_desc)=1 ))
+ THEN (TO_CHAR(calendar_year) || '_0')
+ WHEN ((GROUPING(calendar_quarter_desc)=0 )
+ AND (GROUPING(calendar_month_desc)=1 ))
+ THEN (TO_CHAR(calendar_quarter_desc) || '_1')
+ WHEN ((GROUPING(calendar_month_desc)=0 )
+ AND (GROUPING(t.time_id)=1 ))
+ THEN (TO_CHAR(calendar_month_desc) || '_2')
+ ELSE (TO_CHAR(t.time_id) || '_3')
+ END) Hierarchical_Time,
+ calendar_year year, calendar_quarter_desc quarter,
+ calendar_month_desc month, t.time_id day,
+ prod_category cat, prod_subcategory subcat, p.prod_id prod,
+ GROUPING_ID(prod_category, prod_subcategory, p.prod_id,
+ calendar_year, calendar_quarter_desc, calendar_month_desc,t.time_id)
gid,
+ GROUPING_ID(prod_category, prod_subcategory, p.prod_id) gid_p,
+ GROUPING_ID(calendar_year, calendar_quarter_desc,
+ calendar_month_desc, t.time_id) gid_t,
+ SUM(amount_sold) s_sold, COUNT(amount_sold) c_sold, COUNT(*) cnt
+ FROM SALES s, TIMES t, PRODUCTS p
+ WHERE s.time_id = t.time_id AND
+ p.prod_name IN ('Bounce', 'Y Box') AND s.prod_id = p.prod_id
+ GROUP BY
+ ROLLUP(calendar_year, calendar_quarter_desc, calendar_month_desc,
t.time_id),
+ ROLLUP(prod_category, prod_subcategory, p.prod_id);" db-types="Oracle" />
</sql-cases>
