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 7032f5b1424 Add SQL parser test cases for Firebird (#37715)
7032f5b1424 is described below
commit 7032f5b1424302c30f7e9b996b60d24585e3f24b
Author: Liang Zhang <[email protected]>
AuthorDate: Mon Jan 12 17:38:46 2026 +0800
Add SQL parser test cases for Firebird (#37715)
* Add SQL parser test cases for Hive
* Add SQL parser test cases for Firebird
* Add SQL parser test cases for Firebird
---
.../src/main/resources/case/dml/select-join.xml | 46 +++++++
.../main/resources/case/dml/select-pagination.xml | 13 ++
.../parser/src/main/resources/case/dml/select.xml | 135 +++++++++++++++++++++
.../resources/sql/supported/dml/clickhouse.xml | 10 +-
.../main/resources/sql/supported/dml/delete.xml | 2 +-
.../sql/supported/dml/select-distinct.xml | 2 +-
.../sql/supported/dml/select-expression.xml | 2 +-
.../resources/sql/supported/dml/select-join.xml | 6 +-
.../sql/supported/dml/select-order-by.xml | 2 +-
.../sql/supported/dml/select-pagination.xml | 1 +
.../sql/supported/dml/select-sub-query.xml | 8 +-
.../resources/sql/supported/dml/select-with.xml | 2 +-
.../main/resources/sql/supported/dml/select.xml | 14 ++-
13 files changed, 224 insertions(+), 19 deletions(-)
diff --git a/test/it/parser/src/main/resources/case/dml/select-join.xml
b/test/it/parser/src/main/resources/case/dml/select-join.xml
index 983e33483a5..131c3b0bfe3 100644
--- a/test/it/parser/src/main/resources/case/dml/select-join.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-join.xml
@@ -3412,4 +3412,50 @@
</column-projection>
</projections>
</select>
+ <select sql-case-id="select_right_join_firebird">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <join-table join-type="RIGHT">
+ <left>
+ <simple-table name="t_order" alias="o" start-index="14"
stop-index="22" />
+ </left>
+ <right>
+ <simple-table name="t_order_item" alias="i"
start-index="35" stop-index="48" />
+ </right>
+ <on-condition>
+ <binary-operation-expression start-index="53"
stop-index="75">
+ <left>
+ <column name="order_id" start-index="53"
stop-index="62">
+ <owner name="o" start-index="53"
stop-index="53" />
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="order_id" start-index="66"
stop-index="75">
+ <owner name="i" start-index="66"
stop-index="66" />
+ </column>
+ </right>
+ </binary-operation-expression>
+ </on-condition>
+ </join-table>
+ </from>
+ </select>
+
+ <select sql-case-id="select_cross_join_firebird">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from>
+ <join-table join-type="CROSS">
+ <left>
+ <simple-table name="t_order" start-index="14"
stop-index="20" />
+ </left>
+ <right>
+ <simple-table name="t_order_item" start-index="33"
stop-index="44" />
+ </right>
+ </join-table>
+ </from>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select-pagination.xml
b/test/it/parser/src/main/resources/case/dml/select-pagination.xml
index 46c0648b8fc..a3883606202 100644
--- a/test/it/parser/src/main/resources/case/dml/select-pagination.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-pagination.xml
@@ -2084,6 +2084,19 @@
</from>
</select>
+ <select
sql-case-id="select_pagination_with_first_skip_parameters_firebird"
parameters="1, 2">
+ <limit start-index="7" stop-index="20">
+ <row-count value="1" parameter-index="0" start-index="13"
stop-index="13" />
+ <offset value="2" parameter-index="1" start-index="20"
stop-index="20" />
+ </limit>
+ <projections start-index="22" stop-index="28">
+ <column-projection name="user_id" start-index="22" stop-index="28"
/>
+ </projections>
+ <from start-index="30" stop-index="41">
+ <simple-table name="t_order" start-index="35" stop-index="41" />
+ </from>
+ </select>
+
<select sql-case-id="select_limit_offset_doris">
<from>
<simple-table name="t_order" start-index="14" stop-index="20" />
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 5d5a82dc724..f4b020c8f44 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -12123,4 +12123,139 @@
</function-table>
</from>
</select>
+
+ <select sql-case-id="select_interval_firebird">
+ <projections start-index="7" stop-index="20">
+ <expression-projection text="INTERVAL 1 DAY" start-index="7"
stop-index="20">
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="27" stop-index="33" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_having_firebird">
+ <projections start-index="7" stop-index="30">
+ <column-projection name="user_id" start-index="7" stop-index="13"
/>
+ <aggregation-projection type="COUNT" expression="COUNT(*)"
start-index="16" stop-index="23" alias="cnt" />
+ </projections>
+ <from start-index="32" stop-index="43">
+ <simple-table name="t_order" start-index="37" stop-index="43" />
+ </from>
+ <group-by start-index="45" stop-index="60">
+ <column-item name="user_id" start-index="54" stop-index="60" />
+ </group-by>
+ <having start-index="62" stop-index="80">
+ <expr>
+ <binary-operation-expression start-index="69" stop-index="80">
+ <left>
+ <aggregation-projection type="COUNT"
expression="COUNT(*)" start-index="69" stop-index="76" />
+ </left>
+ <operator>></operator>
+ <right>
+ <literal-expression value="1" start-index="80"
stop-index="80" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </having>
+ </select>
+
+ <select sql-case-id="select_function_table_firebird">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from start-index="14" stop-index="19">
+ <function-table start-index="14" stop-index="19">
+ <table-function function-name="foo" text="foo(1)"
start-index="14" stop-index="19" />
+ </function-table>
+ </from>
+ </select>
+
+ <select sql-case-id="select_cast_int_firebird">
+ <projections start-index="7" stop-index="26">
+ <expression-projection text="CAST(user_id AS INT)" start-index="7"
stop-index="26">
+ <expr>
+ <function function-name="CAST" text="CAST(user_id AS INT)"
start-index="7" stop-index="26">
+ <parameter>
+ <column name="user_id" start-index="12"
stop-index="18" />
+ </parameter>
+ <parameter>
+ <data-type value="INT" start-index="23"
stop-index="25" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="33" stop-index="39" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_gen_id_with_identifier_firebird">
+ <projections start-index="7" stop-index="20">
+ <expression-projection text="GEN_ID(seq, 1)" start-index="7"
stop-index="20">
+ <expr>
+ <function function-name="GEN_ID" text="GEN_ID(seq, 1)"
start-index="7" stop-index="20">
+ <parameter>
+ <variable-segment start-index="14" stop-index="16"
variable="seq" />
+ </parameter>
+ <parameter>
+ <literal-expression value="1" start-index="19"
stop-index="19" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="27" stop-index="33" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_gen_id_with_parameters_firebird"
parameters="1, 2">
+ <projections start-index="7" stop-index="18">
+ <expression-projection text="GEN_ID(?, ?)" start-index="7"
stop-index="18">
+ <expr>
+ <function function-name="GEN_ID" text="GEN_ID(?, ?)"
start-index="7" stop-index="18">
+ <parameter>
+ <literal-expression value="1" start-index="7"
stop-index="18" />
+ <parameter-marker-expression parameter-index="0"
start-index="7" stop-index="18" />
+ </parameter>
+ <parameter>
+ <literal-expression value="2" start-index="17"
stop-index="17" />
+ <parameter-marker-expression parameter-index="1"
start-index="17" stop-index="17" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="25" stop-index="31" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_variable_default_firebird">
+ <projections start-index="7" stop-index="13">
+ <expression-projection text="DEFAULT" start-index="7"
stop-index="13">
+ <expr>
+ <variable-segment start-index="7" stop-index="13"
variable="DEFAULT" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="20" stop-index="26" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_parenthesized_column_firebird">
+ <projections start-index="7" stop-index="15">
+ <expression-projection text="user_id" start-index="7"
stop-index="15">
+ <expr>
+ <column name="user_id" start-index="8" stop-index="14" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t_order" start-index="22" stop-index="28" />
+ </from>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/clickhouse.xml
b/test/it/parser/src/main/resources/sql/supported/dml/clickhouse.xml
index 8041cd11c66..1831796945a 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/clickhouse.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/clickhouse.xml
@@ -22,12 +22,12 @@
<sql-case id="clickhouse_update_left_join" value="UPDATE t_order o LEFT
JOIN t_order_item i ON o.order_id = i.order_id SET o.status = 1, i.status = 2
WHERE o.order_id = ? AND i.user_id = ?" db-types="ClickHouse" />
<sql-case id="clickhouse_delete_with_owner" value="ALTER TABLE db1.t_order
DELETE WHERE order_id = ?" db-types="ClickHouse" />
<sql-case id="clickhouse_select_distinct_join_using" value="SELECT
DISTINCT *, o.*, sum(i.amount) AS total_amount, o.status AS status_alias,
o.order_id + i.order_id AS merged_id, toString(o.user_id) AS user_text FROM
t_order o JOIN t_order_item i USING (order_id) WHERE o.status = ? AND i.amount
> 100 GROUP BY o.order_id, i.order_id, o.status, o.user_id HAVING
sum(i.amount) > 1000 ORDER BY total_amount DESC, merged_id"
db-types="ClickHouse" />
- <sql-case id="clickhouse_select_literals" value="SELECT 0x1A AS hex_lit,
0b101 AS bit_lit, TRUE AS bool_lit, NULL AS nil FROM t_order"
db-types="ClickHouse" />
+ <sql-case id="clickhouse_select_literals" value="SELECT 0x1A AS hex_lit,
0b101 AS bit_lit, TRUE AS bool_lit, NULL AS nil FROM t_order"
db-types="ClickHouse,Firebird" />
<sql-case id="clickhouse_select_functions_right_join" value="SELECT
POSITION('a' IN 'abc') AS pos, CAST(order_id AS DATETIME64(3)) AS price, CAST(1
AS INT8) AS tiny, INTERVAL 2 DAY AS inter, COUNT(DISTINCT user_id) AS cnt FROM
t_order ORDER BY 1" db-types="ClickHouse" />
- <sql-case id="clickhouse_select_not_predicate" value="SELECT * FROM
t_order WHERE NOT (status > 0)" db-types="ClickHouse" />
- <sql-case id="clickhouse_select_not_like" value="SELECT * FROM t_order
WHERE status NOT LIKE 'A%'" db-types="ClickHouse" />
+ <sql-case id="clickhouse_select_not_predicate" value="SELECT * FROM
t_order WHERE NOT (status > 0)" db-types="ClickHouse,Firebird" />
+ <sql-case id="clickhouse_select_not_like" value="SELECT * FROM t_order
WHERE status NOT LIKE 'A%'" db-types="ClickHouse,Firebird" />
<sql-case id="clickhouse_select_in_list_and_subquery" value="SELECT * FROM
t_order WHERE user_id IN (1, 2, 3) AND order_id IN (SELECT order_id FROM
t_order_item) AND status IS NOT NULL" db-types="ClickHouse" />
- <sql-case id="clickhouse_select_subquery_compare" value="SELECT * FROM
t_order WHERE amount = (SELECT 1)" db-types="ClickHouse" />
+ <sql-case id="clickhouse_select_subquery_compare" value="SELECT * FROM
t_order WHERE amount = (SELECT 1)" db-types="ClickHouse,Firebird" />
<sql-case id="clickhouse_select_param_projection" value="SELECT ? AS
param_alias" db-types="ClickHouse" case-types="PLACEHOLDER" />
<sql-case id="clickhouse_select_subquery_projection" value="SELECT (SELECT
1) AS sub_val FROM t_order" db-types="ClickHouse" />
<sql-case id="clickhouse_update_without_where" value="UPDATE t_order SET
status = 0" db-types="ClickHouse" />
@@ -45,7 +45,7 @@
<sql-case id="clickhouse_select_is_false" value="SELECT * FROM t_order
WHERE is_deleted IS FALSE" db-types="ClickHouse" />
<sql-case id="clickhouse_select_parenthesized_right_cross_join"
value="SELECT * FROM (t_order o RIGHT JOIN t_order_item i ON o.order_id =
i.order_id) CROSS JOIN t_user u" db-types="ClickHouse" />
<sql-case id="clickhouse_select_compare_any_subquery_paren" value="SELECT
* FROM t_order WHERE (status = 1)" db-types="ClickHouse" />
- <sql-case id="clickhouse_insert_default_value" value="INSERT INTO t_order
VALUES (DEFAULT)" db-types="ClickHouse" />
+ <sql-case id="clickhouse_insert_default_value" value="INSERT INTO t_order
VALUES (DEFAULT)" db-types="ClickHouse,Firebird" />
<sql-case id="clickhouse_select_addition_no_alias" value="SELECT order_id
+ 1 FROM t_order" db-types="ClickHouse" />
<sql-case id="clickhouse_select_hex_bit_literals" value="SELECT * FROM
t_order WHERE hex_col = X'1A' AND bit_col = B'101'" db-types="ClickHouse" />
<sql-case id="clickhouse_update_set_default" value="UPDATE t_order SET
status = DEFAULT WHERE order_id = 1" db-types="ClickHouse" />
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 79730431277..8c51bbd2d8a 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
@@ -20,7 +20,7 @@
<sql-case id="delete_with_sharding_value" value="DELETE FROM t_order WHERE
order_id = ? AND user_id = ? AND status=?" />
<sql-case id="delete_without_sharding_value" value="DELETE FROM t_order
WHERE status=?" />
<sql-case id="delete_with_special_character_without_sharding_value"
value="DELETE FROM `t_order` WHERE `status`='init'" db-types="MySQL,Doris" />
- <sql-case id="delete_with_alias" value="DELETE FROM t_order AS o WHERE
status=?" db-types="MySQL,SQLServer,Doris" />
+ <sql-case id="delete_with_alias" value="DELETE FROM t_order AS o WHERE
status=?" db-types="MySQL,SQLServer,Doris,Firebird" />
<sql-case id="delete_with_alias_without_as" value="DELETE
product_price_history pp WHERE (product_id, currency_code, effective_from_date)
IN (SELECT product_id, currency_code, MAX(effective_from_date) FROM
product_price_history GROUP BY product_id, currency_code)" db-types="Oracle" />
<sql-case id="delete_with_order_by_row_count" value="DELETE FROM t_order
WHERE order_id = ? AND user_id = ? AND status=? ORDER BY order_id LIMIT ?"
db-types="MySQL,Doris,Hive" />
<sql-case id="delete_with_output_clause" value="DELETE FROM t_order OUTPUT
DELETED.order_id, DELETED.user_id INTO @MyTableVar (temp_order_id,
temp_user_id) WHERE order_id = ?" db-types="SQLServer" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-distinct.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-distinct.xml
index f8840ece0d4..a6ea1c5faae 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-distinct.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-distinct.xml
@@ -34,7 +34,7 @@
<sql-case id="select_distinct_with_star" value="SELECT DISTINCT * FROM
t_order WHERE order_id > 1100 ORDER BY order_id" />
<sql-case id="select_distinct_with_owner_star" value="SELECT DISTINCT
t_order.*, t_order_item.order_id FROM t_order, t_order_item WHERE
t_order.order_id = t_order_item.order_id ORDER BY t_order.order_id" />
<sql-case id="select_distinct_with_sum" value="SELECT SUM(DISTINCT
order_id) s FROM t_order WHERE order_id < 1100" />
- <sql-case id="select_distinct_with_count" value="SELECT COUNT(DISTINCT
order_id) c FROM t_order WHERE order_id < 1100"
db-types="MySQL,PostgreSQL,openGauss,Doris" />
+ <sql-case id="select_distinct_with_count" value="SELECT COUNT(DISTINCT
order_id) c FROM t_order WHERE order_id < 1100"
db-types="MySQL,PostgreSQL,openGauss,Doris,Firebird" />
<sql-case id="select_distinct_with_avg" value="SELECT AVG(DISTINCT
order_id) FROM t_order WHERE order_id < 1100" db-types="MySQL" />
<!--TODO The metadata of MySQL is different from which of H2. For now, we
could only specify one db-types from MySQL or H2. We need to support different
expected data in one case.-->
<sql-case id="select_distinct_with_count_sum" value="SELECT COUNT(DISTINCT
order_id), SUM(DISTINCT order_id) FROM t_order WHERE order_id < 1100"
db-types="MySQL" />
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 b4a9124561d..d1673f76f8a 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
@@ -107,7 +107,7 @@
<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,Hive" />
+ <sql-case id="select_string_concat_with_double_bar" value="SELECT 'a' ||
'b'" db-types="Doris,Hive,Firebird" />
<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-join.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
index 94cbecddea7..bc1abe16830 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
@@ -19,7 +19,7 @@
<sql-cases>
<sql-case id="select_inner_join_related_with_alias" value="SELECT i.* FROM
t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.order_id
= ?" />
<sql-case id="select_inner_join_related_with_name" value="SELECT
t_order_item.* FROM t_order JOIN t_order_item ON t_order.order_id =
t_order_item.order_id WHERE t_order.order_id = ?" />
- <sql-case id="select_join_using" value="SELECT i.* FROM t_order o JOIN
t_order_item i USING(order_id) WHERE o.order_id = ?"
db-types="MySQL,PostgreSQL,openGauss,Doris" />
+ <sql-case id="select_join_using" value="SELECT i.* FROM t_order o JOIN
t_order_item i USING(order_id) WHERE o.order_id = ?"
db-types="MySQL,PostgreSQL,openGauss,Doris,Firebird" />
<sql-case id="select_left_outer_join_related_with_alias" value="SELECT
d.department_id, e.last_name FROM departments d LEFT OUTER JOIN employees e ON
d.department_id = e.department_id ORDER BY d.department_id, e.last_name"
db-types="MySQL, Oracle" />
<sql-case id="select_right_outer_join_related_with_alias" value="SELECT
d.department_id, e.last_name FROM departments d RIGHT OUTER JOIN employees e ON
d.department_id = e.department_id ORDER BY d.department_id, e.last_name"
db-types="MySQL, Oracle" />
<sql-case id="select_full_outer_join_related_with_alias" value="SELECT
d.department_id AS d_dept_id, e.department_id AS e_dept_id, e.last_name FROM
departments d FULL OUTER JOIN employees e ON d.department_id = e.department_id
ORDER BY d.department_id, e.last_name" db-types="Oracle" />
@@ -32,7 +32,7 @@
<sql-case id="select_natural_full_join" value="SELECT * FROM t_order o
NATURAL FULL JOIN t_order_item i WHERE o.order_id = ?"
db-types="PostgreSQL,openGauss,Oracle" />
<sql-case id="select_with_join_operator" value="SELECT * FROM t_order o ,
t_order_item i WHERE o.order_id(+) = i.order_id" db-types="Oracle" />
<sql-case id="select_join_with_quote" value="SELECT "u".*,
"o".* FROM t_user "u" INNER JOIN t_order "o" ON
"u".user_id = "o".user_id WHERE "u".user_id = ?"
db-types="Oracle" />
- <sql-case id="select_outer_left_join_without_alias" value="SELECT * from
t1 LEFT JOIN t2 ON t1.id = t2.id" db-types="Oracle"/>
+ <sql-case id="select_outer_left_join_without_alias" value="SELECT * from
t1 LEFT JOIN t2 ON t1.id = t2.id" db-types="Oracle,Firebird"/>
<sql-case id="select_outer_full_join_without_alias" value="SELECT * FROM
t1 FULL JOIN t2 ON t1.id = t2.id" db-types="Oracle"/>
<sql-case id="select_natural_join_with_object_id_function" value="SELECT
c.name AS column_name,c.column_id,SCHEMA_NAME(t.schema_id) AS
type_schema,t.name AS
type_name,t.is_user_defined,t.is_assembly_type,c.max_length,c.precision,c.scale
FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('<schema_name.table_name>') ORDER BY
c.column_id" db-types="SQLServer"/>
<sql-case id="select_inner_join_with_object_id_function" value="SELECT
SCHEMA_NAME(schema_id) AS schema_name,o.name AS
object_name,o.type_desc,p.parameter_id,p.name AS
parameter_name,TYPE_NAME(p.user_type_id) AS
parameter_type,p.max_length,p.precision,p.scale,p.is_output FROM sys.objects AS
o INNER JOIN sys.parameters AS p ON o.object_id = p.object_id WHERE o.object_id
= OBJECT_ID('<schema_name.object_name>') ORDER BY schema_name,
object_name, p.parameter_id" db-types="SQLServer"/>
@@ -62,4 +62,6 @@
<sql-case id="select_cross_join_sys_dm_exec_requests" value="SELECT
session_id as SPID, command, a.text AS Query, start_time, percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as
estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP
DATABASE','RESTORE DATABASE')" db-types="SQLServer"/>
<sql-case id="select_from_join_with_json_table" value="SELECT c1, c2,
JSON_EXTRACT(c3, '$.*') FROM t1 AS m JOIN JSON_TABLE(m.c3, '$.*' COLUMNS(at
VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY, bt VARCHAR(10) PATH '$.b'DEFAULT
'2' ON EMPTY, ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY)) AS tt ON m.c1
> tt.at;" db-types="MySQL"/>
<sql-case id="select_join_with_brackets" value="SELECT t.ORDER_ID FROM
(t_order t INNER JOIN t_order_item i ON t.ORDER_ID = i.ORDER_ID) LEFT JOIN
T_ORDER c ON t.ORDER_ID = c.ORDER_ID" db-types="Oracle"/>
+ <sql-case id="select_right_join_firebird" value="SELECT * FROM t_order o
RIGHT JOIN t_order_item i ON o.order_id = i.order_id" db-types="Firebird"/>
+ <sql-case id="select_cross_join_firebird" value="SELECT * FROM t_order
CROSS JOIN t_order_item" db-types="Firebird"/>
</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 6cc2e5bbbbd..ced646ec2c2 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" db-types="Doris" />
+ <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,Firebird" />
<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-pagination.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-pagination.xml
index c772b6542d8..9a9fdce5351 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
@@ -41,6 +41,7 @@
<sql-case id="select_pagination_with_first" value="SELECT FIRST 1 1 a FROM
t_order" db-types="Firebird" />
<sql-case id="select_pagination_with_first_skip" value="SELECT FIRST 1
SKIP 1 1 a FROM t_order" db-types="Firebird" />
<sql-case id="select_pagination_with_skip" value="SELECT SKIP 1 1 a FROM
t_order" db-types="Firebird" />
+ <sql-case id="select_pagination_with_first_skip_parameters_firebird"
value="SELECT FIRST ? SKIP ? user_id FROM t_order" db-types="Firebird" />
<sql-case id="select_limit_offset_doris" value="SELECT * FROM t_order
LIMIT 5 OFFSET 2" db-types="Doris" />
<sql-case id="select_limit_only_doris" value="SELECT * FROM t_order LIMIT
5" db-types="Doris" />
<sql-case id="select_limit_with_params_doris" value="SELECT * FROM t_order
LIMIT ?, ?" db-types="Doris,Hive" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
index 2c893ca8516..81c96a2dbdf 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-sub-query.xml
@@ -18,11 +18,11 @@
<sql-cases>
<sql-case id="select_with_lateral" value="SELECT 1 FROM t1, LATERAL
(SELECT DISTINCT t1.x) AS dt1, LATERAL (SELECT DISTINCT dt1.x) AS dt2 WHERE
dt1.x = dt2.x" db-types="MySQL" />
- <sql-case id="select_sub_query_with_project" value="SELECT order_id,
(SELECT 1) AS num FROM t_order" db-types="MySQL, PostgreSQL,openGauss,
SQLServer" />
+ <sql-case id="select_sub_query_with_project" value="SELECT order_id,
(SELECT 1) AS num FROM t_order" db-types="MySQL, PostgreSQL,openGauss,
SQLServer,Firebird" />
<sql-case id="select_sub_query_with_table" value="SELECT t.* FROM (SELECT
* FROM t_order WHERE order_id IN (?, ?)) t" />
- <sql-case id="select_with_equal_subquery" value="SELECT * FROM t_order
WHERE user_id = (SELECT user_id FROM t_order_item WHERE id = 10)"
db-types="MySQL, PostgreSQL,openGauss" />
+ <sql-case id="select_with_equal_subquery" value="SELECT * FROM t_order
WHERE user_id = (SELECT user_id FROM t_order_item WHERE id = 10)"
db-types="MySQL, PostgreSQL,openGauss,Firebird" />
<sql-case id="select_with_any_subquery" value="SELECT * FROM employees
WHERE salary = ANY (SELECT salary FROM employees WHERE department_id = 30)
ORDER BY employee_id;" db-types="Oracle" />
- <sql-case id="select_with_in_subquery" value="SELECT * FROM t_order WHERE
user_id IN (SELECT user_id FROM t_order_item WHERE id IN (10, 11))"
db-types="MySQL,PostgreSQL,openGauss,Doris,Hive" />
+ <sql-case id="select_with_in_subquery" value="SELECT * FROM t_order WHERE
user_id IN (SELECT user_id FROM t_order_item WHERE id IN (10, 11))"
db-types="MySQL,PostgreSQL,openGauss,Doris,Hive,Firebird" />
<sql-case id="select_with_between_subquery" value="SELECT * FROM t_order
WHERE user_id BETWEEN (SELECT user_id FROM t_order_item WHERE order_id = 10)
AND ?" db-types="MySQL, PostgreSQL,openGauss,Doris" />
<sql-case id="select_with_exists_sub_query_with_project" value="SELECT
EXISTS (SELECT 1 FROM t_order)" db-types="MySQL,PostgreSQL,openGauss" />
<sql-case id="select_with_exists_sub_query_with_project_for_doris"
value="SELECT EXISTS (SELECT 1 FROM t_order)" db-types="Doris" />
@@ -31,7 +31,7 @@
<sql-case id="select_with_projection_subquery_and_multiple_parameters"
value="SELECT t_order_federate.order_id, t_order_federate.user_id, (SELECT
CONCAT(order_id, user_id) FROM t_user_info) FROM t_order_federate" />
<sql-case id="select_with_in_subquery_condition" value="SELECT
t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE
user_id IN (SELECT * FROM t_user_info)" />
<sql-case id="select_with_between_and_subquery_condition" value="SELECT
t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE
user_id BETWEEN (SELECT user_id FROM t_user_info WHERE information = 'before')
AND (SELECT user_id FROM t_user_info WHERE information = 'after')" />
- <sql-case id="select_with_exist_subquery_condition" value="SELECT
t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE
EXISTS (SELECT * FROM t_user_info WHERE t_order_federate.user_id =
t_user_info.user_id)" db-types="MySQL,PostgreSQL,openGauss,Doris" />
+ <sql-case id="select_with_exist_subquery_condition" value="SELECT
t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE
EXISTS (SELECT * FROM t_user_info WHERE t_order_federate.user_id =
t_user_info.user_id)" db-types="MySQL,PostgreSQL,openGauss,Doris,Firebird" />
<sql-case id="select_with_not_exist_subquery_condition" value="SELECT
t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE
NOT EXISTS (SELECT * FROM t_user_info WHERE t_order_federate.user_id =
t_user_info.user_id)" db-types="MySQL,Doris" />
<sql-case id="select_with_exist_string_split_subquery" value="SELECT
ProductId, Name, Tags FROM Product WHERE EXISTS (SELECT * FROM
STRING_SPLIT(Tags, ',') WHERE value IN ('clothing', 'road'))"
db-types="SQLServer"/>
<sql-case id="select_sub_query_with_cast_function" value="SELECT
[T1_1].[BusinessEntityID] AS [BusinessEntityID], [T1_1].[rowguid] AS [rowguid],
[T1_1].[ModifiedDate] AS [ModifiedDate] FROM (SELECT [T2_1].[BusinessEntityID]
AS [BusinessEntityID], [T2_1].[rowguid] AS [rowguid], [T2_1].[ModifiedDate] AS
[ModifiedDate] FROM [AdventureWorks2022].[Person].[BusinessEntity] AS T2_1
WHERE ([T2_1].[BusinessEntityID] = CAST ((17907) AS INT))) AS T1_1"
db-types="SQLServer"/>
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 34d2822ab3e..91a85133d66 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,Doris" />
+ <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,Firebird" />
<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 de588d7901a..39cf071fc57 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
@@ -89,7 +89,7 @@
<sql-case id="select_with_mod_function" value="SELECT * FROM t_order WHERE
MOD(order_id, 1) = 1" db-types="MySQL" />
<sql-case id="select_with_date_format_function" value="SELECT * FROM
t_order WHERE DATE_FORMAT(current_date, '%Y-%m-%d') = '2019-12-18'"
db-types="MySQL" />
<sql-case id="select_with_spatial_function" value="SELECT * FROM t_order
WHERE ST_DISTANCE_SPHERE(POINT(113.358772, 23.1273723),
POINT(user_id,order_id)) != 0" db-types="MySQL" />
- <sql-case id="select_current_user" value="SELECT CURRENT_USER"
db-types="PostgreSQL,openGauss" />
+ <sql-case id="select_current_user" value="SELECT CURRENT_USER"
db-types="PostgreSQL,openGauss,Firebird" />
<sql-case id="select_with_match_against" value="SELECT * FROM t_order_item
WHERE MATCH(t_order_item.description) AGAINST (? IN NATURAL LANGUAGE MODE) AND
user_id = ?" db-types="MySQL,Doris" />
<sql-case id="select_not_match_against_doris" value="SELECT * FROM t_order
WHERE NOT MATCH(description) AGAINST('abc' IN NATURAL LANGUAGE MODE)"
db-types="Doris" />
<sql-case id="select_straight_join_doris" value="SELECT STRAIGHT_JOIN *
FROM t_order" db-types="Doris" />
@@ -183,7 +183,7 @@
<sql-case id="select_aggregate_rank" value="SELECT RANK(15500) WITHIN
GROUP (ORDER BY salary DESC) 'Rank of 15500' FROM employees;" db-types="Oracle"
/>
<sql-case id="select_rowid" value="SELECT ROWID FROM employees WHERE
ROWIDTOCHAR(ROWID) LIKE '%JAAB%' ORDER BY ROWID;" db-types="Oracle" />
<sql-case id="select_linear_regression_function" value="SELECT job_id,
employee_id ID, salary, REGR_SLOPE(SYSDATE-hire_date, salary) OVER (PARTITION
BY job_id) slope, REGR_INTERCEPT(SYSDATE-hire_date, salary) OVER (PARTITION BY
job_id) intcpt, REGR_R2(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id)
rsqr, REGR_COUNT(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id) count,
REGR_AVGX(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id) avgx,
REGR_AVGY(SYSDATE-hire_date, salar [...]
- <sql-case id="select_lpad_function" value="SELECT LPAD('Page 1',15,'*.')
'LPAD example' FROM DUAL;" db-types="Oracle" />
+ <sql-case id="select_lpad_function" value="SELECT LPAD('Page 1',15,'*.')
'LPAD example' FROM DUAL;" db-types="Oracle,Firebird" />
<sql-case id="select_to_char_function" value="SELECT TO_CHAR(ts_col,
'DD-MON-YYYY HH24:MI:SSxFF') AS ts_col FROM date_tab ORDER BY ts_col;" />
<sql-case id="select_xmlelement_xmlagg_function" value="SELECT
XMLELEMENT('Department', XMLAGG(XMLELEMENT('Employee', e.job_id||'
'||e.last_name) ORDER BY last_name)) as 'Dept_list' FROM employees e WHERE
e.department_id = 30;" db-types="Oracle" />
<sql-case id="select_xmlcast_function" value="SELECT
XMLCAST(des.COLUMN_VALUE AS VARCHAR2(256)) FROM purchaseorder;"
db-types="Oracle" />
@@ -201,7 +201,7 @@
<sql-case id="select_positional_parameter_type_cast_money" value="SELECT
$1::money" db-types="PostgreSQL,openGauss" case-types="Placeholder" />
<sql-case id="select_string_constant_type_cast" value="SELECT int4 '1',
money '2'" db-types="PostgreSQL,openGauss" />
<sql-case id="select_constant_with_nested_type_cast" value="SELECT
CAST(MONEY '1' AS VARCHAR)::CHAR(10)::VARCHAR::CHAR(4)"
db-types="PostgreSQL,openGauss" />
- <sql-case id="select_projection_with_parameter" value="SELECT 1 AS id, ?
AS status, SYSDATE AS create_time, TRUNC(SYSDATE) AS create_date FROM DUAL"
db-types="Oracle" />
+ <sql-case id="select_projection_with_parameter" value="SELECT 1 AS id, ?
AS status, SYSDATE AS create_time, TRUNC(SYSDATE) AS create_date FROM DUAL"
db-types="Oracle,Firebird" />
<sql-case id="select_with_chinese_comma" value="SELECT 1， 2，3 FROM DUAL"
db-types="Oracle" />
<sql-case id="select_with_chinese_whitespace" value="SELECT 1， 2，3 FROM
DUAL" db-types="Oracle" />
<sql-case id="select_with_auto_keyword" value="SELECT * FROM t_auto WHERE
auto = ?" db-types="MySQL"/>
@@ -402,4 +402,12 @@
<sql-case id="select_querytraceon_hint" value="SELECT * FROM Person OPTION
(QUERYTRACEON 4199, QUERYTRACEON 4137);" db-types="SQLServer"/>
<sql-case id="select_with_sample_clause" value="SELECT 1 AS C1 FROM
T_ORDER SAMPLE BLOCK (1.43302, 8) SEED(9) T_ORDER" db-types="Oracle" />
<sql-case id="select_from_function_table_doris" value="SELECT * FROM
table_func(1, 2)" db-types="Doris" />
+ <sql-case id="select_interval_firebird" value="SELECT INTERVAL 1 DAY FROM
t_order" db-types="Firebird"/>
+ <sql-case id="select_having_firebird" value="SELECT user_id, COUNT(*) AS
cnt FROM t_order GROUP BY user_id HAVING COUNT(*) > 1" db-types="Firebird"/>
+ <sql-case id="select_function_table_firebird" value="SELECT * FROM foo(1)"
db-types="Firebird"/>
+ <sql-case id="select_cast_int_firebird" value="SELECT CAST(user_id AS INT)
FROM t_order" db-types="Firebird"/>
+ <sql-case id="select_gen_id_with_identifier_firebird" value="SELECT
GEN_ID(seq, 1) FROM t_order" db-types="Firebird"/>
+ <sql-case id="select_gen_id_with_parameters_firebird" value="SELECT
GEN_ID(?, ?) FROM t_order" db-types="Firebird" case-types="Placeholder"/>
+ <sql-case id="select_variable_default_firebird" value="SELECT DEFAULT FROM
t_order" db-types="Firebird"/>
+ <sql-case id="select_parenthesized_column_firebird" value="SELECT
(user_id) FROM t_order" db-types="Firebird"/>
</sql-cases>
