This is an automated email from the ASF dual-hosted git repository.
zhangliang 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 3cecf63c34e Add more test cases on presto SQL parser (#37621)
3cecf63c34e is described below
commit 3cecf63c34ef63b8fa6923e97357496aca8d3c11
Author: Liang Zhang <[email protected]>
AuthorDate: Fri Jan 2 18:32:13 2026 +0800
Add more test cases on presto SQL parser (#37621)
---
.../parser/src/main/resources/case/dml/presto.xml | 107 +++++++++++++++++++++
.../resources/sql/supported/ddl/create-table.xml | 2 +-
.../main/resources/sql/supported/ddl/drop-view.xml | 2 +-
.../main/resources/sql/supported/dml/delete.xml | 2 +-
.../main/resources/sql/supported/dml/insert.xml | 10 +-
.../main/resources/sql/supported/dml/presto.xml | 2 +
.../sql/supported/dml/select-expression.xml | 4 +-
.../resources/sql/supported/dml/select-lock.xml | 2 +-
.../dml/select-pagination-group-by-order-by.xml | 2 +-
.../sql/supported/dml/select-special-function.xml | 6 +-
.../main/resources/sql/supported/dml/select.xml | 6 +-
.../main/resources/sql/supported/dml/update.xml | 2 +-
.../main/resources/sql/supported/dml/values.xml | 2 +-
13 files changed, 129 insertions(+), 20 deletions(-)
diff --git a/test/it/parser/src/main/resources/case/dml/presto.xml
b/test/it/parser/src/main/resources/case/dml/presto.xml
index e2eea14bed2..f1d47a0f003 100644
--- a/test/it/parser/src/main/resources/case/dml/presto.xml
+++ b/test/it/parser/src/main/resources/case/dml/presto.xml
@@ -701,4 +701,111 @@
</from>
</select>
+ <select sql-case-id="presto_select_union">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="table1" start-index="14" stop-index="19" />
+ </from>
+ <combine combine-type="UNION" start-index="21" stop-index="46">
+ <left>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="table1" start-index="14"
stop-index="19" />
+ </from>
+ </left>
+ <right>
+ <projections start-index="34" stop-index="34">
+ <shorthand-projection start-index="34" stop-index="34" />
+ </projections>
+ <from>
+ <simple-table name="table2" start-index="41"
stop-index="46" />
+ </from>
+ </right>
+ </combine>
+ </select>
+
+ <select sql-case-id="presto_select_union_all">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="table1" start-index="14" stop-index="19" />
+ </from>
+ <combine combine-type="UNION_ALL" start-index="21" stop-index="50">
+ <left>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="table1" start-index="14"
stop-index="19" />
+ </from>
+ </left>
+ <right>
+ <projections start-index="38" stop-index="38">
+ <shorthand-projection start-index="38" stop-index="38" />
+ </projections>
+ <from>
+ <simple-table name="table2" start-index="45"
stop-index="50" />
+ </from>
+ </right>
+ </combine>
+ </select>
+
+ <select sql-case-id="presto_select_with_except">
+ <projections start-index="8" stop-index="8">
+ <shorthand-projection start-index="8" stop-index="8" />
+ </projections>
+ <from>
+ <simple-table name="t1" start-index="15" stop-index="16" />
+ </from>
+ <combine combine-type="EXCEPT" start-index="19" stop-index="43">
+ <left>
+ <projections start-index="8" stop-index="8">
+ <shorthand-projection start-index="8" stop-index="8" />
+ </projections>
+ <from>
+ <simple-table name="t1" start-index="15" stop-index="16" />
+ </from>
+ </left>
+ <right>
+ <projections start-index="34" stop-index="34">
+ <shorthand-projection start-index="34" stop-index="34" />
+ </projections>
+ <from>
+ <simple-table name="t2" start-index="41" stop-index="42" />
+ </from>
+ </right>
+ </combine>
+ </select>
+
+ <select sql-case-id="presto_select_limit_only">
+ <from>
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <limit start-index="22" stop-index="28">
+ <row-count value="5" start-index="28" stop-index="28" />
+ </limit>
+ </select>
+
+ <select sql-case-id="presto_select_current_time_precision">
+ <projections start-index="7" stop-index="21">
+ <expression-projection text="CURRENT_TIME(3)" start-index="7"
stop-index="21">
+ <expr>
+ <function function-name="CURRENT_TIME"
text="CURRENT_TIME(3)" start-index="7" stop-index="21">
+ <parameter>
+ <literal-expression value="3" start-index="20"
stop-index="20" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
index b0996e021a7..c2d2aba987a 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
@@ -27,7 +27,7 @@
<sql-case id="create_table" value="CREATE TABLE t_log(id int PRIMARY KEY,
status varchar(10))" />
<sql-case id="create_table_with_engin_charset" value="CREATE TABLE
t_log(id int PRIMARY KEY, status varchar(10)) ENGINE=InnoDB DEFAULT
CHARSET=utf8mb4" db-types="MySQL" />
<sql-case id="create_table_with_keyword" value="CREATE TABLE t_log(id int
PRIMARY KEY, status boolean)" db-types="PostgreSQL,openGauss" />
- <sql-case id="create_table_if_not_exists" value="CREATE TABLE IF NOT
EXISTS t_log(id int, status varchar(10))" db-types="MySQL,PostgreSQL,openGauss"
/>
+ <sql-case id="create_table_if_not_exists" value="CREATE TABLE IF NOT
EXISTS t_log(id int, status varchar(10))"
db-types="MySQL,PostgreSQL,openGauss,Presto" />
<sql-case id="create_temporary_table_if_not_exists" value="CREATE
TEMPORARY TABLE IF NOT EXISTS t_temp_log(id int, status varchar(10))"
db-types="MySQL,PostgreSQL,openGauss" />
<sql-case id="create_global_temporary_table" value="CREATE GLOBAL
TEMPORARY TABLE t_temp_log(id int, status varchar(10))"
db-types="Oracle,PostgreSQL,openGauss" />
<sql-case id="create_private_temporary_table" value="CREATE PRIVATE
TEMPORARY TABLE t_temp_log(id int, status varchar(10))" db-types="Oracle" />
diff --git a/test/it/parser/src/main/resources/sql/supported/ddl/drop-view.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/drop-view.xml
index 0c58f4d851b..ebcde77c257 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/drop-view.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/drop-view.xml
@@ -23,6 +23,6 @@
<sql-case id="drop_view_cascade" value="DROP VIEW kinds, order_view
CASCADE" db-types="PostgreSQL, openGauss" />
<sql-case id="drop_view_basic" value="DROP VIEW sales_summary"
db-types="Hive" />
<sql-case id="drop_view_with_if_exists" value="DROP VIEW IF EXISTS
sales_summary" db-types="Hive" />
- <sql-case id="drop_view_with_db_name" value="DROP VIEW db1.sales_summary"
db-types="Hive" />
+ <sql-case id="drop_view_with_db_name" value="DROP VIEW db1.sales_summary"
db-types="Hive,Presto" />
<sql-case id="drop_view_with_if_exists_and_db_name" value="DROP VIEW IF
EXISTS db1.sales_summary" db-types="Hive" />
</sql-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/delete.xml
b/test/it/parser/src/main/resources/sql/supported/dml/delete.xml
index 7c577b496a5..890ae205dbf 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/delete.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/delete.xml
@@ -32,7 +32,7 @@
<sql-case id="delete_with_with_clause" value="WITH cte (order_id, user_id)
AS (SELECT order_id, user_id FROM t_order) DELETE t_order FROM cte WHERE
t_order.order_id = cte.order_id" db-types="SQLServer" />
<sql-case id="delete_using_with_clause" value="WITH cte AS (SELECT
order_id FROM t_order WHERE order_id = 1000) DELETE FROM t_order USING t_order
JOIN cte ON t_order.order_id = cte.order_id" db-types="MySQL" />
<sql-case id="delete_without_columns_with_with_clause" value="WITH cte AS
(SELECT order_id, user_id FROM t_order) DELETE t_order FROM cte WHERE
t_order.order_id = cte.order_id" db-types="SQLServer" />
- <sql-case id="delete_multi_tables" value="DELETE t_order, t_order_item
from t_order, t_order_item where t_order.order_id = t_order_item.order_id and
t_order.status = ?" db-types="MySQL" />
+ <sql-case id="delete_multi_tables" value="DELETE t_order, t_order_item
from t_order, t_order_item where t_order.order_id = t_order_item.order_id and
t_order.status = ?" db-types="MySQL,Presto" />
<sql-case id="delete_multi_tables_with_using" value="DELETE from t_order,
t_order_item using t_order left join t_order_item on t_order.order_id =
t_order_item.order_id where t_order.status = ?" db-types="MySQL" />
<sql-case id="delete_with_query_hint" value="DELETE FROM t_order WHERE
order_id = ?" db-types="SQLServer" />
<sql-case id="delete_with_simple_table" value="DELETE FROM
product_descriptions WHERE language_id = 'AR'" db-types="Oracle" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
index f31e49825b5..0f3491e21e1 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
@@ -17,7 +17,7 @@
-->
<sql-cases>
- <sql-case id="insert_into_values1" value="INSERT INTO test_nested
VALUES(1)" db-types="MySQL" />
+ <sql-case id="insert_into_values1" value="INSERT INTO test_nested
VALUES(1)" db-types="MySQL,Presto" />
<sql-case id="insert_into_values2" value="insert into emp_table
values(1,'John',1000.00,'Architect')" db-types="Oracle" />
<sql-case id="insert_into_values3" value="insert into emp_table
values(2,'Robert',900.00,'Developer')" db-types="Oracle" />
<sql-case id="insert_into_values4" value="insert into emp_table
values(3,'James',2000.00,'Director')" db-types="Oracle" />
@@ -32,7 +32,7 @@
<sql-case id="insert_with_special_syntax" value="INSERT /*+ index(field1)
*/ INTO t_order (order_id, user_id, status) VALUES (1, 1, 'insert') RETURNING
order_id, *, t_order.*, user_id u, t_order.status as s, 'OK' result"
db-types="PostgreSQL,openGauss" />
<sql-case id="insert_with_all_placeholders_for_table_identifier"
value="INSERT INTO t_order (t_order.order_id, t_order.user_id, t_order.status)
VALUES (?, ?, ?)" />
<sql-case id="insert_without_columns_with_all_placeholders" value="INSERT
INTO t_order VALUES (?, ?, ?)" />
- <sql-case id="insert_set_with_all_placeholders" value="INSERT INTO t_order
SET order_id = ?, user_id = ?, status = ?" db-types="MySQL" />
+ <sql-case id="insert_set_with_all_placeholders" value="INSERT INTO t_order
SET order_id = ?, user_id = ?, status = ?" db-types="MySQL,Presto" />
<sql-case id="insert_duplicate_key_update" value="INSERT INTO t_order SET
b='11', a=0 AS n ON DUPLICATE KEY UPDATE b=n.a, a=n.b" db-types="MySQL" />
<sql-case id="insert_with_underscore_charset" value="INSERT INTO t_order
VALUES(_utf16 0x1EC2), (_utf16 0x1EC3), (_utf16 0x1EC5), (_utf16 0x1EC0),
(_utf16 0x1EC7), (_Utf16 0x1EBF)" db-types="MySQL" />
<sql-case id="insert_set_with_all_placeholders_for_table_identifier"
value="INSERT INTO t_order SET t_order.order_id = ?, t_order.user_id = ?,
t_order.status = ?" db-types="MySQL" />
@@ -49,8 +49,8 @@
<sql-case id="insert_with_batch_and_composite_expression" value="INSERT
INTO t_order (order_id, user_id, status) VALUES (?, ?, SUBSTR(?, 1)), (?, ?,
SUBSTR(?, 1))" db-types="H2,MySQL" />
<sql-case id="insert_with_batch_and_with_generate_key_column"
value="INSERT INTO t_order_item(item_id, order_id, user_id, status,
creation_date) values (?, ?, ?, 'insert', '2017-08-08'), (?, ?, ?, 'insert',
'2017-08-08')" db-types="MySQL, SQLServer, PostgreSQL,openGauss" />
<sql-case id="insert_with_batch_and_without_generate_key_column"
value="INSERT INTO t_order_item(order_id, user_id, status, creation_date)
values (?, ?, 'insert', '2017-08-08'), (?, ?, 'insert', '2017-08-08')"
db-types="MySQL, SQLServer, PostgreSQL,openGauss" />
- <sql-case id="insert_on_duplicate_key_update" value="INSERT INTO t_order
(order_id, user_id, status) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE status =
VALUES(status)" db-types="MySQL,openGauss" />
- <sql-case id="insert_on_duplicate_key_update_with_placeholders"
value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) ON
DUPLICATE KEY UPDATE status = ?" db-types="MySQL,openGauss" />
+ <sql-case id="insert_on_duplicate_key_update" value="INSERT INTO t_order
(order_id, user_id, status) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE status =
VALUES(status)" db-types="MySQL,openGauss,Presto" />
+ <sql-case id="insert_on_duplicate_key_update_with_placeholders"
value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) ON
DUPLICATE KEY UPDATE status = ?" db-types="MySQL,openGauss,Presto" />
<sql-case id="insert_on_duplicate_key_update_with_placeholders_postgres"
value="INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) ON
CONFLICT (order_id) DO UPDATE SET status = ?" db-types="PostgreSQL" />
<sql-case id="insert_on_duplicate_key_update_with_complicated_expression"
value="INSERT INTO emp(order_id,emp_id,age,salary) VALUES (?,?,?,?) ON
DUPLICATE KEY UPDATE salary = VALUES(salary)+VALUES(salary)*0.2"
db-types="MySQL" />
<sql-case id="insert_with_multiple_values" value="INSERT INTO t_order
(order_id, user_id, status) VALUES (1, 1, 'insert'), (2, 2, 'insert2')"
db-types="MySQL" />
@@ -67,7 +67,7 @@
<sql-case id="insert_on_duplicate_key_update_with_base64_aes_encrypt"
value="INSERT INTO t_order SET order_id = ?, user_id = ?, status =
convert(to_base64(aes_encrypt(?, 'key')) USING utf8) ON DUPLICATE KEY UPDATE
status = VALUES(status)" db-types="MySQL" />
<sql-case id="insert_all_with_all_placeholders" value="INSERT ALL INTO
t_order (order_id, user_id, status) VALUES (?, ?, ?) INTO t_order (order_id,
user_id, status) VALUES (?, ?, ?) INTO t_order (order_id, user_id, status)
VALUES (?, ?, ?) SELECT * FROM dual" db-types="Oracle" />
<sql-case id="insert_with_str_date_add" value="INSERT INTO
t_order(present_date, order_id, user_id) VALUES (date_add(now(),interval ?
second), ?, ?)" db-types="MySQL" />
- <sql-case id="insert_select_with_all_columns" value="INSERT INTO t_order
(order_id, user_id, status) SELECT order_id, user_id, status FROM t_order WHERE
order_id = ?" db-types="MySQL,PostgreSQL,openGauss" />
+ <sql-case id="insert_select_with_all_columns" value="INSERT INTO t_order
(order_id, user_id, status) SELECT order_id, user_id, status FROM t_order WHERE
order_id = ?" db-types="MySQL,PostgreSQL,openGauss,Presto" />
<sql-case id="insert_select_without_columns" value="INSERT INTO t_order
SELECT order_id, user_id, status FROM t_order WHERE order_id = ?"
db-types="MySQL,PostgreSQL,openGauss" />
<sql-case id="insert_select_with_generate_key_column" value="INSERT INTO
t_order_item(item_id, order_id, user_id, status, creation_date) SELECT item_id,
order_id, user_id, 'insert', '2017-08-08' FROM t_order_item WHERE item_id = ?"
db-types="MySQL" />
<sql-case id="insert_select_without_generate_key_column" value="INSERT
INTO t_order_item(order_id, user_id, status, creation_date) SELECT order_id,
user_id, 'insert', '2017-08-08' FROM t_order_item WHERE order_id = ?"
db-types="MySQL" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/presto.xml
b/test/it/parser/src/main/resources/sql/supported/dml/presto.xml
index f77eeeb2dd9..efcb0972fde 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/presto.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/presto.xml
@@ -44,4 +44,6 @@
<sql-case id="presto_select_extract_function" value="SELECT EXTRACT(YEAR
FROM DATE '2020-01-01')" db-types="Presto" />
<sql-case id="presto_select_weight_string" value="SELECT
WEIGHT_STRING('a')" db-types="Presto" />
<sql-case id="presto_select_group_concat" value="SELECT
GROUP_CONCAT(order_id) FROM t_order" db-types="Presto" />
+ <sql-case id="presto_select_limit_only" value="SELECT * FROM t_order LIMIT
5" db-types="Presto" />
+ <sql-case id="presto_select_current_time_precision" value="SELECT
CURRENT_TIME(3)" db-types="Presto" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
index 1c6aef14c2f..f4121d5a314 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-expression.xml
@@ -38,7 +38,7 @@
<sql-case id="select_where_with_expr_with_and_sign" value="SELECT * FROM
t_order WHERE t_order.order_id = ? && ? = t_order.order_id"
db-types="MySQL" />
<sql-case id="select_where_with_expr_with_not" value="SELECT * FROM
t_order WHERE NOT (? = t_order.order_id)" db-types="MySQL" />
<sql-case id="select_where_with_expr_with_not_sign" value="SELECT * FROM
t_order WHERE ! ( ? = t_order.order_id)" db-types="MySQL" />
- <sql-case id="select_where_with_expr_with_is" value="SELECT * FROM t_order
WHERE ? = t_order.order_id IS FALSE" db-types="MySQL" />
+ <sql-case id="select_where_with_expr_with_is" value="SELECT * FROM t_order
WHERE ? = t_order.order_id IS FALSE" db-types="MySQL,Presto" />
<sql-case id="select_where_with_expr_with_is_not" value="SELECT * FROM
t_order WHERE ? = t_order.order_id IS NOT FALSE" db-types="MySQL" />
<sql-case id="select_where_with_boolean_primary_with_is" value="SELECT *
FROM t_order WHERE t_order.status IS NULL" db-types="MySQL,Presto" />
<sql-case id="select_where_with_boolean_primary_with_is_not" value="SELECT
* FROM t_order WHERE t_order.status IS NOT NULL" db-types="MySQL,Presto" />
@@ -102,7 +102,7 @@
<sql-case id="select_with_regular_function" value="SELECT A(1) FROM
t_order WHERE A(1) = 1 GROUP BY A(order_id) ORDER BY A(order_id)"
db-types="MySQL,Oracle,SQLServer" />
<sql-case id="select_with_regular_function_for_sql92" value="SELECT A(1)
FROM t_order WHERE A(1) = 1" db-types="MySQL,Oracle,SQLServer,H2,SQL92" />
<sql-case id="select_with_regular_function_utc_timestamp" value="SELECT
TIMEDIFF(NOW(), UTC_TIMESTAMP())" db-types="MySQL" />
- <sql-case id="select_with_collate_with_marker" value="SELECT * FROM
t_order WHERE order_id COLLATE ?" db-types="MySQL" />
+ <sql-case id="select_with_collate_with_marker" value="SELECT * FROM
t_order WHERE order_id COLLATE ?" db-types="MySQL,Presto" />
<sql-case id="select_age_for_postgres" value="SELECT * FROM
cypher('sharding_test_1', $$ CREATE (n) $$) as (a agtype)"
db-types="PostgreSQL" />
<sql-case id="select_datetime_expression" value="SELECT SYSTIMESTAMP AT
TIME ZONE 'UTC' FROM DUAL;" db-types="Oracle" />
<sql-case id="select_between_expression" value="SELECT item_id BETWEEN 1
AND order_id, status FROM t_order_item;" db-types="MySQL" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-lock.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-lock.xml
index e7e859b9dd1..a90c71bd22b 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-lock.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-lock.xml
@@ -18,7 +18,7 @@
<sql-cases>
<sql-case id="select_lock_with_lock_in" value="SELECT * FROM t_order WHERE
order_id = ? LOCK IN SHARE MODE" db-types="MySQL" />
- <sql-case id="select_lock_with_for_update" value="SELECT * FROM t_order
WHERE order_id = ? FOR UPDATE" db-types="MySQL,Oracle,PostgreSQL,openGauss" />
+ <sql-case id="select_lock_with_for_update" value="SELECT * FROM t_order
WHERE order_id = ? FOR UPDATE"
db-types="MySQL,Oracle,PostgreSQL,openGauss,Presto" />
<sql-case id="select_lock_with_for_share" value="SELECT * FROM t_order
WHERE order_id = ? FOR SHARE" db-types="MySQL" />
<sql-case id="select_lock_with_nowait" value="SELECT * FROM t_order WHERE
order_id = ? FOR UPDATE NOWAIT" db-types="MySQL" />
<sql-case id="select_lock_with_skip_locked" value="SELECT * FROM t_order
WHERE order_id = ? FOR UPDATE SKIP LOCKED" db-types="MySQL" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-pagination-group-by-order-by.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-pagination-group-by-order-by.xml
index 810b634ebcd..1646e8208eb 100644
---
a/test/it/parser/src/main/resources/sql/supported/dml/select-pagination-group-by-order-by.xml
+++
b/test/it/parser/src/main/resources/sql/supported/dml/select-pagination-group-by-order-by.xml
@@ -17,7 +17,7 @@
-->
<sql-cases>
- <sql-case id="select_pagination_with_group_by_and_order_by" value="SELECT
i.user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND
o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND
? GROUP BY i.item_id ORDER BY i.item_id DESC LIMIT ?, ?" db-types="MySQL,H2" />
+ <sql-case id="select_pagination_with_group_by_and_order_by" value="SELECT
i.user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND
o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND
? GROUP BY i.item_id ORDER BY i.item_id DESC LIMIT ?, ?"
db-types="MySQL,H2,Presto" />
<sql-case id="select_pagination_with_diff_group_by_and_order_by"
value="SELECT i.user_id FROM t_order o JOIN t_order_item i ON o.user_id =
i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id
BETWEEN ? AND ? GROUP BY i.user_id ORDER BY i.item_id DESC LIMIT ?, ?"
db-types="MySQL" />
<sql-case id="select_pagination_with_top_and_group_by_and_order_by"
value="SELECT * FROM (SELECT TOP ? row_number() OVER (ORDER BY i.item_id DESC)
AS rownum_, i.item_id, o.order_id as order_id, o.status as status, o.user_id as
user_id FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND
o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND
? GROUP BY i.item_id) AS row_ WHERE row_.rownum_ > ?" db-types="SQLServer" />
<sql-case
id="select_pagination_with_top_percent_with_ties_and_group_by_and_order_by"
value="SELECT * FROM (SELECT TOP ? PERCENT WITH TIES row_number() OVER (ORDER
BY i.item_id DESC) AS rownum_, i.item_id, o.order_id as order_id, o.status as
status, o.user_id as user_id FROM t_order o JOIN t_order_item i ON o.user_id =
i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id
BETWEEN ? AND ? GROUP BY i.item_id) AS row_ WHERE row_.rownum_ > ?"
db-types="SQLServer" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
index 280b4353354..e51645c5ad8 100644
---
a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
+++
b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
@@ -27,7 +27,7 @@
<sql-case id="select_convert_function" value="SELECT CONVERT('2020-10-01',
DATE)" db-types="MySQL" />
<sql-case id="select_position" value="SELECT POSITION('bar' IN
'foobarbar')" db-types="MySQL" />
<sql-case id="select_substring" value="SELECT SUBSTRING('foobarbar' from
-4 for 2)" db-types="MySQL" />
- <sql-case id="select_substr" value="SELECT SUBSTR('foobarbar' from 4)"
db-types="MySQL" />
+ <sql-case id="select_substr" value="SELECT SUBSTR('foobarbar' from 4)"
db-types="MySQL,Presto" />
<sql-case id="select_extract" value="SELECT EXTRACT(YEAR FROM
'2019-07-02')" db-types="MySQL" />
<sql-case id="select_extract_from_column" value="SELECT EXTRACT(YEAR FROM
o.creation_date) FROM t_order o" db-types="MySQL" />
<sql-case id="select_char" value="SELECT CHAR(77,121,83,81,'76')"
db-types="MySQL" />
@@ -39,7 +39,7 @@
<sql-case id="select_with_trim_expr_from_expr" value="SELECT
TRIM(remove_name FROM name) FROM t_order" db-types="MySQL" />
<sql-case id="select_with_trim_expr_from_expr_and_both" value="SELECT
TRIM(BOTH `remove_name` FROM `name`) FROM `t_order`" db-types="MySQL" />
<sql-case id="select_weight_string" value="SELECT WEIGHT_STRING('bar')"
db-types="MySQL" />
- <sql-case id="select_values" value="SELECT VALUES(order_id) FROM t_order"
db-types="MySQL" />
+ <sql-case id="select_values" value="SELECT VALUES(order_id) FROM t_order"
db-types="MySQL,Presto" />
<sql-case id="select_current_user_brackets" value="SELECT CURRENT_USER()"
db-types="MySQL" />
<sql-case id="select_extract_function" value="SELECT EXTRACT(YEAR FROM
TIMESTAMP '2001-02-16 20:38:40')" db-types="PostgreSQL,openGauss" />
<sql-case id="select_extract_function_week" value="SELECT EXTRACT(WEEK
FROM TIMESTAMP '2001-02-16 20:38:40')" db-types="PostgreSQL,openGauss" />
@@ -136,7 +136,7 @@
<sql-case id="select_window_with_cume_dist_function" value="SELECT val,
CUME_DIST() OVER() FROM numbers" db-types="MySQL" />
<sql-case id="select_curdate_function" value="SELECT CURDATE()"
db-types="MySQL" />
<sql-case id="select_current_date_function" value="SELECT CURRENT_DATE()"
db-types="MySQL" />
- <sql-case id="select_current_time_function" value="SELECT CURRENT_TIME()"
db-types="MySQL" />
+ <sql-case id="select_current_time_function" value="SELECT CURRENT_TIME()"
db-types="MySQL,Presto" />
<sql-case id="select_current_timestamp_function" value="SELECT
CURRENT_TIMESTAMP()" db-types="MySQL" />
<sql-case id="select_curtime_function" value="SELECT CURTIME()"
db-types="MySQL" />
<sql-case id="select_date_function" value="SELECT DATE('2003-12-31
01:02:03')" db-types="MySQL" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
index 19616bb2413..8a68efa414b 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
@@ -43,7 +43,7 @@
<sql-case id="select_with_operator_ilike" value="SELECT id from t_order
where name !~ '^pg_toast'" db-types="PostgreSQL,openGauss" />
<sql-case id="select_with_binary_operation_of_aggregation_expr"
value="SELECT (count(*)+1) as a" db-types="MySQL" />
<sql-case id="select_with_schema_func" value="SELECT schema(), database()"
db-types="MySQL" />
- <sql-case id="select_system_variables" value="SELECT
@@session.auto_increment_increment auto_increment_increment,
@@global.max_connections max_connections, @@autocommit" db-types="MySQL" />
+ <sql-case id="select_system_variables" value="SELECT
@@session.auto_increment_increment auto_increment_increment,
@@global.max_connections max_connections, @@autocommit" db-types="MySQL,Presto"
/>
<sql-case id="select_sqlmode_ansi_quotes" value='select "id" from
"t_order" where "t_order"."id"=10' db-types="MySQL" />
<sql-case id="select_with_function_name" value="SELECT current_timestamp"
db-types="MySQL" />
<sql-case id="select_with_same_table_name_and_alias" value="SELECT
t_order.* FROM t_order t_order WHERE user_id = ? AND order_id = ?" />
@@ -235,8 +235,8 @@
<sql-case id="select_with_corr_function" value="SELECT employee_id,
CORR(SYSDATE - hire_date, salary) FROM employees WHERE department_id in (50,
80) ORDER BY employee_id" db-types="Oracle" />
<sql-case id="select_with_trim_function_multi" value="select TRIM(' derby
'), TRIM(BOTH ' ' FROM ' derby '), TRIM(TRAILING ' ' FROM ' derby '),
TRIM(cast (null as char(1)) FROM ' derby '), TRIM(' ' FROM cast(null as
varchar(30))), TRIM('y' FROM ' derby') FROM employees" db-types="Oracle" />
<sql-case id="select_with_trim_function_simple" value="SELECT TRIM( '.,! '
FROM ' # test .') AS Result" db-types="SQLServer" />
- <sql-case id="select_with_trim_function_leading" value="SELECT
TRIM(LEADING '.,! ' FROM ' .# test .') AS Result"
db-types="SQLServer" />
- <sql-case id="select_with_trim_function_trailing" value="SELECT
TRIM(TRAILING '.,! ' FROM ' .# test .') AS Result"
db-types="SQLServer" />
+ <sql-case id="select_with_trim_function_leading" value="SELECT
TRIM(LEADING '.,! ' FROM ' .# test .') AS Result"
db-types="SQLServer,Presto" />
+ <sql-case id="select_with_trim_function_trailing" value="SELECT
TRIM(TRAILING '.,! ' FROM ' .# test .') AS Result"
db-types="SQLServer,Presto" />
<sql-case id="select_with_trim_function_both" value="SELECT TRIM(BOTH
'123' FROM '123abc123') AS Result" db-types="SQLServer" />
<sql-case id="select_with_ratio_to_report_function" value="SELECT
TO_CHAR(RATIO_TO_REPORT(amount_sold) OVER (), '9.999') AS RATIO_TO_REPORT FROM
sales s GROUP BY s.channel_desc" db-types="Oracle" />
<sql-case id="select_with_sys_xmlagg_and_xmlgen" value="SELECT
SYS_XMLAGG(last_name) a, SYS_XMLGEN(last_name) b FROM employees WHERE last_name
LIKE 'R%' ORDER BY xmlagg;" db-types="Oracle" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/update.xml
b/test/it/parser/src/main/resources/sql/supported/dml/update.xml
index 1b00e18003f..6d76f1ae655 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/update.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/update.xml
@@ -31,7 +31,7 @@
<sql-case id="update_with_column_equal_column" value="update t_order set
order_id = order_id, status = 'init' where order_id = order_id AND order_id =
?" db-types="MySQL" />
<sql-case id="update_with_case_when" value="update stock_freeze_detail set
row_status=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end,
update_user=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ?
end, update_time=case WHEN (id=?) THEN ? end where tenant_id = ?"
db-types="MySQL,Oracle" />
- <sql-case id="update_with_order_by_row_count" value="UPDATE t_order SET
status = ? WHERE order_id = ? AND user_id = ? ORDER BY order_id LIMIT ?"
db-types="MySQL" />
+ <sql-case id="update_with_order_by_row_count" value="UPDATE t_order SET
status = ? WHERE order_id = ? AND user_id = ? ORDER BY order_id LIMIT ?"
db-types="MySQL,Presto" />
<sql-case id="update_with_number" value="UPDATE t_order SET order_id = ?
WHERE user_id = ?" db-types="PostgreSQL,openGauss" />
<sql-case id="update_with_with_clause" value="WITH cte (order_id, user_id,
status) AS (SELECT order_id, user_id, status FROM t_order) UPDATE t_order SET
status = ? FROM t_order AS t JOIN cte AS c ON t.order_id = c.order_id WHERE
c.order_id = ?" db-types="SQLServer" />
<sql-case id="update_with_from_clause" value="UPDATE t_order SET status =
? FROM t_order AS t JOIN t_order_item AS i ON t.order_id = i.order_id WHERE
i.order_id = ?" db-types="PostgreSQL,openGauss,SQLServer" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/values.xml
b/test/it/parser/src/main/resources/sql/supported/dml/values.xml
index eb6d76e1164..4ffecf2961c 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/values.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/values.xml
@@ -18,7 +18,7 @@
<sql-cases>
<sql-case id="values_with_regexp_replace" value="SELECT REGEXP_REPLACE(e,
'pattern', 'xyz')FROM (VALUES ROW('Find pattern'), ROW(NULL), ROW('Find
pattern')) AS v(e)" db-types="MySQL" />
- <sql-case id="values_with_row" value="values ROW(1,2)" db-types="MySQL" />
+ <sql-case id="values_with_row" value="values ROW(1,2)"
db-types="MySQL,Presto" />
<sql-case id="values_with_order_limit" value="VALUES ROW(1,-2,3),
ROW(5,7,9), ROW(4,6,8) ORDER BY column_1 desc , column_0 desc limit 10"
db-types="MySQL" />
<sql-case id="values_with_select" value="values row((select 1), 2)"
db-types="MySQL" />
</sql-cases>
