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 ffce86be885 Add more test cases on Doris SQL parser (#37662)
ffce86be885 is described below
commit ffce86be885a64de2b41a27433656908a30c1283
Author: Liang Zhang <[email protected]>
AuthorDate: Tue Jan 6 21:36:42 2026 +0800
Add more test cases on Doris SQL parser (#37662)
---
.../src/main/resources/case/dml/select-into.xml | 21 +++++++++++
.../resources/case/dml/select-special-function.xml | 43 ++++++++++++++++++++++
.../parser/src/main/resources/case/dml/select.xml | 14 +++++++
.../src/main/resources/sql/supported/dal/set.xml | 4 +-
.../src/main/resources/sql/supported/dal/show.xml | 4 +-
.../resources/sql/supported/ddl/alter-table.xml | 12 +++---
.../src/main/resources/sql/supported/dml/do.xml | 2 +-
.../main/resources/sql/supported/dml/presto.xml | 4 +-
.../sql/supported/dml/select-expression.xml | 2 +-
.../sql/supported/dml/select-group-by.xml | 6 +--
.../resources/sql/supported/dml/select-into.xml | 2 +
.../sql/supported/dml/select-order-by.xml | 2 +-
.../sql/supported/dml/select-special-function.xml | 3 +-
.../resources/sql/supported/dml/select-with.xml | 2 +-
.../main/resources/sql/supported/dml/select.xml | 1 +
15 files changed, 102 insertions(+), 20 deletions(-)
diff --git a/test/it/parser/src/main/resources/case/dml/select-into.xml
b/test/it/parser/src/main/resources/case/dml/select-into.xml
index 7569c48f039..09e6fe63091 100644
--- a/test/it/parser/src/main/resources/case/dml/select-into.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-into.xml
@@ -424,4 +424,25 @@
<property key="s3.secret_key" value="yyy" />
</outfile>
</select>
+
+ <select sql-case-id="select_into_outfile_doris_with_fields">
+ <from start-index="14" stop-index="20">
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <projections distinct-row="false" start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <outfile start-index="22" stop-index="110"
file-path="hdfs://path/to/result_" />
+ </select>
+
+ <select sql-case-id="select_into_outfile_doris_with_fields_and_escaped">
+ <from start-index="28" stop-index="34">
+ <simple-table name="t_order" start-index="28" stop-index="34" />
+ </from>
+ <projections distinct-row="false" start-index="7" stop-index="21">
+ <column-projection name="user_id" start-index="7" stop-index="13"
/>
+ <column-projection name="status" start-index="16" stop-index="21"
/>
+ </projections>
+ <outfile start-index="36" stop-index="140"
file-path="hdfs://path/to/result_" />
+ </select>
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/case/dml/select-special-function.xml
b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
index 4efb09019b6..3e72c3ea55b 100644
--- a/test/it/parser/src/main/resources/case/dml/select-special-function.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
@@ -3953,6 +3953,49 @@
</projections>
</select>
+ <select sql-case-id="select_json_extract_cast_abs_doris">
+ <from>
+ <simple-table name="t_order" start-index="82" stop-index="88" />
+ </from>
+ <projections start-index="7" stop-index="75">
+ <expression-projection text="JSON_EXTRACT(order_id, '$.a')"
start-index="7" stop-index="35">
+ <expr>
+ <function function-name="JSON_EXTRACT"
text="JSON_EXTRACT(order_id, '$.a')" start-index="7" stop-index="35">
+ <parameter>
+ <column name="order_id" start-index="20"
stop-index="27" />
+ </parameter>
+ <parameter>
+ <literal-expression value="$.a" start-index="30"
stop-index="34" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection text="CAST(order_id AS DATETIME(3))"
start-index="38" stop-index="66">
+ <expr>
+ <function function-name="CAST" text="CAST(order_id AS
DATETIME(3))" start-index="38" stop-index="66">
+ <parameter>
+ <column name="order_id" start-index="43"
stop-index="50" />
+ </parameter>
+ <parameter>
+ <data-type value="DATETIME" start-index="55"
stop-index="65">
+ <data-type-length precision="3"
start-index="63" stop-index="65" />
+ </data-type>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ <expression-projection text="ABS(-1)" start-index="69"
stop-index="75">
+ <expr>
+ <function function-name="ABS" text="ABS(-1)"
start-index="69" stop-index="75">
+ <parameter>
+ <literal-expression value="-1" start-index="73"
stop-index="74" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
<select sql-case-id="select_json_remove">
<projections start-index="7" stop-index="44">
<expression-projection start-index="7" stop-index="44"
text="JSON_REMOVE('["a", "b", "d"]', '$[0]')">
diff --git a/test/it/parser/src/main/resources/case/dml/select.xml
b/test/it/parser/src/main/resources/case/dml/select.xml
index 752a7c932c5..d6ca3578e50 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -143,6 +143,20 @@
</expression-projection>
</projections>
</select>
+
+ <select sql-case-id="select_collate_clause_doris">
+ <projections start-index="7" stop-index="29">
+ <expression-projection text="'a' COLLATE utf8mb4_bin"
start-index="7" stop-index="29">
+ <expr>
+ <collate-expression start-index="7" stop-index="29">
+ <collate-name>
+ <literal-expression start-index="11"
stop-index="29" value="utf8mb4_bin" />
+ </collate-name>
+ </collate-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
<select sql-case-id="select_with_default_str">
<projections start-index="7" stop-index="7">
diff --git a/test/it/parser/src/main/resources/sql/supported/dal/set.xml
b/test/it/parser/src/main/resources/sql/supported/dal/set.xml
index 0f92b710d61..add44b5fbc0 100644
--- a/test/it/parser/src/main/resources/sql/supported/dal/set.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dal/set.xml
@@ -18,7 +18,7 @@
<sql-cases>
<sql-case id="set_user_variable" value="SET @drop = IF(@have_ndb = 0,
@drop_cmd, 'SET @dummy = 0')" db-types="MySQL" />
- <sql-case id="set_parameter_equal" value="SET configuration_parameter =
'value'" db-types="PostgreSQL,openGauss, MySQL" />
+ <sql-case id="set_parameter_equal" value="SET configuration_parameter =
'value'" db-types="PostgreSQL,openGauss, MySQL,Doris" />
<sql-case id="set_parameter_equal_number" value="SET extra_float_digits =
3" db-types="PostgreSQL,openGauss, MySQL" />
<sql-case id="set_parameter_equal_session_mysql" value="SET SESSION
configuration_parameter = 'value'" db-types="MySQL" />
<sql-case id="set_parameter_equal_at_at_session_mysql" value="SET
@@SESSION.configuration_parameter = 'value'" db-types="MySQL" />
@@ -39,7 +39,7 @@
<sql-case id="set_parameter_equal_boolean" value="SET extra_float_digits =
true" db-types="PostgreSQL,openGauss" />
<sql-case id="set_parameter_equal_list" value="SET extra_float_digits =
1,2,3" db-types="PostgreSQL,openGauss" />
<sql-case id="set_parameter_equal_number_with_signal" value="SET
extra_float_digits = -10.5" db-types="PostgreSQL,openGauss" />
- <sql-case id="set_names" value="SET NAMES 'utf8' COLLATE
'utf8_general_ci'" db-types="MySQL" />
+ <sql-case id="set_names" value="SET NAMES 'utf8' COLLATE
'utf8_general_ci'" db-types="MySQL,Doris" />
<sql-case id="set_resource_group" value="SET RESOURCE GROUP rg"
db-types="MySQL" />
<sql-case id="set_charset_mysql" value="SET NAMES 'utf8'" db-types="MySQL"
/>
<sql-case id="set_charset_postgresql" value="SET NAMES 'UTF8'"
db-types="PostgreSQL" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dal/show.xml
b/test/it/parser/src/main/resources/sql/supported/dal/show.xml
index 7e3e0366596..b170e72f5f4 100644
--- a/test/it/parser/src/main/resources/sql/supported/dal/show.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dal/show.xml
@@ -98,7 +98,7 @@
<sql-case id="show_extended_full_tables" value="SHOW EXTENDED FULL TABLES"
db-types="MySQL" />
<sql-case id="show_tables_from_schema" value="SHOW TABLES FROM
`sharding_db`" db-types="MySQL" />
<sql-case id="show_tables_in_schema" value="SHOW TABLES IN `sharding_db`"
db-types="MySQL" />
- <sql-case id="show_tables_with_like_pattern" value="SHOW TABLES FROM
`sharding_db` LIKE 't_order_%'" db-types="MySQL" />
+ <sql-case id="show_tables_with_like_pattern" value="SHOW TABLES FROM
`sharding_db` LIKE 't_order_%'" db-types="MySQL,Doris" />
<sql-case id="show_tables_with_where_expr" value="SHOW EXTENDED FULL
TABLES FROM `sharding_db` WHERE `table_type` = ?" db-types="MySQL" />
<sql-case id="show_tables_with_where_expr_no_parameter" value="SHOW
EXTENDED FULL TABLES FROM `sharding_db` WHERE `table_type` = 'BASE TABLE'"
db-types="MySQL" />
<sql-case id="show_character_set" value="SHOW CHARACTER SET"
db-types="MySQL" />
@@ -114,7 +114,7 @@
<sql-case id="show_session_variables" value="SHOW SESSION VARIABLES"
db-types="MySQL" />
<sql-case id="show_variables_with_like_pattern" value="SHOW SESSION
VARIABLES LIKE '%size%'" db-types="MySQL" />
<sql-case id="show_variables_with_where_expr" value="SHOW GLOBAL VARIABLES
WHERE variable_name = ?" db-types="MySQL" />
- <sql-case id="show_variables_with_where_expr_no_parameter" value="SHOW
VARIABLES WHERE variable_name = 'max_join_size'" db-types="MySQL" />
+ <sql-case id="show_variables_with_where_expr_no_parameter" value="SHOW
VARIABLES WHERE variable_name = 'max_join_size'" db-types="MySQL,Doris" />
<sql-case id="show_binlog_events_with_log_name" value="SHOW BINLOG EVENTS
IN 'log_name'" db-types="MySQL" />
<sql-case id="show_binlog_events_with_from_pos" value="SHOW BINLOG EVENTS
FROM 1" db-types="MySQL" />
<sql-case id="show_binlog_events_with_limit" value="SHOW BINLOG EVENTS
LIMIT 1,2" db-types="MySQL" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/alter-table.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/alter-table.xml
index c587890a77d..dd7e851c9b6 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/alter-table.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/alter-table.xml
@@ -65,8 +65,8 @@
<sql-case id="alter_table_with_space" value=" ALTER TABLE
t_order" db-types="MySQL,Oracle" />
<sql-case id="alter_table_with_back_quota" value="ALTER TABLE `t_order`
FORCE" db-types="MySQL" />
- <sql-case id="alter_table_add_column" value="ALTER TABLE t_order ADD
column4 VARCHAR(10)" db-types="MySQL, PostgreSQL,openGauss" />
- <sql-case id="alter_table_add_columns" value="ALTER TABLE t_order ADD
column4 VARCHAR(10), ADD column5 VARCHAR(10), ADD column6 VARCHAR(10)"
db-types="MySQL,PostgreSQL,openGauss" />
+ <sql-case id="alter_table_add_column" value="ALTER TABLE t_order ADD
column4 VARCHAR(10)" db-types="MySQL, PostgreSQL,openGauss,Doris" />
+ <sql-case id="alter_table_add_columns" value="ALTER TABLE t_order ADD
column4 VARCHAR(10), ADD column5 VARCHAR(10), ADD column6 VARCHAR(10)"
db-types="MySQL,PostgreSQL,openGauss,Doris" />
<sql-case id="alter_table_add_columns_integer_type_mysql" value="ALTER
TABLE t_order ADD column4 INTEGER, ADD column5 TINYINT, ADD column6 MEDIUMINT"
db-types="MySQL" />
<sql-case id="alter_table_add_columns_integer_type_oracle" value="ALTER
TABLE t_order ADD (column4 INTEGER, column5 INT, column6 SMALLINT)"
db-types="Oracle" />
<sql-case id="alter_table_add_columns_fixed_point_type" value="ALTER TABLE
t_order ADD column4 decimal(10,2), ADD column5 NUMERIC"
db-types="MySQL,PostgreSQL,openGauss" />
@@ -91,7 +91,7 @@
<sql-case id="alter_table_modify_column_with_first_after" value="ALTER
TABLE t_order MODIFY status VARCHAR(20) FIRST, MODIFY user_id INT AFTER status"
db-types="MySQL" />
<sql-case id="alter_table_drop_column" value="ALTER TABLE t_order DROP
COLUMN user_id" db-types="PostgreSQL" />
<sql-case id="alter_table_drop_columns" value="ALTER TABLE t_order DROP
user_id, DROP column5" db-types="MySQL,PostgreSQL,openGauss" />
- <sql-case id="alter_table_change_column" value="ALTER TABLE t_order CHANGE
user_id column4 VARCHAR(10)" db-types="MySQL" />
+ <sql-case id="alter_table_change_column" value="ALTER TABLE t_order CHANGE
user_id column4 VARCHAR(10)" db-types="MySQL,Doris" />
<sql-case id="alter_table_add_primary_key" value="ALTER TABLE t_order ADD
CONSTRAINT pk_user_id PRIMARY KEY (user_id)" />
<sql-case id="alter_table_add_composite_primary_key" value="ALTER TABLE
t_order ADD status int,ADD CONSTRAINT pk_order_id_user_id_status PRIMARY KEY
(order_id, user_id, status)" db-types="MySQL,PostgreSQL,openGauss" />
<!-- TODO support PostgreSQL,openGauss-->
@@ -109,7 +109,7 @@
<sql-case id="alter_table_drop_index" value="ALTER TABLE t_order DROP
INDEX order_index" db-types="MySQL" />
<sql-case id="alter_table_drop_indexes" value="ALTER TABLE t_order DROP
INDEX idx_column1, DROP INDEX idx_column2, DROP INDEX idx_column3"
db-types="MySQL" />
<sql-case id="alter_table_rename_index" value="ALTER TABLE t_order RENAME
INDEX idx_column1_bak TO idx_column1" db-types="MySQL" />
- <sql-case id="alter_table_composite_operate_columns" value="ALTER TABLE
t_order ADD column4 VARCHAR(10), ADD column5 VARCHAR(10), ADD column6
VARCHAR(10),MODIFY user_id bigint, drop column status" db-types="MySQL" />
+ <sql-case id="alter_table_composite_operate_columns" value="ALTER TABLE
t_order ADD column4 VARCHAR(10), ADD column5 VARCHAR(10), ADD column6
VARCHAR(10),MODIFY user_id bigint, drop column status" db-types="MySQL,Doris" />
<sql-case id="alter_table_with_quota" value="ALTER TABLE
"t_order" PARALLEL" db-types="Oracle" />
<!-- TODO support PostgreSQL,openGauss-->
<sql-case id="alter_table_add_check" value="ALTER TABLE t_order ADD
CONSTRAINT chk_order_id CHECK (order_id > 0)" db-types="Oracle,SQLServer" />
@@ -172,8 +172,8 @@
<sql-case id="alter_table_modify_range_partition" value="ALTER TABLE
t_order MODIFY PARTITION hash_partition ADD SUBPARTITION P1
VALUES('COMPLETE');" db-types="Oracle" />
<sql-case id="alter_table_move_partition" value="ALTER TABLE t_order MOVE
PARTITION p0 TABLESPACE space0" db-types="Oracle" />
<sql-case id="alter_table_coalesce_partition" value="ALTER TABLE t_order
COALESCE PARTITION" db-types="Oracle" />
- <sql-case id="alter_table_convert" value="ALTER TABLE t1 CONVERT TO
CHARACTER SET DEFAULT" db-types="MySQL" />
- <sql-case id="alter_table_convert_collate" value="ALTER TABLE t1 CONVERT
TO CHARACTER SET DEFAULT COLLATE cp1251_bin;" db-types="MySQL" />
+ <sql-case id="alter_table_convert" value="ALTER TABLE t1 CONVERT TO
CHARACTER SET DEFAULT" db-types="MySQL,Doris" />
+ <sql-case id="alter_table_convert_collate" value="ALTER TABLE t1 CONVERT
TO CHARACTER SET DEFAULT COLLATE cp1251_bin;" db-types="MySQL,Doris" />
<sql-case id="alter_table_add_column_with_visible" value="ALTER TABLE
t_order ADD COLUMN status VARCHAR(10) VISIBLE" db-types="MySQL" />
<sql-case id="alter_table_add_column_with_invisible" value="ALTER TABLE
t_order ADD COLUMN status VARCHAR(10) INVISIBLE" db-types="MySQL" />
<sql-case id="alter_table_modify_column_with_visible" value="ALTER TABLE
t_order MODIFY COLUMN status VARCHAR(10) VISIBLE" db-types="MySQL" />
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/do.xml
b/test/it/parser/src/main/resources/sql/supported/dml/do.xml
index 2e839dca9cf..399f96dc4b7 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/do.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/do.xml
@@ -17,7 +17,7 @@
-->
<sql-cases>
- <sql-case id="do_constant" value="DO 1" db-types="MySQL" />
+ <sql-case id="do_constant" value="DO 1" db-types="MySQL,Doris" />
<sql-case id="do_sleep" value="DO SLEEP(1)" db-types="MySQL" />
<sql-case id="do_multiple_sleep" value="DO SLEEP(1), SLEEP(2)"
db-types="MySQL" />
<sql-case id="do_with_function_1" value="DO
FROM_BASE64(CAST((MID(UUID(),20,64)) AS BINARY(55)))" 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 6a3546fb1ee..b54de1e86e6 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
@@ -45,7 +45,7 @@
<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-case id="presto_select_current_time_precision" value="SELECT
CURRENT_TIME(3)" db-types="Presto,Doris" />
<sql-case id="presto_select_predicate_not_variants" value="SELECT * FROM
t_order WHERE flag IS NOT NULL AND status NOT LIKE '%x%' AND status NOT REGEXP
'[12]' AND status NOT RLIKE '[34]' AND order_id NOT IN (1, 2)"
db-types="Presto" />
<sql-case id="presto_select_collate_params" value="SELECT 'a' COLLATE
utf8mb4_bin, 'b' COLLATE latin1_bin FROM t_order" db-types="Presto" />
<sql-case id="presto_select_agg_group_having_order" value="SELECT DISTINCT
status, COUNT(DISTINCT order_id) AS cnt FROM t_order GROUP BY status HAVING
COUNT(*) > 1 ORDER BY 2 DESC, status ASC" db-types="Presto" />
@@ -53,7 +53,7 @@
<sql-case id="presto_select_lock_for_update" value="SELECT * FROM t_order
FOR UPDATE" db-types="Presto" />
<sql-case id="presto_values_constructor" value="VALUES ROW(1, 2)"
db-types="Presto" />
<sql-case id="presto_table_statement" value="TABLE t_order"
db-types="Presto" />
- <sql-case id="presto_select_limit_comma" value="SELECT * FROM t_order
LIMIT 2, 5" db-types="Presto" />
+ <sql-case id="presto_select_limit_comma" value="SELECT * FROM t_order
LIMIT 2, 5" db-types="Presto,Doris" />
<sql-case id="presto_select_join_natural_left" value="SELECT * FROM
t_order NATURAL LEFT JOIN t_order_item" db-types="Presto" />
<sql-case id="presto_select_system_variable" value="SELECT
@@global.time_zone" db-types="Presto" />
<sql-case id="presto_select_parameter_marker" value="SELECT ?"
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 f4121d5a314..4c2aac549e8 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
@@ -48,7 +48,7 @@
<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_sounds_like" value="SELECT
* FROM t_order WHERE t_order.order_id SOUNDS LIKE '1%'" db-types="MySQL,Presto"
/>
+ <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"
/>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
index 8a010cb55dc..2211485033a 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-group-by.xml
@@ -31,9 +31,9 @@
<sql-case id="select_with_item_alias_match_order_by_and_group_by_items"
value="SELECT o.user_id uid FROM t_order o GROUP BY o.user_id ORDER BY
o.user_id" db-types="H2,MySQL,SQLServer,PostgreSQL,openGauss" />
<sql-case id="select_group_by_with_date_function" value="SELECT
date_format(creation_date, '%y-%m-%d') as creation_date, count(*) as c_number
FROM `t_order_item` WHERE order_id in (?, ?) GROUP BY
date_format(creation_date, '%y-%m-%d')" db-types="MySQL" />
<sql-case id="select_group_by_with_keyword_alias" value="SELECT
SUM(order_id) AS orders_sum, user_id as `key` FROM t_order GROUP BY `key`"
db-types="MySQL" />
- <sql-case id="select_group_by_with_count_without_column_name"
value="SELECT COUNT(order_id) AS orders_count, user_id FROM t_order GROUP BY 2
ORDER BY 2" db-types="MySQL,Oracle,SQLServer,PostgreSQL,openGauss" />
- <sql-case id="select_group_by_with_having" value="SELECT COUNT(order_id)
AS orders_count, user_id FROM t_order GROUP BY user_id HAVING orders_count > 0"
/>
- <sql-case id="select_group_by_with_having_count" value="SELECT
COUNT(order_id), user_id FROM t_order GROUP BY user_id HAVING COUNT(order_id) >
0" />
+ <sql-case id="select_group_by_with_count_without_column_name"
value="SELECT COUNT(order_id) AS orders_count, user_id FROM t_order GROUP BY 2
ORDER BY 2" db-types="MySQL,Oracle,SQLServer,PostgreSQL,openGauss,Doris" />
+ <sql-case id="select_group_by_with_having" value="SELECT COUNT(order_id)
AS orders_count, user_id FROM t_order GROUP BY user_id HAVING orders_count > 0"
db-types="MySQL,Oracle,SQLServer,PostgreSQL,openGauss,Doris" />
+ <sql-case id="select_group_by_with_having_count" value="SELECT
COUNT(order_id), user_id FROM t_order GROUP BY user_id HAVING COUNT(order_id) >
0" db-types="MySQL,Oracle,SQLServer,PostgreSQL,openGauss,Doris" />
<sql-case id="select_group_by_with_having_and_window" value="SELECT
COUNT(order_id) AS orders_count, user_id FROM t_order GROUP BY user_id HAVING
orders_count > 0 WINDOW w AS (PARTITION BY user_id)"
db-types="MySQL,PostgreSQL,openGauss" />
<sql-case id="select_group_by_cube" value="SELECT
DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS
department_name, DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id,
COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal" FROM
employees e, departments d WHERE d.department_id = e.department_id GROUP BY
CUBE (department_name, job_id) ORDER BY department_name, job_id"
db-types="Oracle" />
<sql-case id="select_group_by_grouping_sets" value="SELECT channel_desc,
calendar_month_desc, co.country_id, TO_CHAR(SUM(amount_sold) , '9,999,999,999')
SALES$ FROM sales, customers, times, channels, countries co WHERE
sales.time_id=times.time_id GROUP BY GROUPING
SETS((channel_desc,calendar_month_desc,co.country_id),
(channel_desc,co.country_id), (calendar_month_desc,co.country_id))"
db-types="Oracle" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-into.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-into.xml
index 07ccfe0e06a..d72ed723ce8 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-into.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-into.xml
@@ -38,4 +38,6 @@
<sql-case id="select_into_outfile_doris_with_properties" value="SELECT *
FROM t_order INTO OUTFILE 'hdfs://path/to/result_' PROPERTIES
('column_separator' = ',', 'line_delimiter' = '\n')" db-types="Doris"/>
<sql-case id="select_into_outfile_doris_full" value="SELECT * FROM t_order
INTO OUTFILE 'hdfs://path/to/result_' FORMAT AS CSV PROPERTIES ('broker.name' =
'my_broker', 'column_separator' = ',', 'max_file_size' = '100MB')"
db-types="Doris"/>
<sql-case id="select_into_outfile_doris_with_parquet" value="SELECT * FROM
t_order INTO OUTFILE 's3://bucket/result_' FORMAT AS PARQUET PROPERTIES
('s3.endpoint' = 'http://s3.amazonaws.com', 's3.access_key' = 'xxx',
's3.secret_key' = 'yyy')" db-types="Doris"/>
+ <sql-case id="select_into_outfile_doris_with_fields" value="SELECT * FROM
t_order INTO OUTFILE 'hdfs://path/to/result_' FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'" db-types="Doris"/>
+ <sql-case id="select_into_outfile_doris_with_fields_and_escaped"
value="SELECT user_id, status FROM t_order INTO OUTFILE
'hdfs://path/to/result_' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY
'"' ESCAPED BY '\\'" db-types="Doris"/>
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-order-by.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-order-by.xml
index ab377693811..47dc76524a7 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-order-by.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-order-by.xml
@@ -17,7 +17,7 @@
-->
<sql-cases>
- <sql-case id="select_order_by_asc_and_index_desc" value="SELECT * FROM
t_order o ORDER BY o.order_id, 2 DESC" />
+ <sql-case id="select_order_by_asc_and_index_desc" value="SELECT * FROM
t_order o ORDER BY o.order_id, 2 DESC" db-types="Doris" />
<sql-case id="select_order_by_desc_and_index_asc" value="SELECT i.* FROM
t_order o, t_order_item i WHERE o.order_id = i.order_id AND o.status = 'init'
ORDER BY o.order_id DESC, 1" />
<sql-case id="select_order_by_with_ordered_column" value="SELECT
o.order_id AS gen_order_id_ FROM t_order o ORDER BY o.order_id" />
<sql-case id="select_order_by_with_date" value="SELECT i.* FROM t_order o,
t_order_item i WHERE o.order_id = i.order_id AND o.status = 'init' ORDER BY
i.creation_date DESC, o.order_id DESC, i.item_id" />
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 a3850ba7499..fe7a1f3bf46 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
@@ -233,6 +233,7 @@
<sql-case id="select_json_overlaps" value="SELECT
JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]")" db-types="MySQL" />
<sql-case id="select_json_pretty" value="SELECT JSON_PRETTY('123')"
db-types="MySQL" />
<sql-case id="select_json_quote" value="SELECT JSON_QUOTE('a')"
db-types="MySQL,Doris" />
+ <sql-case id="select_json_extract_cast_abs_doris" value="SELECT
JSON_EXTRACT(order_id, '$.a'), CAST(order_id AS DATETIME(3)), ABS(-1) FROM
t_order" db-types="Doris" />
<sql-case id="select_json_remove" value="SELECT
JSON_REMOVE('["a", "b", "d"]', '$[0]')"
db-types="MySQL" />
<sql-case id="select_json_replace" value="SELECT JSON_REPLACE('{
"a": 1, "b": "2"}', '$.a', 10)" db-types="MySQL"
/>
<sql-case id="select_json_schema_valid" value="SELECT
JSON_SCHEMA_VALID('{"type":"object","required":["name","value"]}','{"name":"a","value":10}')"
db-types="MySQL" />
@@ -295,7 +296,7 @@
<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_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" />
+ <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" />
<sql-case id="select_oct" value="SELECT OCT(12)" db-types="MySQL" />
<sql-case id="select_octet_length" value="SELECT OCTET_LENGTH('text')"
db-types="MySQL" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
index ed54f82f3c6..34d2822ab3e 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-with.xml
@@ -23,7 +23,7 @@
<sql-case
id="select_with_subquery_factoring_with_search_depth_first_with_cycle"
value="WITH dup_hiredate (eid, emp_last, mgr_id, reportLevel, hire_date,
job_id) AS (SELECT employee_id, last_name, manager_id, reportLevel, hire_date,
job_id FROM employees WHERE manager_id IS NULL) SEARCH DEPTH FIRST BY hire_date
SET order1 CYCLE hire_date SET is_cycle TO 'Y' DEFAULT 'N' SELECT lpad('
',2*reportLevel)||emp_last emp_name, eid, mgr_id, hire_date, job_id, is_cycle
FROM dup_hiredate ORDER [...]
<sql-case
id="select_with_subquery_factoring_with_search_depth_first_with_having"
value="WITH emp_count (eid, emp_last, mgr_id, mgrLevel, salary, cnt_employees)
AS (SELECT employee_id, last_name, manager_id, mgrLevel, salary, cnt_employees
FROM employees) SEARCH DEPTH FIRST BY emp_last SET order1 SELECT emp_last, eid,
mgr_id, salary FROM emp_count GROUP BY emp_last, eid, mgr_id, salary HAVING
salary > 24000 ORDER BY mgr_id NULLS FIRST, emp_last" db-types="Oracle" />
<sql-case id="select_with_multiple_cte_definitions" value="WITH
cte1(status, user_id) AS (SELECT status, user_id FROM t_order), cte2(item_id)
AS (SELECT item_id FROM t_order_item) SELECT status, user_id, item_id FROM cte1
INNER JOIN cte2 ON cte1.user_id = cte2.user_id" db-types="SQLServer" />
- <sql-case id="select_with_single_subquery" value="WITH t AS (SELECT a+2
c,b FROM t1) SELECT c,b FROM t" db-types="MySQL" />
+ <sql-case id="select_with_single_subquery" value="WITH t AS (SELECT a+2
c,b FROM t1) SELECT c,b FROM t" db-types="MySQL,Doris" />
<sql-case id="select_with_oracle_single_subquery" value="WITH t AS (SELECT
a+2 c,b FROM t1) SELECT c,b FROM t" db-types="Oracle" />
<sql-case id="select_with_multiple_subquery" value="WITH cte1(col1, col2,
col3) as (SELECT emp_no,first_name,last_name FROM employees WHERE
emp_no=10012), cte2(col1, col2, col3) as (SELECT emp_no,first_name,last_name
from employees WHERE emp_no=10012) SELECT col1, col2, col3 FROM cte1"
db-types="MySQL" />
<sql-case id="select_with_recursive_union_all1" value="WITH RECURSIVE
DirectoryCTE as (SELECT * FROM table1 WHERE id = 1 AND project_id = 2 UNION ALL
SELECT * FROM project_file_catalog t INNER JOIN DirectoryCTE cte ON
t.project_id = cte.project_id AND t.parent_id = cte.id) SELECT * FROM
DirectoryCTE ORDER BY level" 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 6dda260f00c..da99f51a5ae 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
@@ -233,6 +233,7 @@
<sql-case id="select_date_literal_doris" value="SELECT DATE '2020-01-01'"
db-types="Doris" />
<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_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" />
