ThanoshanMV commented on a change in pull request #11503:
URL: https://github.com/apache/shardingsphere/pull/11503#discussion_r680545500
##########
File path:
shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml
##########
@@ -92,4 +92,14 @@
<sql-case id="select_with_containers" value="SELECT * FROM
CONTAINERS(employees)" db-types="Oracle" />
<sql-case id="select_with_hierarchical_connect_by" value="SELECT
last_name, employee_id, manager_id FROM employees CONNECT BY employee_id =
manager_id ORDER BY last_name" db-types="Oracle" />
<sql-case id="select_union" value="SELECT order_id FROM t_order UNION
SELECT order_id FROM t_order_item" db-types="MySQL" />
+ <sql-case id="select_with_model_partition_dimension" value="SELECT
country, prod, year, s FROM sales_view_ref MODEL PARTITION BY (country)
DIMENSION BY (prod, year) MEASURES (sale s) IGNORE NAV UNIQUE DIMENSION RULES
UPSERT SEQUENTIAL ORDER (s[prod='Mouse Pad', year=2001] = s['Mouse Pad', 1999]
+ s['Mouse Pad', 2000],
+ s['Standard Mouse', 2002] = s['Standard Mouse', 2001]) ORDER BY country,
prod, year" db-types="Oracle" />
+ <sql-case id="select_with_model_dimension" value="SELECT country, year,
sale, csum FROM (SELECT country, year, SUM(sale) sale FROM sales_view_ref GROUP
BY country, year) MODEL DIMENSION BY (country, year) MEASURES (sale, 0 csum)
RULES (csum[any, any] = SUM(sale) OVER (PARTITION BY country ORDER BY year ROWS
UNBOUNDED PRECEDING)) ORDER BY country, year" db-types="Oracle" />
+ <sql-case id="select_with_model_with_single_column_for_loop" value="SELECT
SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod, year, sales FROM
sales_view WHERE country='Italy' MODEL RETURN UPDATED ROWS PARTITION BY
(country) DIMENSION BY (prod, year) MEASURES (sale sales) RULES (sales[FOR prod
in ('Mouse Pad', 'Bounce', 'Y Box'), 2005] = 1.3 * sales[cv(prod), 2001]) ORDER
BY country, prod, year" db-types="Oracle" />
+ <sql-case id="select_with_model_with_reference_model" value="SELECT
SUBSTR(country,1,20) country, year, localsales, dollarsales FROM sales_view
WHERE country IN ('Canada', 'Brazil') GROUP BY country, year MODEL RETURN
UPDATED ROWS REFERENCE conv_refmodel ON (SELECT country, exchange_rate AS er
FROM dollar_conv) DIMENSION BY (country) MEASURES (er) IGNORE NAV MAIN
main_model DIMENSION BY (country, year)
+ MEASURES (SUM(sale) sales, 0 localsales, 0 dollarsales) IGNORE NAV RULES
(localsales['Canada', 2005] = sales[cv(country), 2001] * 1.22,
dollarsales['Canada', 2005] = sales[cv(country), 2001] * 1.22 *
conv_refmodel.er['Canada'], localsales['Brazil', 2005] = sales[cv(country),
2001] * 1.34, dollarsales['Brazil', 2005] = sales['Brazil', 2001] * 1.34 *
er['Brazil'])" db-types="Oracle" />
+ <sql-case id="select_with_model_with_reference_models" value="SELECT
SUBSTR(country,1,20) country, year, localsales, dollarsales FROM sales_view
WHERE country IN ('Canada','Brazil') GROUP BY country, year MODEL RETURN
UPDATED ROWS REFERENCE conv_refmodel ON (SELECT country, exchange_rate FROM
dollar_conv) DIMENSION BY (country c) MEASURES (exchange_rate er) IGNORE NAV
REFERENCE growth_refmodel ON (SELECT country, year, growth_rate FROM
growth_rate) DIMENSION BY (country c, year y)
Review comment:
With the previous `(owner DOT_)? name LBT_ expr (COMMA_ expr)* RBT_
simpleExpr?` rule, the above SQLs were executed successfully.
After removing the inappropriate above rule and changing `modelRuleClause`'s
right-hand side `expr` to `modelExpr`, those above SQLs weren't executed
successfully.
Except for SQL case ids `select_with_model_partition_dimension` and
`select_with_model_dimension`, other SQLs were taken from [external
source](https://docs.oracle.com/cd/B19306_01/server.102/b14223/sqlmodel.htm)
and not from [official
documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6).
I added external source SQLs to check how parsing happens in
`singleColumnForLoop`, `referenceModel` and so on.
The parsing fails in `modelExpr`, here's an example with SQL case id
`select_with_model_with_single_column_for_loop`:
I'll comment on all external source SQLs. Maybe it's better to remove the
external source SQLs as we don't know whether they are aligned with Oracle 19?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
