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 2a8cc401d9d Feat: Support parsing Oracle CREATE MATERIALIZED sql
(#27994)
2a8cc401d9d is described below
commit 2a8cc401d9d1c1387df410507a6126e2822a3a9a
Author: Feng Zhang <[email protected]>
AuthorDate: Mon Sep 18 18:02:49 2023 -0700
Feat: Support parsing Oracle CREATE MATERIALIZED sql (#27994)
* Feat: Support parsing Oracle CREATE MATERIALIZED sql
* add final
* refactor ddl g4 to inline grammer
* refactor ddl and dml g4
* add back additional ddl g4 rules
---
.../src/main/antlr4/imports/oracle/DDLStatement.g4 | 50 ++++++++++++++++++++++
.../sql/parser/autogen/OracleStatement.g4 | 2 +
.../statement/type/OracleDDLStatementVisitor.java | 14 ++++++
.../core/database/visitor/SQLVisitorRule.java | 2 +
.../OracleCreateMaterializedViewLogStatement.java | 27 ++++++++++++
.../ddl/OracleCreateMaterializedViewStatement.java | 27 ++++++++++++
.../cases/parser/jaxb/RootSQLParserTestCases.java | 4 ++
...CreateMaterializedViewLogStatementTestCase.java | 26 +++++++++++
...d-view.xml => create-materialized-view-log.xml} | 11 ++---
.../case/ddl/create-materialized-view.xml | 2 +
.../supported/ddl/create-materialized-view-log.xml | 31 ++++++++++++++
.../sql/supported/ddl/create-materialized-view.xml | 11 +++++
12 files changed, 202 insertions(+), 5 deletions(-)
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
index 7a35c2a4372..568a20d1a74 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
@@ -3397,6 +3397,56 @@ arryDMLSubClause
: LP_ typeName (COMMA_ varrayType)? RP_
;
+createMaterializedView
+ : CREATE MATERIALIZED VIEW materializedViewName (OF typeName )?
materializedViewColumnClause? materializedViewPrebuiltClause
materializedViewUsingClause? createMvRefresh? (FOR UPDATE)? ( (DISABLE |
ENABLE) QUERY REWRITE )? AS selectSubquery
+ ;
+
+materializedViewColumnClause
+ : ( LP_ (scopedTableRefConstraint | mvColumnAlias) (COMMA_
(scopedTableRefConstraint | mvColumnAlias))* RP_ )
+ ;
+
+materializedViewPrebuiltClause
+ : ( ON PREBUILT TABLE ( (WITH | WITHOUT) REDUCED PRECISION)? |
physicalProperties? (CACHE | NOCACHE)? parallelClause? buildClause?)
+ ;
+
+materializedViewUsingClause
+ : ( USING INDEX ( (physicalAttributesClause | TABLESPACE tablespaceName)+
)* | USING NO INDEX)
+ ;
+
+mvColumnAlias
+ : (identifier | quotedString) (ENCRYPT encryptionSpecification)?
+ ;
+
+createMvRefresh
+ : ( NEVER REFRESH | REFRESH createMvRefreshOptions+)
+ ;
+
+createMvRefreshOptions
+ : ( (FAST | COMPLETE | FORCE) | ON (DEMAND | COMMIT) | (START WITH | NEXT)
| WITH (PRIMARY KEY | ROWID) | USING ( DEFAULT (MASTER LOCAL)? ROLLBACK SEGMENT
| (MASTER | LOCAL)? ROLLBACK SEGMENT rb_segment=REGULAR_ID ) | USING (ENFORCED
| TRUSTED) CONSTRAINTS)
+ ;
+
+quotedString
+ : variableName
+ | CHAR_STRING
+ | NATIONAL_CHAR_STRING_LIT
+ ;
+
+buildClause
+ : BUILD (IMMEDIATE | DEFERRED)
+ ;
+
+createMaterializedViewLog
+ : CREATE MATERIALIZED VIEW LOG ON tableName materializedViewLogAttribute?
parallelClause? ( WITH ( COMMA_? ( OBJECT ID | PRIMARY KEY | ROWID | SEQUENCE |
COMMIT SCN ) )* (LP_ ( COMMA_? identifier )+ RP_ newViewValuesClause? )?
mvLogPurgeClause? )*
+ ;
+
+materializedViewLogAttribute
+ : ( ( physicalAttributesClause | TABLESPACE tablespaceName | loggingClause
| (CACHE | NOCACHE))+ )
+ ;
+
+newViewValuesClause
+ : (INCLUDING | EXCLUDING ) NEW VALUES
+ ;
+
alterMaterializedView
: ALTER MATERIALIZED VIEW materializedViewName materializedViewAttribute?
alterIotClauses? (USING INDEX physicalAttributesClause)?
((MODIFY scopedTableRefConstraint) | alterMvRefresh)?
evaluationEditionClause?
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/OracleStatement.g4
b/parser/sql/dialect/oracle/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/OracleStatement.g4
index c8a8306d123..92800a99bca 100644
---
a/parser/sql/dialect/oracle/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/OracleStatement.g4
+++
b/parser/sql/dialect/oracle/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/OracleStatement.g4
@@ -121,6 +121,8 @@ execute
| alterRollbackSegment
| alterDiskgroup
| alterIndexType
+ | createMaterializedView
+ | createMaterializedViewLog
| alterMaterializedView
| alterMaterializedViewLog
| alterFunction
diff --git
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDDLStatementVisitor.java
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDDLStatementVisitor.java
index 926dba4f3d2..20c623fc534 100644
---
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDDLStatementVisitor.java
+++
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDDLStatementVisitor.java
@@ -94,6 +94,8 @@ import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.Create
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.CreateSynonymContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.CreateTableContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.CreateTablespaceContext;
+import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.CreateMaterializedViewContext;
+import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.CreateMaterializedViewLogContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.CreateTypeContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.CreateViewContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.DataTypeDefinitionContext;
@@ -256,6 +258,8 @@ import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.Ora
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleCreateSynonymStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleCreateTableStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleCreateTablespaceStatement;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleCreateMaterializedViewStatement;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleCreateMaterializedViewLogStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleCreateViewStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleCreateVarrayTypeStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl.OracleDisassociateStatisticsStatement;
@@ -1299,6 +1303,16 @@ public final class OracleDDLStatementVisitor extends
OracleStatementVisitor impl
return new OracleCreateTablespaceStatement();
}
+ @Override
+ public ASTNode visitCreateMaterializedView(final
CreateMaterializedViewContext ctx) {
+ return new OracleCreateMaterializedViewStatement();
+ }
+
+ @Override
+ public ASTNode visitCreateMaterializedViewLog(final
CreateMaterializedViewLogContext ctx) {
+ return new OracleCreateMaterializedViewLogStatement();
+ }
+
@Override
public ASTNode visitCreateCluster(final CreateClusterContext ctx) {
return new OracleCreateClusterStatement();
diff --git
a/parser/sql/engine/src/main/java/org/apache/shardingsphere/sql/parser/core/database/visitor/SQLVisitorRule.java
b/parser/sql/engine/src/main/java/org/apache/shardingsphere/sql/parser/core/database/visitor/SQLVisitorRule.java
index 197fcb86fb8..14edbebacd0 100644
---
a/parser/sql/engine/src/main/java/org/apache/shardingsphere/sql/parser/core/database/visitor/SQLVisitorRule.java
+++
b/parser/sql/engine/src/main/java/org/apache/shardingsphere/sql/parser/core/database/visitor/SQLVisitorRule.java
@@ -667,6 +667,8 @@ public enum SQLVisitorRule {
CREATE_MATERIALIZED_VIEW("CreateMaterializedView", SQLStatementType.DDL),
+ CREATE_MATERIALIZED_VIEW_LOG("CreateMaterializedViewLog",
SQLStatementType.DDL),
+
CREATE_OPERATOR("CreateOperator", SQLStatementType.DDL),
CREATE_POLICY("CreatePolicy", SQLStatementType.DDL),
diff --git
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateMaterializedViewLogStatement.java
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateMaterializedViewLogStatement.java
new file mode 100644
index 00000000000..983760bf84a
--- /dev/null
+++
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateMaterializedViewLogStatement.java
@@ -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.
+ */
+
+package org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl;
+
+import
org.apache.shardingsphere.sql.parser.sql.common.statement.AbstractSQLStatement;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.OracleStatement;
+
+/**
+ * Oracle create materialized view log statement.
+ */
+public final class OracleCreateMaterializedViewLogStatement extends
AbstractSQLStatement implements OracleStatement {
+}
diff --git
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateMaterializedViewStatement.java
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateMaterializedViewStatement.java
new file mode 100644
index 00000000000..c3e1c98549c
--- /dev/null
+++
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/oracle/ddl/OracleCreateMaterializedViewStatement.java
@@ -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.
+ */
+
+package org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.ddl;
+
+import
org.apache.shardingsphere.sql.parser.sql.common.statement.ddl.CreateMaterializedViewStatement;
+import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.oracle.OracleStatement;
+
+/**
+ * OracleSQL create materialized view statement.
+ */
+public final class OracleCreateMaterializedViewStatement extends
CreateMaterializedViewStatement implements OracleStatement {
+}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/RootSQLParserTestCases.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/RootSQLParserTestCases.java
index 191e946c8c7..88821886f53 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/RootSQLParserTestCases.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/RootSQLParserTestCases.java
@@ -183,6 +183,7 @@ import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.s
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.CreateInmemoryJoinGroupStatementTestCase;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.CreateLanguageStatementTestCase;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.CreateLockdownProfileStatementTestCase;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.CreateMaterializedViewLogStatementTestCase;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.CreateMaterializedViewStatementTestCase;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.CreateOperatorStatementTestCase;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl.CreatePFileStatementTestCase;
@@ -1666,6 +1667,9 @@ public final class RootSQLParserTestCases {
@XmlElement(name = "create-materialized-view")
private final List<CreateMaterializedViewStatementTestCase>
createMaterializedViewStatementTestCases = new LinkedList<>();
+ @XmlElement(name = "create-materialized-view-log")
+ private final List<CreateMaterializedViewLogStatementTestCase>
createMaterializedViewLogStatementTestCases = new LinkedList<>();
+
@XmlElement(name = "create-operator")
private final List<CreateOperatorStatementTestCase>
createOperatorStatementTestCases = new LinkedList<>();
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/ddl/CreateMaterializedViewLogStatementTestCase.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/ddl/CreateMaterializedViewLogStatementTestCase.java
new file mode 100644
index 00000000000..7366ded65db
--- /dev/null
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/ddl/CreateMaterializedViewLogStatementTestCase.java
@@ -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.
+ */
+
+package
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.statement.ddl;
+
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.SQLParserTestCase;
+
+/**
+ * Create materialized view statement test case.
+ */
+public final class CreateMaterializedViewLogStatementTestCase extends
SQLParserTestCase {
+}
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-log.xml
similarity index 61%
copy from
test/it/parser/src/main/resources/case/ddl/create-materialized-view.xml
copy to
test/it/parser/src/main/resources/case/ddl/create-materialized-view-log.xml
index ffbdfdded5f..71de1e31278 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-log.xml
@@ -17,9 +17,10 @@
-->
<sql-parser-test-cases>
- <create-materialized-view
sql-case-id="create_materialized_view_with_if_not_exists" />
- <create-materialized-view sql-case-id="create_materialized_view" />
- <create-materialized-view
sql-case-id="create_materialized_view_with_using" />
- <create-materialized-view
sql-case-id="create_materialized_view_with_no_data" />
- <create-materialized-view sql-case-id="create_materialized_view_with_data"
/>
+ <create-materialized-view-log
sql-case-id="create_materialized_view_log_with" />
+ <create-materialized-view-log
sql-case-id="create_materialized_view_log_with_including_new" />
+ <create-materialized-view-log
sql-case-id="create_materialized_view_log_with_tablespace" />
+ <create-materialized-view-log
sql-case-id="create_materialized_view_log_with_table_schema" />
+ <create-materialized-view-log
sql-case-id="create_materialized_view_log_with_object_id" />
+ <create-materialized-view-log
sql-case-id="create_materialized_view_log_with_row_id" />
</sql-parser-test-cases>
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 ffbdfdded5f..20e6f11f159 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
@@ -22,4 +22,6 @@
<create-materialized-view
sql-case-id="create_materialized_view_with_using" />
<create-materialized-view
sql-case-id="create_materialized_view_with_no_data" />
<create-materialized-view sql-case-id="create_materialized_view_with_data"
/>
+ <create-materialized-view
sql-case-id="create_materialized_view_with_refresh_fast" />
+ <create-materialized-view
sql-case-id="create_materialized_view_with_refresh_fast_query_rewrite" />
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-materialized-view-log.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-materialized-view-log.xml
new file mode 100644
index 00000000000..0baea22fa2a
--- /dev/null
+++
b/test/it/parser/src/main/resources/sql/supported/ddl/create-materialized-view-log.xml
@@ -0,0 +1,31 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ 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.
+ -->
+
+<sql-cases>
+ <sql-case id="create_materialized_view_log_with" value="CREATE
MATERIALIZED VIEW LOG ON inventories WITH (quantity_on_hand);"
db-types="Oracle" />
+ <sql-case id="create_materialized_view_log_with_including_new"
value="CREATE MATERIALIZED VIEW
+ LOG ON employees WITH PRIMARY KEY INCLUDING NEW VALUES;" db-types="Oracle"
/>
+ <sql-case id="create_materialized_view_log_with_tablespace" value="CREATE
MATERIALIZED VIEW
+ LOG ON emp_data PCTFREE 5 PCTUSED 60 TABLESPACE example STORAGE (INITIAL
50K)
+ REFRESH FAST NEXT sysdate + 7 AS SELECT * FROM employees;"
db-types="Oracle" />
+ <sql-case id="create_materialized_view_log_with_table_schema"
value="CREATE MATERIALIZED VIEW
+ LOG ON "SH"."CUSTOMERS" WITH ROWID,
SEQUENCE("CUST_ID")
+ INCLUDING NEW VALUES;" db-types="Oracle" />
+ <sql-case id="create_materialized_view_log_with_object_id" value="CREATE
MATERIALIZED VIEW LOG ON oe.categories_tab_sys WITH OBJECT ID;"
db-types="Oracle" />
+ <sql-case id="create_materialized_view_log_with_row_id" value="CREATE
MATERIALIZED VIEW LOG ON sales WITH ROWID;" db-types="Oracle" />
+</sql-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 3bc8aa4d22e..1db36d160a4 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
@@ -22,4 +22,15 @@
<sql-case id="create_materialized_view_with_using" value="CREATE
MATERIALIZED VIEW mat_view_heap_psql USING heap_psql AS SELECT f1 from
tbl_heap_psql;" db-types="PostgreSQL" />
<sql-case id="create_materialized_view_with_no_data" value="CREATE
MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1,
10) WITH NO DATA;" db-types="PostgreSQL" />
<sql-case id="create_materialized_view_with_data" value="CREATE
MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT
generate_series(1, 10) WITH DATA;" db-types="PostgreSQL" />
+ <sql-case id="create_materialized_view_with_refresh_fast" value="CREATE
MATERIALIZED VIEW warranty_orders REFRESH FAST AS
+ SELECT order_id, line_item_id, product_id FROM order_items WHERE EXISTS
+ (SELECT * FROM inventories i WHERE o.product_id = i.product_id AND
i.quantity_on_hand IS NOT NULL)
+ UNION
+ SELECT order_id, line_item_id, product_id FROM order_items WHERE quantity
> 5;" db-types="Oracle" />
+ <sql-case id="create_materialized_view_with_refresh_fast_query_rewrite"
value="CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1
+ REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE
+ AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2,
+ SUM(SH.SALES.AMOUNT_SOLD) M1, COUNT(SH.SALES.AMOUNT_SOLD) M2, COUNT(*) M3
FROM SH.SALES, SH.CUSTOMERS
+ WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID AND (SH.SALES.CUST_ID IN
(1012, 1010, 1005))
+ GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID;" db-types="Oracle" />
</sql-cases>
