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 1c0908cb4de Add SQL parser test cases for Hive (#37705)
1c0908cb4de is described below
commit 1c0908cb4de0fca4b06e89466e3c30762dfb4329
Author: Liang Zhang <[email protected]>
AuthorDate: Sat Jan 10 21:41:03 2026 +0800
Add SQL parser test cases for Hive (#37705)
* Add SQL parser test cases for Hive
* Add SQL parser test cases for Hive
---
.../src/main/resources/case/dml/select-hive.xml | 50 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/insert.xml | 8 ++--
.../main/resources/sql/supported/dml/presto.xml | 2 +-
.../sql/supported/dml/select-expression.xml | 20 ++++-----
.../resources/sql/supported/dml/select-hive.xml | 4 ++
.../sql/supported/dml/select-pagination.xml | 2 +-
.../sql/supported/dml/select-special-function.xml | 12 +++---
.../resources/sql/supported/dml/select-window.xml | 4 +-
.../main/resources/sql/supported/dml/select.xml | 10 ++---
9 files changed, 83 insertions(+), 29 deletions(-)
diff --git a/test/it/parser/src/main/resources/case/dml/select-hive.xml
b/test/it/parser/src/main/resources/case/dml/select-hive.xml
index 7f862b9053b..b080d6c6b4a 100644
--- a/test/it/parser/src/main/resources/case/dml/select-hive.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-hive.xml
@@ -809,4 +809,54 @@
</from>
</select>
+ <select sql-case-id="hive_group_concat_order">
+ <from>
+ <simple-table name="t_order" start-index="49" stop-index="55" />
+ </from>
+ <projections start-index="7" stop-index="42">
+ <expression-projection text="GROUP_CONCAT(status ORDER BY status)"
start-index="7" stop-index="42">
+ <expr>
+ <function function-name="GROUP_CONCAT" start-index="7"
stop-index="42" text="GROUP_CONCAT(status ORDER BY status)">
+ <parameter>
+ <column name="status" start-index="20"
stop-index="25" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="hive_json_array_simple">
+ <projections start-index="7" stop-index="49">
+ <expression-projection text="JSON_ARRAY(1, 'abc', NULL, TRUE,
CURTIME())" start-index="7" stop-index="49" >
+ <expr>
+ <function function-name="JSON_ARRAY" text="JSON_ARRAY(1,
'abc', NULL, TRUE, CURTIME())" start-index="7" stop-index="49" />
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="hive_convert_date">
+ <projections start-index="7" stop-index="33">
+ <expression-projection text="CONVERT('2020-10-01', DATE)"
start-index="7" stop-index="33">
+ <expr>
+ <function function-name="CONVERT"
text="CONVERT('2020-10-01', DATE)" start-index="7" stop-index="33" />
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="hive_limit_param" parameters="1,2">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <limit start-index="22" stop-index="37">
+ <row-count value="1" parameter-index="0" start-index="28"
stop-index="28" />
+ <offset value="2" parameter-index="1" start-index="37"
stop-index="37" />
+ </limit>
+ </select>
+
</sql-parser-test-cases>
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 7ed81a3cd4e..c0bfa1db011 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
@@ -32,12 +32,12 @@
<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,Presto,Doris" />
+ <sql-case id="insert_set_with_all_placeholders" value="INSERT INTO t_order
SET order_id = ?, user_id = ?, status = ?" db-types="MySQL,Presto,Doris,Hive" />
<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" />
<sql-case id="insert_with_partial_placeholders" value="INSERT INTO t_order
(order_id, user_id, status) VALUES (?, ?, 'insert')" />
- <sql-case id="insert_set_with_partial_placeholders" value="INSERT INTO
t_order SET order_id = ?, user_id = ?, status = 'insert'" db-types="MySQL" />
+ <sql-case id="insert_set_with_partial_placeholders" value="INSERT INTO
t_order SET order_id = ?, user_id = ?, status = 'insert'" db-types="MySQL,Hive"
/>
<sql-case id="insert_with_generate_key_column" value="INSERT INTO
t_order_item(item_id, order_id, user_id, status, creation_date) VALUES (?, ?,
?, 'insert', '2017-08-08')" />
<sql-case id="insert_set_with_generate_key_column" value="INSERT INTO
t_order_item SET item_id = ?, order_id = ?, user_id = ?, status = 'insert',
creation_date='2017-08-08'" db-types="MySQL,Doris" />
<sql-case id="insert_without_generate_key_column" value="INSERT INTO
t_order_item(order_id, user_id, status, creation_date) VALUES (?, ?, 'insert',
'2017-08-08')" />
@@ -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,Doris" />
<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,Doris" />
- <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,Doris" />
- <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,Doris" />
+ <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,Doris,Hive" />
+ <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,Doris,Hive" />
<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,Doris" />
<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" />
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 d2962179ee6..7f17ce2d28b 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
@@ -88,7 +88,7 @@
<sql-case id="presto_select_udf_with_param" value="SELECT my_udf(order_id)
FROM t_order" db-types="Presto" />
<sql-case id="presto_insert_select_no_columns" value="INSERT INTO t_order
SELECT 1" db-types="Presto" />
<sql-case id="presto_insert_values_empty_columns" value="INSERT INTO
t_order () VALUES (1)" db-types="Presto" />
- <sql-case id="presto_select_cast_lengths" value="SELECT CAST(order_id AS
DECIMAL(10,2)) FROM t_order" db-types="Presto" />
+ <sql-case id="presto_select_cast_lengths" value="SELECT CAST(order_id AS
DECIMAL(10,2)) FROM t_order" db-types="Presto,Hive" />
<sql-case id="presto_select_window_partition_only" value="SELECT
ROW_NUMBER() OVER win FROM t_order WINDOW win AS (PARTITION BY status)"
db-types="Presto" />
<sql-case id="presto_delete_order_limit" value="DELETE FROM t_order WHERE
flag = 1 ORDER BY order_id LIMIT 1" db-types="Presto" />
<sql-case id="presto_select_cast_literal" value="SELECT CAST('1' AS CHAR)
FROM t_order" db-types="Presto" />
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 b103c74394d..b4a9124561d 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
@@ -36,22 +36,22 @@
<sql-case id="select_where_with_expr_with_and" value="SELECT * FROM
t_order WHERE t_order.order_id = ? AND ? = t_order.order_id" db-types="MySQL" />
<sql-case id="select_where_with_expr_with_and_or" value="SELECT * FROM
t_order WHERE t_order.order_id = ? AND ? = t_order.order_id OR t_order.status =
'failed' AND ? = t_order.order_id" />
<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_not" value="SELECT * FROM
t_order WHERE NOT (? = t_order.order_id)" db-types="MySQL,Hive" />
+ <sql-case id="select_where_with_expr_with_not_sign" value="SELECT * FROM
t_order WHERE ! ( ? = t_order.order_id)" db-types="MySQL,Hive" />
<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" />
+ <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,Hive" />
+ <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,Hive" />
<sql-case id="select_where_with_boolean_primary_with_null_safe"
value="SELECT * FROM t_order WHERE t_order.status <=> t_order.order_id"
db-types="MySQL,Presto" />
<sql-case id="select_where_with_boolean_primary_with_comparison_predicate"
value="SELECT * FROM t_order WHERE t_order.status >= t_order.order_id"
db-types="MySQL" />
<sql-case id="select_where_with_boolean_primary_with_comparison_subquery"
value="SELECT * FROM t_order WHERE t_order.status > ALL (SELECt status FROM
t_order_item WHERE status > ?)" db-types="MySQL" />
<sql-case id="select_where_with_predicate_with_in_subquery" value="SELECT
* FROM t_order WHERE t_order.order_id NOT IN (SELECT order_id FROM t_order_item
WHERE status > ?)" db-types="MySQL,PostgreSQL,openGauss,Presto" />
<sql-case id="select_where_with_predicate_with_in_expr" value="SELECT *
FROM t_order WHERE t_order.order_id IN (?, ?, ?)"
db-types="MySQL,PostgreSQL,openGauss,Presto" />
- <sql-case id="select_where_with_predicate_with_between" value="SELECT *
FROM t_order WHERE t_order.order_id BETWEEN ? AND ?" db-types="MySQL,Presto" />
+ <sql-case id="select_where_with_predicate_with_between" value="SELECT *
FROM t_order WHERE t_order.order_id BETWEEN ? AND ?"
db-types="MySQL,Presto,Hive" />
<sql-case id="select_where_with_predicate_with_sounds_like" value="SELECT
* FROM t_order WHERE t_order.order_id SOUNDS LIKE '1%'"
db-types="MySQL,Presto,Doris" />
- <sql-case id="select_where_with_predicate_with_like" value="SELECT * FROM
t_order WHERE t_order.order_id NOT LIKE '1%' ESCAPE '$'"
db-types="MySQL,Presto" />
- <sql-case id="select_where_with_predicate_with_not_like" value="SELECT *
FROM t_order WHERE t_order.status NOT LIKE '1%'"
db-types="MySQL,PostgreSQL,openGauss" />
- <sql-case id="select_where_with_predicate_with_regexp" value="SELECT *
FROM t_order WHERE t_order.order_id NOT REGEXP '[123]'" db-types="MySQL,Presto"
/>
+ <sql-case id="select_where_with_predicate_with_like" value="SELECT * FROM
t_order WHERE t_order.order_id NOT LIKE '1%' ESCAPE '$'"
db-types="MySQL,Presto,Hive" />
+ <sql-case id="select_where_with_predicate_with_not_like" value="SELECT *
FROM t_order WHERE t_order.status NOT LIKE '1%'"
db-types="MySQL,PostgreSQL,openGauss,Hive" />
+ <sql-case id="select_where_with_predicate_with_regexp" value="SELECT *
FROM t_order WHERE t_order.order_id NOT REGEXP '[123]'"
db-types="MySQL,Presto,Hive" />
<sql-case id="select_where_with_predicate_with_rlike" value="SELECT * FROM
t_order WHERE t_order.order_id NOT RLIKE '[123]'"
db-types="MySQL,Presto,Doris,Hive" />
<sql-case id="select_where_with_bit_expr_with_vertical_bar" value="SELECT
* FROM t_order WHERE t_order.order_id | ?" db-types="MySQL" />
<sql-case id="select_where_with_bit_expr_with_ampersand" value="SELECT *
FROM t_order WHERE t_order.order_id & ?" db-types="MySQL" />
@@ -102,12 +102,12 @@
<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,Presto,Doris" />
+ <sql-case id="select_with_collate_with_marker" value="SELECT * FROM
t_order WHERE order_id COLLATE ?" db-types="MySQL,Presto,Doris,Hive" />
<sql-case id="select_safe_equal" value="SELECT 1 <=> 1"
db-types="Doris" />
<sql-case id="select_where_is_true_doris" value="SELECT * FROM t_order
WHERE flag IS TRUE" db-types="Doris" />
<sql-case id="select_where_is_false_doris" value="SELECT * FROM t_order
WHERE flag IS FALSE" db-types="Doris" />
- <sql-case id="select_string_concat_with_double_bar" value="SELECT 'a' ||
'b'" db-types="Doris" />
+ <sql-case id="select_string_concat_with_double_bar" value="SELECT 'a' ||
'b'" db-types="Doris,Hive" />
<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-hive.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-hive.xml
index 69a1e9680db..cff26fe3947 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-hive.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-hive.xml
@@ -57,4 +57,8 @@
<sql-case id="hive_select_current_time_precision" value="SELECT
CURRENT_TIME(3)" db-types="Hive" />
<sql-case id="hive_select_natural_join" value="SELECT * FROM t1 NATURAL
JOIN t2" db-types="Hive" />
<sql-case id="hive_select_match" value="SELECT * FROM t_doc WHERE
MATCH(title) AGAINST('hive')" db-types="Hive" />
+ <sql-case id="hive_group_concat_order" value="SELECT GROUP_CONCAT(status
ORDER BY status) FROM t_order" db-types="Hive" />
+ <sql-case id="hive_json_array_simple" value="SELECT JSON_ARRAY(1, 'abc',
NULL, TRUE, CURTIME())" db-types="Hive" />
+ <sql-case id="hive_convert_date" value="SELECT CONVERT('2020-10-01',
DATE)" db-types="Hive" />
+ <sql-case id="hive_limit_param" value="SELECT * FROM t_order LIMIT ?
OFFSET ?" db-types="Hive" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-pagination.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-pagination.xml
index 0c8379be29d..c2aa71171f4 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-pagination.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-pagination.xml
@@ -23,7 +23,7 @@
<sql-case id="select_pagination_with_top_percent_with_ties" 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 ?) AS row_" db-types="SQLServer" />
<sql-case id="select_pagination_with_row_number" value="SELECT * FROM
(SELECT row_.*, rownum rownum_ FROM (SELECT order0_.order_id AS order_id,
order0_.status AS status, order0_.user_id AS user_id FROM t_order order0_ JOIN
t_order_item i ON order0_.user_id = i.user_id AND order0_.order_id = i.order_id
WHERE order0_.user_id IN (?, ?) AND order0_.order_id BETWEEN ? AND ? ORDER BY
i.item_id DESC) row_ WHERE rownum <= ?)" db-types="Oracle" />
<sql-case id="select_pagination_with_limit_with_back_quotes" value="SELECT
i.* 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 ? ORDER BY i.item_id DESC LIMIT ?, ?" db-types="MySQL" />
- <sql-case id="select_pagination_with_limit_and_offset_keyword"
value="SELECT i.* 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 ? ORDER BY i.item_id DESC LIMIT ? OFFSET ?"
db-types="MySQL" />
+ <sql-case id="select_pagination_with_limit_and_offset_keyword"
value="SELECT i.* 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 ? ORDER BY i.item_id DESC LIMIT ? OFFSET ?"
db-types="MySQL,Hive" />
<sql-case id="select_pagination_with_offset_and_limit" value="SELECT i.*
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 ? ORDER BY
i.item_id DESC OFFSET ? LIMIT ?" db-types="PostgreSQL,openGauss" />
<sql-case id="select_pagination_with_offset_and_limit_all" value="SELECT
i.* 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 ? ORDER BY
i.item_id DESC OFFSET ? LIMIT ALL" db-types="PostgreSQL,openGauss" />
<sql-case id="select_pagination_with_top_for_greater_than" 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 ?) 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 14b428dc08f..a4649cd5a13 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
@@ -38,9 +38,9 @@
<sql-case id="select_with_trim_expr_and_both" value="SELECT TRIM(BOTH '#'
FROM `name`) FROM `t_order`" db-types="MySQL" />
<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,Doris" />
- <sql-case id="select_values" value="SELECT VALUES(order_id) FROM t_order"
db-types="MySQL,Presto,Doris" />
- <sql-case id="select_current_user_brackets" value="SELECT CURRENT_USER()"
db-types="MySQL,Doris" />
+ <sql-case id="select_weight_string" value="SELECT WEIGHT_STRING('bar')"
db-types="MySQL,Doris,Hive" />
+ <sql-case id="select_values" value="SELECT VALUES(order_id) FROM t_order"
db-types="MySQL,Presto,Doris,Hive" />
+ <sql-case id="select_current_user_brackets" value="SELECT CURRENT_USER()"
db-types="MySQL,Doris,Hive" />
<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" />
<sql-case id="select_extract_function_quarter" value="SELECT
EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40')"
db-types="PostgreSQL,openGauss" />
@@ -105,7 +105,7 @@
<sql-case id="select_with_xor_function" value="SELECT 1 XOR NULL;"
db-types="MySQL,Doris"/>
<sql-case id="select_with_year_function" value="SELECT
YEAR('1999-09-09');" db-types="MySQL"/>
<sql-case id="select_with_yearweek_function" value="SELECT
YEARWEEK('1988-08-08');" db-types="MySQL"/>
- <sql-case id="select_with_weight_string_function" value="SELECT
HEX(WEIGHT_STRING('MySQL'));" db-types="MySQL,Doris"/>
+ <sql-case id="select_with_weight_string_function" value="SELECT
HEX(WEIGHT_STRING('MySQL'));" db-types="MySQL,Doris,Hive"/>
<sql-case id="select_row_number" value="SELECT ROW_NUMBER() OVER w FROM
t_order" db-types="MySQL" />
<sql-case id="select_rpad" value="SELECT RPAD('Hello', 10, '*')"
db-types="MySQL" />
<sql-case id="select_rtrim" value="SELECT RTRIM(' Hello ')"
db-types="MySQL" />
@@ -213,7 +213,7 @@
<sql-case id="select_round" value="SELECT ROUND(1.58)" db-types="MySQL" />
<sql-case id="select_row_count" value="SELECT ROW_COUNT()"
db-types="MySQL" />
<sql-case id="select_md5" value="SELECT MD5('testing')"
db-types="MySQL,Doris" />
- <sql-case id="select_member_of" value="SELECT 'ab' MEMBER OF('[23,
"abc", 17, "ab", 10]')" db-types="MySQL,Doris" />
+ <sql-case id="select_member_of" value="SELECT 'ab' MEMBER OF('[23,
"abc", 17, "ab", 10]')" db-types="MySQL,Doris,Hive" />
<sql-case id="select_microsecond" value="SELECT
MICROSECOND('12:00:00.123456')" db-types="MySQL" />
<sql-case id="select_mid" value="SELECT MID('foobarbar' from 4)"
db-types="MySQL" />
<sql-case id="select_minute" value="SELECT MINUTE('2008-02-03 10:05:03')"
db-types="MySQL,Doris" />
@@ -295,7 +295,7 @@
<sql-case id="select_prediction_cost" value="SELECT cust_id FROM
mining_data_apply_v WHERE country_name = 'Italy' ORDER BY
PREDICTION_COST(DT_SH_Clas_sample, 1 COST MODEL USING *)" db-types="Oracle" />
<sql-case id="select_name_const" value="SELECT NAME_CONST('myname',14)"
db-types="MySQL" />
<sql-case id="select_not_in" value="SELECT (3,4) NOT IN ((1,2), (3,4))"
db-types="MySQL" />
- <sql-case id="select_not_like" value="SELECT 'hello world' NOT LIKE
'%test%'" db-types="MySQL" />
+ <sql-case id="select_not_like" value="SELECT 'hello world' NOT LIKE
'%test%'" db-types="MySQL,Hive" />
<sql-case id="select_now" value="SELECT NOW()" db-types="MySQL" />
<sql-case id="select_nth_value" value="SELECT NTH_VALUE(salary, 3) OVER
(ORDER BY salary) AS third_highest_salary FROM employees"
db-types="MySQL,Doris" />
<sql-case id="select_nullif" value="SELECT NULLIF(1,1)" db-types="MySQL" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-window.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-window.xml
index ce264f5690c..b84769827f5 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-window.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-window.xml
@@ -17,8 +17,8 @@
-->
<sql-cases>
- <sql-case id="select_window" value="SELECT user_id, ROW_NUMBER() OVER w AS
'row_number', RANK() OVER w AS 'rank', DENSE_RANK() OVER w AS 'dense_rank' FROM
t_order WHERE order_id = ? WINDOW w AS (ORDER BY user_id)"
db-types="MySQL,Doris" />
- <sql-case id="select_window_partition_order_doris" value="SELECT
ROW_NUMBER() OVER (PARTITION BY k ORDER BY v) FROM t_order" db-types="Doris" />
+ <sql-case id="select_window" value="SELECT user_id, ROW_NUMBER() OVER w AS
'row_number', RANK() OVER w AS 'rank', DENSE_RANK() OVER w AS 'dense_rank' FROM
t_order WHERE order_id = ? WINDOW w AS (ORDER BY user_id)"
db-types="MySQL,Doris,Hive" />
+ <sql-case id="select_window_partition_order_doris" value="SELECT
ROW_NUMBER() OVER (PARTITION BY k ORDER BY v) FROM t_order"
db-types="Doris,Hive" />
<sql-case id="select_window_lead_lag_doris" value="SELECT LEAD(v) OVER
(PARTITION BY k ORDER BY v) FROM t_order" db-types="Doris" />
<sql-case id="select_window_first_value_doris" value="SELECT
FIRST_VALUE(v) OVER (PARTITION BY k ORDER BY v) FROM t_order" db-types="Doris"
/>
<sql-case id="select_window_last_value_doris" value="SELECT LAST_VALUE(v)
OVER (PARTITION BY k ORDER BY v ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW) FROM t_order" db-types="Doris" />
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 8fd2f7cc09f..de588d7901a 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
@@ -44,7 +44,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,Presto"
/>
+ <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,Hive" />
<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 = ?" />
@@ -124,8 +124,8 @@
<sql-case id="select_with_jsonb_path_delete" value="SELECT
content_json::jsonb#-'{title}' FROM tb_content_json"
db-types="PostgreSQL,openGauss" />
<sql-case id="select_with_jsonb_path_contain_any_value" value="SELECT
content_json::jsonb @?'$.keyword[*]?(@=="ss")' FROM tb_content_json"
db-types="PostgreSQL,openGauss" />
<sql-case id="select_with_jsonb_path_predicate_check" value="SELECT
content_json::jsonb@@'$.keyword[*]=="ss"' FROM tb_content_json"
db-types="PostgreSQL,openGauss" />
- <sql-case id="select_with_assignment_operator" value="SELECT @rn := 1,
@now_code := '' FROM t_order" db-types="MySQL,Doris" />
- <sql-case id="select_with_assignment_operator_and_keyword" value="SELECT
@KEY := '', @num := 123 FROM t_order" db-types="MySQL,Doris" />
+ <sql-case id="select_with_assignment_operator" value="SELECT @rn := 1,
@now_code := '' FROM t_order" db-types="MySQL,Doris,Hive" />
+ <sql-case id="select_with_assignment_operator_and_keyword" value="SELECT
@KEY := '', @num := 123 FROM t_order" db-types="MySQL,Doris,Hive" />
<sql-case id="select_from_dual" value="SELECT 1 FROM DUAL"
db-types="MySQL,Hive" />
<sql-case id="select_with_cast_as_signed" value="SELECT
user_id,CAST(order_id AS SIGNED) FROM t_order" db-types="MySQL" />
<sql-case id="select_with_cast_as_unsigned" value="SELECT CAST(order_id AS
UNSIGNED),user_id FROM t_order" db-types="MySQL" />
@@ -245,9 +245,9 @@
<sql-case id="select_hex_literal_doris" value="SELECT 0x1AF"
db-types="Doris" />
<sql-case id="select_bit_literal_doris" value="SELECT b'1010'"
db-types="Doris" />
<sql-case id="select_collate_clause_doris" value="SELECT 'a' COLLATE
utf8mb4_bin" db-types="Doris" />
- <sql-case id="select_true_literal_doris" value="SELECT TRUE"
db-types="Doris" />
+ <sql-case id="select_true_literal_doris" value="SELECT TRUE"
db-types="Doris,Hive" />
<sql-case id="select_false_literal_doris" value="SELECT FALSE"
db-types="Doris" />
- <sql-case id="select_null_literal_doris" value="SELECT NULL"
db-types="Doris" />
+ <sql-case id="select_null_literal_doris" value="SELECT NULL"
db-types="Doris,Hive" />
<sql-case id="select_with_percentile_functions" value="SELECT
department_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) 'Median
cont', PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) 'Median disc'
FROM employees GROUP BY department_id" db-types="Oracle" />
<sql-case id="select_with_keep_clause" value="SELECT salary,MIN(salary)
KEEP (DENSE_RANK FIRST ORDER BY commission_pct) OVER (PARTITION BY
department_id) 'Worst', MAX(salary) KEEP (DENSE_RANK LAST ORDER BY
commission_pct) OVER (PARTITION BY department_id) 'Best' FROM employees ORDER
BY department_id" db-types="Oracle" />
<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" />
