You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by zh...@apache.org on 2022/11/18 10:35:38 UTC
[shardingsphere] branch master updated: Add nulls-order-type sql parser test case (#22261)
This is an automated email from the ASF dual-hosted git repository.
zhaojinchao 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 3e5dc7cfbee Add nulls-order-type sql parser test case (#22261)
3e5dc7cfbee is described below
commit 3e5dc7cfbee1b83ba3a90d8f074ccfa473210994
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Fri Nov 18 18:35:26 2022 +0800
Add nulls-order-type sql parser test case (#22261)
---
.../statement/impl/OracleStatementSQLVisitor.java | 2 +-
.../asserts/segment/orderby/OrderByItemAssert.java | 8 +--
.../impl/orderby/item/ExpectedOrderByItem.java | 3 +
test/parser/src/main/resources/case/dml/delete.xml | 4 +-
.../main/resources/case/dml/select-expression.xml | 16 ++---
.../main/resources/case/dml/select-group-by.xml | 32 ++++-----
.../src/main/resources/case/dml/select-join.xml | 12 ++--
.../main/resources/case/dml/select-order-by.xml | 18 +++--
.../dml/select-pagination-group-by-order-by.xml | 13 ++--
.../main/resources/case/dml/select-pagination.xml | 11 ++-
.../src/main/resources/case/dml/select-union.xml | 16 ++---
.../src/main/resources/case/dml/select-with.xml | 16 ++---
test/parser/src/main/resources/case/dml/select.xml | 82 +++++++++++++---------
.../sql/supported/dml/select-distinct.xml | 17 ++---
.../sql/supported/dml/select-expression.xml | 4 +-
.../sql/supported/dml/select-group-by.xml | 30 ++++----
.../sql/supported/dml/select-order-by.xml | 14 ++--
.../sql/supported/dml/select-pagination.xml | 2 +-
.../sql/supported/dml/select-relation.xml | 4 +-
.../resources/sql/supported/dml/select-union.xml | 4 +-
.../resources/sql/supported/dml/select-with.xml | 4 +-
.../main/resources/sql/supported/dml/select.xml | 26 +++----
22 files changed, 175 insertions(+), 163 deletions(-)
diff --git a/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java b/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
index 11028049376..ff3c582223a 100644
--- a/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
+++ b/sql-parser/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
@@ -663,7 +663,7 @@ public abstract class OracleStatementSQLVisitor extends OracleStatementBaseVisit
}
private NullsOrderType generateNullsOrderType(final OrderByItemContext ctx, final OrderDirection orderDirection) {
- if (null == ctx) {
+ if (null == ctx.FIRST() && null == ctx.LAST()) {
return OrderDirection.ASC.equals(orderDirection) ? NullsOrderType.LAST : NullsOrderType.FIRST;
}
return null == ctx.FIRST() ? NullsOrderType.LAST : NullsOrderType.FIRST;
diff --git a/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/asserts/segment/orderby/OrderByItemAssert.java b/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/asserts/segment/orderby/OrderByItemAssert.java
index 09f9476c2e2..36687997ec1 100644
--- a/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/asserts/segment/orderby/OrderByItemAssert.java
+++ b/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/asserts/segment/orderby/OrderByItemAssert.java
@@ -26,8 +26,8 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.Or
import org.apache.shardingsphere.test.sql.parser.internal.asserts.SQLCaseAssertContext;
import org.apache.shardingsphere.test.sql.parser.internal.asserts.segment.SQLSegmentAssert;
import org.apache.shardingsphere.test.sql.parser.internal.asserts.segment.expression.ExpressionAssert;
-import org.apache.shardingsphere.test.sql.parser.internal.asserts.segment.owner.OwnerAssert;
import org.apache.shardingsphere.test.sql.parser.internal.asserts.segment.identifier.IdentifierValueAssert;
+import org.apache.shardingsphere.test.sql.parser.internal.asserts.segment.owner.OwnerAssert;
import org.apache.shardingsphere.test.sql.parser.internal.jaxb.cases.domain.segment.impl.orderby.ExpectedOrderByClause;
import org.apache.shardingsphere.test.sql.parser.internal.jaxb.cases.domain.segment.impl.orderby.item.ExpectedOrderByItem;
import org.apache.shardingsphere.test.sql.parser.internal.jaxb.cases.domain.segment.impl.orderby.item.impl.ExpectedColumnOrderByItem;
@@ -37,8 +37,8 @@ import org.apache.shardingsphere.test.sql.parser.internal.jaxb.cases.domain.segm
import java.util.Collection;
import static org.hamcrest.CoreMatchers.is;
-import static org.junit.Assert.assertFalse;
import static org.hamcrest.MatcherAssert.assertThat;
+import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
/**
@@ -85,8 +85,8 @@ public final class OrderByItemAssert {
}
private static void assertOrderInfo(final SQLCaseAssertContext assertContext, final OrderByItemSegment actual, final ExpectedOrderByItem expected, final String type) {
- assertThat(assertContext.getText(String.format("%s item order direction assertion error: ", type)),
- null != actual.getOrderDirection() ? actual.getOrderDirection().name() : actual.getNullsOrderType().name(), is(expected.getOrderDirection()));
+ assertThat(assertContext.getText(String.format("%s item order direction assertion error: ", type)), actual.getOrderDirection().name(), is(expected.getOrderDirection()));
+ assertThat(assertContext.getText(String.format("%s item nulls order type assertion error: ", type)), actual.getNullsOrderType().name(), is(expected.getNullsOrderType()));
}
private static void assertColumnOrderByItem(final SQLCaseAssertContext assertContext,
diff --git a/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/jaxb/cases/domain/segment/impl/orderby/item/ExpectedOrderByItem.java b/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/jaxb/cases/domain/segment/impl/orderby/item/ExpectedOrderByItem.java
index 4815c1ce0f7..abac8b88880 100644
--- a/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/jaxb/cases/domain/segment/impl/orderby/item/ExpectedOrderByItem.java
+++ b/test/parser/src/main/java/org/apache/shardingsphere/test/sql/parser/internal/jaxb/cases/domain/segment/impl/orderby/item/ExpectedOrderByItem.java
@@ -32,4 +32,7 @@ public abstract class ExpectedOrderByItem extends AbstractExpectedDelimiterSQLSe
@XmlAttribute(name = "order-direction")
private String orderDirection = "ASC";
+
+ @XmlAttribute(name = "nulls-order-type")
+ private String nullsOrderType = "FIRST";
}
diff --git a/test/parser/src/main/resources/case/dml/delete.xml b/test/parser/src/main/resources/case/dml/delete.xml
index ee168450cf2..844c9e3793f 100644
--- a/test/parser/src/main/resources/case/dml/delete.xml
+++ b/test/parser/src/main/resources/case/dml/delete.xml
@@ -143,8 +143,8 @@
<aggregation-projection type="MAX" inner-expression="(effective_from_date)" start-index="125" stop-index="148" />
</projections>
<group-by>
- <column-item name="product_id" start-index="186" stop-index="195" />
- <column-item name="currency_code" start-index="198" stop-index="210" />
+ <column-item name="product_id" start-index="186" stop-index="195" nulls-order-type="LAST" />
+ <column-item name="currency_code" start-index="198" stop-index="210" nulls-order-type="LAST" />
</group-by>
</select>
</subquery>
diff --git a/test/parser/src/main/resources/case/dml/select-expression.xml b/test/parser/src/main/resources/case/dml/select-expression.xml
index 59d9bf7a538..c0bc3d2abbc 100644
--- a/test/parser/src/main/resources/case/dml/select-expression.xml
+++ b/test/parser/src/main/resources/case/dml/select-expression.xml
@@ -41,7 +41,7 @@
<expression-projection text="o.order_id + 1 * 2" alias="exp" start-index="7" stop-index="31"/>
</projections>
<order-by>
- <column-item name="order_id" start-index="60" stop-index="69">
+ <column-item name="order_id" start-index="60" stop-index="69" nulls-order-type="LAST">
<owner name="o" start-index="60" stop-index="60"/>
</column-item>
</order-by>
@@ -65,7 +65,7 @@
</expression-projection>
</projections>
<order-by>
- <expression-item expression="DATE(i.creation_date)" order-direction="DESC" start-index="80" stop-index="100"/>
+ <expression-item expression="DATE(i.creation_date)" order-direction="DESC" nulls-order-type="LAST" start-index="80" stop-index="100"/>
</order-by>
</select>
@@ -1760,7 +1760,7 @@
</expr>
</where>
<order-by>
- <column-item name="last_name" start-index="127" stop-index="135" />
+ <column-item name="last_name" start-index="127" stop-index="135" nulls-order-type="LAST" />
</order-by>
</select>
@@ -1808,8 +1808,8 @@
</expr>
</where>
<order-by>
- <column-item name="last_name" start-index="144" stop-index="152" />
- <column-item name="department_id" start-index="155" stop-index="167" />
+ <column-item name="last_name" start-index="144" stop-index="152" nulls-order-type="LAST" />
+ <column-item name="department_id" start-index="155" stop-index="167" nulls-order-type="LAST" />
</order-by>
</select>
@@ -1856,7 +1856,7 @@
</expr>
</where>
<order-by>
- <column-item name="last_name" start-index="125" stop-index="133" />
+ <column-item name="last_name" start-index="125" stop-index="133" nulls-order-type="LAST" />
</order-by>
</select>
@@ -1994,7 +1994,7 @@
</expr>
</where>
<group-by>
- <expression-item expression="ABS(1)" start-index="53" stop-index="58">
+ <expression-item expression="ABS(1)" start-index="53" stop-index="58" nulls-order-type="LAST">
<expr>
<function function-name="ABS" text="ABS(1)" start-index="53" stop-index="58">
<parameter>
@@ -2005,7 +2005,7 @@
</expression-item>
</group-by>
<order-by>
- <expression-item expression="ABS(1)" start-index="69" stop-index="74">
+ <expression-item expression="ABS(1)" start-index="69" stop-index="74" nulls-order-type="LAST">
<expr>
<function function-name="ABS" text="ABS(1)" start-index="69" stop-index="74">
<parameter>
diff --git a/test/parser/src/main/resources/case/dml/select-group-by.xml b/test/parser/src/main/resources/case/dml/select-group-by.xml
index 9e8125f75db..611653db1da 100644
--- a/test/parser/src/main/resources/case/dml/select-group-by.xml
+++ b/test/parser/src/main/resources/case/dml/select-group-by.xml
@@ -109,7 +109,7 @@
<column-item name="user_id" start-index="66" stop-index="72" />
</group-by>
<order-by>
- <column-item name="orders_sum" order-direction="DESC" start-index="83" stop-index="92" />
+ <column-item name="orders_sum" order-direction="DESC" start-index="83" stop-index="92" nulls-order-type="LAST" />
</order-by>
</select>
@@ -263,12 +263,12 @@
</column-projection>
</projections>
<group-by>
- <column-item name="user_id" start-index="45" stop-index="53">
+ <column-item name="user_id" start-index="45" stop-index="53" nulls-order-type="LAST">
<owner name="o" start-index="45" stop-index="45" />
</column-item>
</group-by>
<order-by>
- <column-item name="user_id" start-index="64" stop-index="72">
+ <column-item name="user_id" start-index="64" stop-index="72" nulls-order-type="LAST">
<owner name="o" start-index="64" stop-index="64" />
</column-item>
</order-by>
@@ -410,7 +410,7 @@
<column-projection name="user_id" start-index="40" stop-index="46" />
</projections>
<group-by>
- <column-item name="user_id" start-index="70" stop-index="76" />
+ <column-item name="user_id" start-index="70" stop-index="76" nulls-order-type="LAST" />
</group-by>
<having start-index="78" stop-index="100">
<expr>
@@ -463,12 +463,12 @@
</expr>
</where>
<group-by>
- <column-item name="department_name" start-index="300" stop-index="314" />
- <column-item name="job_id" start-index="317" stop-index="322" />
+ <column-item name="department_name" start-index="300" stop-index="314" nulls-order-type="LAST" />
+ <column-item name="job_id" start-index="317" stop-index="322" nulls-order-type="LAST" />
</group-by>
<order-by>
- <column-item name="department_name" start-index="334" stop-index="348" />
- <column-item name="job_id" start-index="351" stop-index="356" />
+ <column-item name="department_name" start-index="334" stop-index="348" nulls-order-type="LAST" />
+ <column-item name="job_id" start-index="351" stop-index="356" nulls-order-type="LAST" />
</order-by>
</select>
@@ -530,9 +530,9 @@
</expr>
</where>
<group-by>
- <expression-item expression="(channel_desc,calendar_month_desc,co.country_id)" start-index="218" stop-index="265" />
- <expression-item expression="(channel_desc,co.country_id)" start-index="268" stop-index="295" />
- <expression-item expression="(calendar_month_desc,co.country_id)" start-index="298" stop-index="332" />
+ <expression-item expression="(channel_desc,calendar_month_desc,co.country_id)" start-index="218" stop-index="265" nulls-order-type="LAST" />
+ <expression-item expression="(channel_desc,co.country_id)" start-index="268" stop-index="295" nulls-order-type="LAST" />
+ <expression-item expression="(calendar_month_desc,co.country_id)" start-index="298" stop-index="332" nulls-order-type="LAST" />
</group-by>
</select>
@@ -546,7 +546,7 @@
<aggregation-projection type="MAX" inner-expression="(salary)" start-index="35" stop-index="45" />
</projections>
<group-by>
- <column-item name="department_id" start-index="71" stop-index="83" />
+ <column-item name="department_id" start-index="71" stop-index="83" nulls-order-type="LAST" />
</group-by>
<having start-index="85" stop-index="109">
<expr>
@@ -562,7 +562,7 @@
</expr>
</having>
<order-by>
- <column-item name="department_id" start-index="120" stop-index="132" />
+ <column-item name="department_id" start-index="120" stop-index="132" nulls-order-type="LAST" />
</order-by>
</select>
@@ -575,8 +575,8 @@
<column-projection name="manager_id" start-index="22" stop-index="31" />
</projections>
<group-by>
- <column-item name="department_id" start-index="57" stop-index="69" />
- <column-item name="manager_id" start-index="72" stop-index="81" />
+ <column-item name="department_id" start-index="57" stop-index="69" nulls-order-type="LAST" />
+ <column-item name="manager_id" start-index="72" stop-index="81" nulls-order-type="LAST" />
</group-by>
<having start-index="83" stop-index="219">
<expr>
@@ -619,7 +619,7 @@
</expr>
</having>
<order-by>
- <column-item name="department_id" start-index="230" stop-index="242" />
+ <column-item name="department_id" start-index="230" stop-index="242" nulls-order-type="LAST" />
</order-by>
</select>
diff --git a/test/parser/src/main/resources/case/dml/select-join.xml b/test/parser/src/main/resources/case/dml/select-join.xml
index 983b725a627..13cfef09dd8 100644
--- a/test/parser/src/main/resources/case/dml/select-join.xml
+++ b/test/parser/src/main/resources/case/dml/select-join.xml
@@ -276,10 +276,10 @@
</column-projection>
</projections>
<order-by>
- <column-item name="department_id" start-index="172" stop-index="186">
+ <column-item name="department_id" start-index="172" stop-index="186" nulls-order-type="LAST">
<owner name="d" start-index="172" stop-index="172" />
</column-item>
- <column-item name="last_name" start-index="189" stop-index="199">
+ <column-item name="last_name" start-index="189" stop-index="199" nulls-order-type="LAST">
<owner name="e" start-index="189" stop-index="189" />
</column-item>
</order-by>
@@ -304,8 +304,8 @@
</column-projection>
</projections>
<order-by>
- <column-item name="department_id" start-index="127" stop-index="139"/>
- <column-item name="last_name" start-index="142" stop-index="152">
+ <column-item name="department_id" start-index="127" stop-index="139" nulls-order-type="LAST" />
+ <column-item name="last_name" start-index="142" stop-index="152" nulls-order-type="LAST">
<owner name="e" start-index="142" stop-index="142" />
</column-item>
</order-by>
@@ -387,10 +387,10 @@
</expr>
</where>
<order-by>
- <column-item name="department_name" start-index="238" stop-index="254">
+ <column-item name="department_name" start-index="238" stop-index="254" nulls-order-type="LAST">
<owner name="d" start-index="238" stop-index="238" />
</column-item>
- <column-item name="employee_id" start-index="257" stop-index="269">
+ <column-item name="employee_id" start-index="257" stop-index="269" nulls-order-type="LAST">
<owner name="v" start-index="257" stop-index="257" />
</column-item>
</order-by>
diff --git a/test/parser/src/main/resources/case/dml/select-order-by.xml b/test/parser/src/main/resources/case/dml/select-order-by.xml
index c21a6b28eb1..f5a2b84e8e8 100644
--- a/test/parser/src/main/resources/case/dml/select-order-by.xml
+++ b/test/parser/src/main/resources/case/dml/select-order-by.xml
@@ -28,7 +28,7 @@
<column-item name="order_id" start-index="33" stop-index="42">
<owner name="o" start-index="33" stop-index="33" />
</column-item>
- <index-item index="2" order-direction="DESC" start-index="45" stop-index="45" />
+ <index-item index="2" order-direction="DESC" nulls-order-type="LAST" start-index="45" stop-index="45" />
</order-by>
</select>
@@ -84,7 +84,7 @@
</expr>
</where>
<order-by>
- <column-item name="order_id" order-direction="DESC" start-index="103" stop-index="112">
+ <column-item name="order_id" order-direction="DESC" nulls-order-type="LAST" start-index="103" stop-index="112">
<owner name="o" start-index="103" stop-index="103" />
</column-item>
<index-item index="1" start-index="120" stop-index="120" />
@@ -159,10 +159,10 @@
</expr>
</where>
<order-by>
- <column-item name="creation_date" order-direction="DESC" start-index="103" stop-index="117">
+ <column-item name="creation_date" order-direction="DESC" nulls-order-type="LAST" start-index="103" stop-index="117">
<owner name="i" start-index="103" stop-index="103" />
</column-item>
- <column-item name="order_id" order-direction="DESC" start-index="125" stop-index="134">
+ <column-item name="order_id" order-direction="DESC" nulls-order-type="LAST" start-index="125" stop-index="134">
<owner name="o" start-index="125" stop-index="125" />
</column-item>
<column-item name="item_id" start-index="142" stop-index="150">
@@ -171,7 +171,6 @@
</order-by>
</select>
- <!-- //TODO add order-by-null-type -->
<select sql-case-id="select_order_by_for_nulls_first">
<from>
<simple-table name="t_order" alias="o" start-index="40" stop-index="48" />
@@ -182,13 +181,12 @@
</column-projection>
</projections>
<order-by>
- <column-item name="order_id" start-index="59" stop-index="68">
+ <column-item name="order_id" start-index="59" stop-index="68" order-direction="ASC" nulls-order-type="FIRST">
<owner name="o" start-index="59" stop-index="59" />
</column-item>
</order-by>
</select>
- <!-- //TODO add order-by-null-type -->
<select sql-case-id="select_order_by_for_nulls_last">
<from>
<simple-table name="t_order" alias="o" start-index="40" stop-index="48" />
@@ -199,7 +197,7 @@
</column-projection>
</projections>
<order-by>
- <column-item name="order_id" start-index="59" stop-index="68">
+ <column-item name="order_id" start-index="59" stop-index="68" order-direction="ASC" nulls-order-type="LAST">
<owner name="o" start-index="59" stop-index="59" />
</column-item>
</order-by>
@@ -217,7 +215,7 @@
<column-projection name="order_id" start-index="10" stop-index="17" />
</projections>
<order-by>
- <column-item name="order_id" start-index="48" stop-index="57">
+ <column-item name="order_id" start-index="48" stop-index="57" nulls-order-type="LAST">
<owner name="o" start-index="48" stop-index="48" />
</column-item>
</order-by>
@@ -257,7 +255,7 @@
<column-projection name="last_name" start-index="15" stop-index="23" />
</projections>
<order-by>
- <column-item name="salary" order-direction="ASC" start-index="49" stop-index="54" />
+ <column-item name="salary" order-direction="ASC" start-index="49" stop-index="54" nulls-order-type="LAST" />
<column-item name="last_name" order-direction="DESC" start-index="61" stop-index="69" />
</order-by>
</select>
diff --git a/test/parser/src/main/resources/case/dml/select-pagination-group-by-order-by.xml b/test/parser/src/main/resources/case/dml/select-pagination-group-by-order-by.xml
index 17018fea7a0..4566e2a2303 100644
--- a/test/parser/src/main/resources/case/dml/select-pagination-group-by-order-by.xml
+++ b/test/parser/src/main/resources/case/dml/select-pagination-group-by-order-by.xml
@@ -120,7 +120,7 @@
</column-item>
</group-by>
<order-by>
- <column-item name="item_id" order-direction="DESC" start-index="190" stop-index="198" literal-start-index="191" literal-stop-index="199">
+ <column-item name="item_id" order-direction="DESC" nulls-order-type="LAST" start-index="190" stop-index="198" literal-start-index="191" literal-stop-index="199">
<owner name="i" start-index="190" stop-index="190" literal-start-index="191" literal-stop-index="191"/>
</column-item>
</order-by>
@@ -234,7 +234,7 @@
</column-item>
</group-by>
<order-by>
- <column-item name="item_id" order-direction="DESC" start-index="190" stop-index="198" literal-start-index="191" literal-stop-index="199">
+ <column-item name="item_id" order-direction="DESC" nulls-order-type="LAST" start-index="190" stop-index="198" literal-start-index="191" literal-stop-index="199">
<owner name="i" start-index="190" stop-index="190" literal-start-index="191" literal-stop-index="191"/>
</column-item>
</order-by>
@@ -1523,7 +1523,7 @@
</column-item>
</group-by>
<order-by>
- <column-item start-index="344" stop-index="352" name="item_id" order-direction="DESC">
+ <column-item start-index="344" stop-index="352" name="item_id" order-direction="DESC" nulls-order-type="LAST">
<owner start-index="344" stop-index="344" name="i"/>
</column-item>
</order-by>
@@ -1694,7 +1694,7 @@
</column-item>
</group-by>
<order-by>
- <column-item start-index="344" stop-index="352" name="item_id" order-direction="DESC">
+ <column-item start-index="344" stop-index="352" name="item_id" order-direction="DESC" nulls-order-type="LAST">
<owner start-index="344" stop-index="344" name="i"/>
</column-item>
</order-by>
@@ -1860,7 +1860,7 @@
</expr>
</where>
<group-by>
- <column-item start-index="325" stop-index="333" name="item_id">
+ <column-item start-index="325" stop-index="333" name="item_id" nulls-order-type="LAST">
<owner start-index="325" stop-index="325" name="i"/>
</column-item>
</group-by>
@@ -2031,7 +2031,7 @@
</expr>
</where>
<group-by>
- <column-item start-index="325" stop-index="333" name="user_id">
+ <column-item start-index="325" stop-index="333" name="user_id" nulls-order-type="LAST">
<owner start-index="325" stop-index="325" name="i"/>
</column-item>
</group-by>
@@ -2079,5 +2079,4 @@
</expr>
</where>
</select>
-
</sql-parser-test-cases>
diff --git a/test/parser/src/main/resources/case/dml/select-pagination.xml b/test/parser/src/main/resources/case/dml/select-pagination.xml
index 24b300722b6..296c5308f2d 100644
--- a/test/parser/src/main/resources/case/dml/select-pagination.xml
+++ b/test/parser/src/main/resources/case/dml/select-pagination.xml
@@ -171,7 +171,6 @@
</on-condition>
</join-table>
</from>
-
<projections start-index="7" stop-index="9">
<shorthand-projection start-index="7" stop-index="9">
<owner name="i" start-index="7" stop-index="7" />
@@ -225,7 +224,7 @@
</expr>
</where>
<order-by>
- <column-item name="item_id" order-direction="DESC" start-index="165" stop-index="173" literal-start-index="166" literal-stop-index="174">
+ <column-item name="item_id" order-direction="DESC" nulls-order-type="FIRST" start-index="165" stop-index="173" literal-start-index="166" literal-stop-index="174">
<owner name="i" start-index="165" stop-index="165" literal-start-index="166" literal-stop-index="166" />
</column-item>
</order-by>
@@ -333,7 +332,7 @@
</expr>
</where>
<order-by>
- <column-item name="item_id" order-direction="DESC" start-index="173" stop-index="181" literal-start-index="174" literal-stop-index="182">
+ <column-item name="item_id" order-direction="DESC" nulls-order-type="LAST" start-index="173" stop-index="181" literal-start-index="174" literal-stop-index="182">
<owner name="i" start-index="173" stop-index="173" literal-start-index="174" literal-stop-index="174" />
</column-item>
</order-by>
@@ -442,7 +441,7 @@
</expr>
</where>
<order-by>
- <column-item name="item_id" order-direction="DESC" start-index="173" stop-index="181" literal-start-index="174" literal-stop-index="182">
+ <column-item name="item_id" order-direction="DESC" nulls-order-type="LAST" start-index="173" stop-index="181" literal-start-index="174" literal-stop-index="182">
<owner name="i" start-index="173" stop-index="173" literal-start-index="174" literal-stop-index="174" />
</column-item>
</order-by>
@@ -1975,7 +1974,7 @@
</expr>
</where>
<order-by>
- <column-item name="order_id" start-index="49" stop-index="56" literal-start-index="50" literal-stop-index="57" />
+ <column-item name="order_id" start-index="49" stop-index="56" literal-start-index="50" literal-stop-index="57" nulls-order-type="LAST" />
</order-by>
<row-count value="20" parameter-index="0" />
</select>
@@ -1988,7 +1987,7 @@
<shorthand-projection start-index="7" stop-index="7" />
</projections>
<order-by>
- <column-item name="order_id" start-index="31" stop-index="38" />
+ <column-item name="order_id" start-index="31" stop-index="38" nulls-order-type="LAST" />
</order-by>
<row-count value="5" start-index="52" stop-index="52"/>
</select>
diff --git a/test/parser/src/main/resources/case/dml/select-union.xml b/test/parser/src/main/resources/case/dml/select-union.xml
index 04bcdc2dcec..5bffa680f39 100644
--- a/test/parser/src/main/resources/case/dml/select-union.xml
+++ b/test/parser/src/main/resources/case/dml/select-union.xml
@@ -73,7 +73,7 @@
</select>
</combine>
<order-by>
- <column-item name="id" start-index="61" stop-index="62" />
+ <column-item name="id" start-index="61" stop-index="62" nulls-order-type="LAST" />
</order-by>
</select>
@@ -95,7 +95,7 @@
</select>
</combine>
<order-by>
- <column-item name="id" start-index="61" stop-index="62" />
+ <column-item name="id" start-index="61" stop-index="62" nulls-order-type="LAST" />
</order-by>
<limit start-index="64" stop-index="73">
<offset value="1" start-index="70" stop-index="70" />
@@ -160,7 +160,7 @@
</select>
</combine>
<order-by>
- <column-item name="id" start-index="92" stop-index="93" />
+ <column-item name="id" start-index="92" stop-index="93" nulls-order-type="LAST" />
</order-by>
</select>
@@ -192,7 +192,7 @@
</select>
</combine>
<order-by>
- <column-item name="id" start-index="92" stop-index="93" />
+ <column-item name="id" start-index="92" stop-index="93" nulls-order-type="LAST" />
</order-by>
<limit start-index="95" stop-index="104">
<offset value="1" start-index="101" stop-index="101" />
@@ -257,7 +257,7 @@
</select>
</combine>
<order-by>
- <column-item name="id" start-index="94" stop-index="95" />
+ <column-item name="id" start-index="94" stop-index="95" nulls-order-type="LAST" />
</order-by>
</select>
@@ -289,7 +289,7 @@
</select>
</combine>
<order-by>
- <column-item name="id" start-index="94" stop-index="95" />
+ <column-item name="id" start-index="94" stop-index="95" nulls-order-type="LAST" />
</order-by>
<limit start-index="97" stop-index="106">
<offset value="1" start-index="103" stop-index="103" />
@@ -334,7 +334,7 @@
</select>
</combine>
<order-by>
- <column-item name="id" start-index="57" stop-index="58" />
+ <column-item name="id" start-index="57" stop-index="58" nulls-order-type="LAST" />
</order-by>
</select>
@@ -356,7 +356,7 @@
</select>
</combine>
<order-by>
- <column-item name="id" start-index="57" stop-index="58" />
+ <column-item name="id" start-index="57" stop-index="58" nulls-order-type="LAST" />
</order-by>
<limit start-index="60" stop-index="69">
<offset value="1" start-index="66" stop-index="66" />
diff --git a/test/parser/src/main/resources/case/dml/select-with.xml b/test/parser/src/main/resources/case/dml/select-with.xml
index 7ad930fbba1..a642369cde4 100644
--- a/test/parser/src/main/resources/case/dml/select-with.xml
+++ b/test/parser/src/main/resources/case/dml/select-with.xml
@@ -195,7 +195,7 @@
<column-projection name="job_id" start-index="264" stop-index="269" />
</projections>
<order-by>
- <column-item name="order1" start-index="295" stop-index="300" />
+ <column-item name="order1" start-index="295" stop-index="300" nulls-order-type="LAST" />
</order-by>
</select>
@@ -247,7 +247,7 @@
<column-projection name="is_cycle" start-index="367" stop-index="374" />
</projections>
<order-by>
- <column-item name="order1" start-index="403" stop-index="408" />
+ <column-item name="order1" start-index="403" stop-index="408" nulls-order-type="LAST" />
</order-by>
</select>
@@ -284,10 +284,10 @@
<column-projection name="salary" start-index="239" stop-index="244" />
</projections>
<group-by>
- <column-item name="emp_last" start-index="270" stop-index="277" />
- <column-item name="eid" start-index="280" stop-index="282" />
- <column-item name="mgr_id" start-index="285" stop-index="290" />
- <column-item name="salary" start-index="293" stop-index="298" />
+ <column-item name="emp_last" start-index="270" stop-index="277" nulls-order-type="LAST" />
+ <column-item name="eid" start-index="280" stop-index="282" nulls-order-type="LAST" />
+ <column-item name="mgr_id" start-index="285" stop-index="290" nulls-order-type="LAST" />
+ <column-item name="salary" start-index="293" stop-index="298" nulls-order-type="LAST" />
</group-by>
<having start-index="300" stop-index="320">
<expr>
@@ -303,8 +303,8 @@
</expr>
</having>
<order-by>
- <column-item name="mgr_id" start-index="331" stop-index="336" />
- <column-item name="emp_last" start-index="351" stop-index="358" />
+ <column-item name="mgr_id" start-index="331" stop-index="336" nulls-order-type="FIRST" />
+ <column-item name="emp_last" start-index="351" stop-index="358" nulls-order-type="LAST" />
</order-by>
</select>
diff --git a/test/parser/src/main/resources/case/dml/select.xml b/test/parser/src/main/resources/case/dml/select.xml
index 9fe0d56528b..b63c57928e2 100644
--- a/test/parser/src/main/resources/case/dml/select.xml
+++ b/test/parser/src/main/resources/case/dml/select.xml
@@ -1711,7 +1711,7 @@
</expr>
</where>
<order-by>
- <column-item name="item_id" start-index="221" stop-index="229" literal-start-index="227" literal-stop-index="235">
+ <column-item name="item_id" start-index="221" stop-index="229" literal-start-index="227" literal-stop-index="235" nulls-order-type="LAST">
<owner name="i" start-index="221" stop-index="221" literal-start-index="227" literal-stop-index="227" />
</column-item>
</order-by>
@@ -2195,7 +2195,7 @@
</expr>
</where>
<order-by>
- <column-item name="item_id" start-delimiter=""" end-delimiter=""" start-index="59" stop-index="67" />
+ <column-item name="item_id" start-delimiter=""" end-delimiter=""" start-index="59" stop-index="67" nulls-order-type="LAST" />
</order-by>
</select>
@@ -2563,6 +2563,18 @@
</order-by>
</select>
+ <select sql-case-id="select_distinct_function_nulls_last">
+ <from>
+ <simple-table name="t_order_item" start-index="30" stop-index="41" />
+ </from>
+ <projections distinct-row="true" start-index="15" stop-index="23">
+ <column-projection start-index="16" stop-index="22" name="item_id"/>
+ </projections>
+ <order-by>
+ <column-item name="item_id" start-index="52" stop-index="58" nulls-order-type="LAST" />
+ </order-by>
+ </select>
+
<select sql-case-id="select_distinct_with_count_calculation" >
<from>
<simple-table name="t_order" start-index="49" stop-index="55" />
@@ -3472,7 +3484,7 @@
<simple-table name="employees" start-index="185" stop-index="193" />
</from>
<group-by>
- <column-item name="department_id" start-index="204" stop-index="216" />
+ <column-item name="department_id" start-index="204" stop-index="216" nulls-order-type="LAST" />
</group-by>
</select>
</subquery>
@@ -3503,7 +3515,7 @@
<expression-projection text="a.sal_sum/b.total_sal" alias="%_Salary" start-index="76" stop-index="107" />
</projections>
<order-by>
- <column-item name="department_id" start-index="301" stop-index="315">
+ <column-item name="department_id" start-index="301" stop-index="315" nulls-order-type="LAST">
<owner name="a" start-index="301" stop-index="301"/>
</column-item>
</order-by>
@@ -3532,9 +3544,9 @@
</expr>
</where>
<order-by>
- <column-item name="cust_id" start-index="84" stop-index="90" />
- <column-item name="time_id" start-index="93" stop-index="99" />
- <column-item name="channel_id" start-index="102" stop-index="111" />
+ <column-item name="cust_id" start-index="84" stop-index="90" nulls-order-type="LAST" />
+ <column-item name="time_id" start-index="93" stop-index="99" nulls-order-type="LAST" />
+ <column-item name="channel_id" start-index="102" stop-index="111" nulls-order-type="LAST" />
</order-by>
</select>
@@ -3575,8 +3587,8 @@
</expr>
</where>
<order-by>
- <column-item name="last_name" start-index="164" stop-index="172" />
- <column-item name="job_id" start-index="175" stop-index="180" />
+ <column-item name="last_name" start-index="164" stop-index="172" nulls-order-type="LAST" />
+ <column-item name="job_id" start-index="175" stop-index="180" nulls-order-type="LAST" />
</order-by>
</select>
@@ -3643,7 +3655,7 @@
<column-projection name="manager_id" start-index="31" stop-index="40"/>
</projections>
<order-by>
- <column-item name="last_name" start-index="102" stop-index="110" />
+ <column-item name="last_name" start-index="102" stop-index="110" nulls-order-type="LAST" />
</order-by>
</select>
@@ -3750,9 +3762,9 @@
<cell-assignment-column name="s" start-index="265" stop-index="265"/>
</model>
<order-by>
- <column-item name="country" start-index="329" stop-index="335" />
- <column-item name="prod" start-index="338" stop-index="341" />
- <column-item name="year" start-index="344" stop-index="347" />
+ <column-item name="country" start-index="329" stop-index="335" nulls-order-type="LAST" />
+ <column-item name="prod" start-index="338" stop-index="341" nulls-order-type="LAST" />
+ <column-item name="year" start-index="344" stop-index="347" nulls-order-type="LAST" />
</order-by>
</select>
@@ -3770,8 +3782,8 @@
<aggregation-projection type="SUM" alias="sale" inner-expression="(sale)" start-index="61" stop-index="69"/>
</projections>
<group-by>
- <column-item name="country" start-index="105" stop-index="111" />
- <column-item name="year" start-index="114" stop-index="117" />
+ <column-item name="country" start-index="105" stop-index="111" nulls-order-type="LAST" />
+ <column-item name="year" start-index="114" stop-index="117" nulls-order-type="LAST" />
</group-by>
</select>
</subquery>
@@ -3787,8 +3799,8 @@
<cell-assignment-column name="csum" start-index="186" stop-index="189"/>
</model>
<order-by>
- <column-item name="country" start-index="290" stop-index="296" />
- <column-item name="year" start-index="299" stop-index="302" />
+ <column-item name="country" start-index="290" stop-index="296" nulls-order-type="LAST" />
+ <column-item name="year" start-index="299" stop-index="302" nulls-order-type="LAST" />
</order-by>
</select>
@@ -3820,9 +3832,9 @@
<cell-assignment-column name="prod" start-index="225" stop-index="228"/>
</model>
<order-by>
- <column-item name="country" start-index="313" stop-index="319" />
- <column-item name="prod" start-index="322" stop-index="325" />
- <column-item name="year" start-index="328" stop-index="331" />
+ <column-item name="country" start-index="313" stop-index="319" nulls-order-type="LAST" />
+ <column-item name="prod" start-index="322" stop-index="325" nulls-order-type="LAST" />
+ <column-item name="year" start-index="328" stop-index="331" nulls-order-type="LAST" />
</order-by>
</select>
@@ -3857,8 +3869,8 @@
</expr>
</where>
<group-by>
- <column-item name="country" start-index="130" stop-index="136" />
- <column-item name="year" start-index="139" stop-index="142" />
+ <column-item name="country" start-index="130" stop-index="136" nulls-order-type="LAST" />
+ <column-item name="year" start-index="139" stop-index="142" nulls-order-type="LAST" />
</group-by>
<model start-index="144" stop-index="713">
<reference-model-select>
@@ -3921,7 +3933,7 @@
</order-by>
</model>
<order-by>
- <column-item name="year" start-index="207" stop-index="210" />
+ <column-item name="year" start-index="207" stop-index="210" nulls-order-type="LAST" />
</order-by>
</select>
@@ -3965,9 +3977,9 @@
</cell-assignment-select>
</model>
<order-by>
- <column-item name="country" start-index="294" stop-index="300" />
- <column-item name="year" start-index="303" stop-index="306" />
- <column-item name="product" start-index="309" stop-index="315" />
+ <column-item name="country" start-index="294" stop-index="300" nulls-order-type="LAST" />
+ <column-item name="year" start-index="303" stop-index="306" nulls-order-type="LAST" />
+ <column-item name="product" start-index="309" stop-index="315" nulls-order-type="LAST" />
</order-by>
</select>
@@ -4470,9 +4482,9 @@
<simple-table name="employees" start-index="122" stop-index="130" />
</from>
<order-by>
- <column-item name="pr" start-index="141" stop-index="142" />
- <column-item name="salary" start-index="145" stop-index="150" />
- <column-item name="last_name" start-index="153" stop-index="161" />
+ <column-item name="pr" start-index="141" stop-index="142" nulls-order-type="LAST" />
+ <column-item name="salary" start-index="145" stop-index="150" nulls-order-type="LAST" />
+ <column-item name="last_name" start-index="153" stop-index="161" nulls-order-type="LAST" />
</order-by>
</select>
@@ -4517,9 +4529,9 @@
</expr>
</where>
<order-by>
- <column-item name="last_name" start-index="206" stop-index="214" />
- <column-item name="salary" start-index="217" stop-index="222" />
- <column-item name="department_id" start-index="225" stop-index="237" />
+ <column-item name="last_name" start-index="206" stop-index="214" nulls-order-type="LAST" />
+ <column-item name="salary" start-index="217" stop-index="222" nulls-order-type="LAST" />
+ <column-item name="department_id" start-index="225" stop-index="237" nulls-order-type="LAST" />
</order-by>
</select>
@@ -4570,7 +4582,7 @@
</expr>
</where>
<order-by>
- <column-item name="ROWID" start-index="76" stop-index="80" />
+ <column-item name="ROWID" start-index="76" stop-index="80" nulls-order-type="LAST" />
</order-by>
</select>
@@ -4609,8 +4621,8 @@
</expr>
</where>
<order-by>
- <column-item name="job_id" start-index="523" stop-index="528" />
- <column-item name="employee_id" start-index="531" stop-index="541" />
+ <column-item name="job_id" start-index="523" stop-index="528" nulls-order-type="LAST" />
+ <column-item name="employee_id" start-index="531" stop-index="541" nulls-order-type="LAST" />
</order-by>
</select>
diff --git a/test/parser/src/main/resources/sql/supported/dml/select-distinct.xml b/test/parser/src/main/resources/sql/supported/dml/select-distinct.xml
index 352ec662018..fca0d21aeb5 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select-distinct.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select-distinct.xml
@@ -28,19 +28,20 @@
<!-- for with owner column with group by without order by -->
<sql-case id="select_distinct_with_owner_column_with_group_by" value="SELECT DISTINCT t_order.order_id FROM t_order GROUP BY t_order.order_id" db-types="MySQL" />
- <sql-case id="select_distinct_with_single_column" value="SELECT DISTINCT item_id FROM t_order_item ORDER BY item_id" />
- <sql-case id="select_distinct_with_multi_column" value="SELECT DISTINCT order_id, user_id, status FROM t_order ORDER BY order_id" />
- <sql-case id="select_distinct_with_owner_column" value="SELECT DISTINCT t_order.order_id FROM t_order ORDER BY order_id" />
- <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_single_column" value="SELECT DISTINCT item_id FROM t_order_item ORDER BY item_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_distinct_with_multi_column" value="SELECT DISTINCT order_id, user_id, status FROM t_order ORDER BY order_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_distinct_with_owner_column" value="SELECT DISTINCT t_order.order_id FROM t_order ORDER BY order_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_distinct_with_star" value="SELECT DISTINCT * FROM t_order WHERE order_id > 1100 ORDER BY order_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <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" db-types="H2,MySQL,SQLServer,SQL92" />
<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" />
<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" />
- <sql-case id="select_distinct_with_single_count_group_by" value="SELECT order_id, COUNT(DISTINCT order_id) c FROM t_order WHERE order_id < 1100 GROUP BY order_id ORDER BY order_id" />
- <sql-case id="select_distinct_with_count_group_by" value="SELECT COUNT(DISTINCT order_id) c, order_id FROM t_order GROUP BY order_id ORDER BY order_id" />
- <sql-case id="select_distinct_function" value="SELECT DISTINCT(item_id) FROM t_order_item ORDER BY item_id" db-types="H2,MySQL,PostgreSQL,openGauss,Oracle,SQLServer" />
+ <sql-case id="select_distinct_with_single_count_group_by" value="SELECT order_id, COUNT(DISTINCT order_id) c FROM t_order WHERE order_id < 1100 GROUP BY order_id ORDER BY order_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_distinct_with_count_group_by" value="SELECT COUNT(DISTINCT order_id) c, order_id FROM t_order GROUP BY order_id ORDER BY order_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_distinct_function" value="SELECT DISTINCT(item_id) FROM t_order_item ORDER BY item_id" db-types="H2,MySQL,SQLServer" />
+ <sql-case id="select_distinct_function_nulls_last" value="SELECT DISTINCT(item_id) FROM t_order_item ORDER BY item_id" db-types="PostgreSQL,openGauss,Oracle" />
<sql-case id="select_distinct_with_count_calculation" value="SELECT COUNT(DISTINCT user_id + order_id) c FROM t_order WHERE order_id < 1100" />
<sql-case id="select_distinct_with_aggregation_functions" value="SELECT SUM(DISTINCT order_id),count(DISTINCT order_id),count(order_id) FROM t_order WHERE order_id < 1100" />
</sql-cases>
diff --git a/test/parser/src/main/resources/sql/supported/dml/select-expression.xml b/test/parser/src/main/resources/sql/supported/dml/select-expression.xml
index a36e095c052..7d0390eed1a 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select-expression.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select-expression.xml
@@ -87,8 +87,8 @@
<sql-case id="select_projections_with_only_expr_for_postgres" value="SELECT CASE order_id WHEN 1 THEN '11' ELSE '00' END FROM t_order" db-types="PostgreSQL,openGauss" />
<sql-case id="select_with_amp" value="select 1 & 1" db-types="PostgreSQL,openGauss" />
<sql-case id="select_with_vertical_bar" value="select 1 | 1" db-types="PostgreSQL,openGauss" />
- <sql-case id="select_with_abs_function" value="SELECT ABS(1) FROM t_order WHERE ABS(1) > 1 GROUP BY ABS(1) ORDER BY ABS(1)" db-types="MySQL,Oracle" />
- <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_abs_function" value="SELECT ABS(1) FROM t_order WHERE ABS(1) > 1 GROUP BY ABS(1) ORDER BY ABS(1)" db-types="Oracle" />
+ <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,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" />
diff --git a/test/parser/src/main/resources/sql/supported/dml/select-group-by.xml b/test/parser/src/main/resources/sql/supported/dml/select-group-by.xml
index 7bd548a1c2d..c02de08c952 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select-group-by.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select-group-by.xml
@@ -17,25 +17,25 @@
-->
<sql-cases>
- <sql-case id="select_group_by_with_sum" value="SELECT SUM(order_id) AS orders_sum, user_id FROM t_order GROUP BY user_id ORDER BY user_id" />
- <sql-case id="select_group_by_with_count" value="SELECT COUNT(order_id) AS orders_count, user_id FROM t_order GROUP BY user_id ORDER BY user_id" />
- <sql-case id="select_group_by_with_max" value="SELECT MAX(order_id) AS max_order_id, user_id FROM t_order GROUP BY user_id ORDER BY user_id" />
- <sql-case id="select_group_by_with_min" value="SELECT MIN(order_id) AS min_order_id, user_id FROM t_order GROUP BY user_id ORDER BY user_id" />
- <sql-case id="select_group_by_with_avg" value="SELECT AVG(order_id) AS orders_avg, user_id FROM t_order GROUP BY user_id ORDER BY user_id" />
- <sql-case id="select_group_by_with_order_by_desc" value="SELECT SUM(order_id) AS orders_sum, user_id FROM t_order GROUP BY user_id ORDER BY orders_sum DESC" />
- <sql-case id="select_group_by_without_grouped_column" value="SELECT count(*) AS items_count FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY o.user_id" />
- <sql-case id="select_group_by_with_limit" value="SELECT user_id FROM t_order GROUP BY user_id ORDER BY user_id LIMIT ?" db-types="H2,MySQL,PostgreSQL,openGauss" />
- <sql-case id="select_group_by_with_order_by_and_limit" value="SELECT user_id, SUM(order_id) AS orders_sum FROM t_order GROUP BY user_id ORDER BY SUM(order_id) LIMIT ?" db-types="H2,MySQL,PostgreSQL,openGauss" />
- <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_sum" value="SELECT SUM(order_id) AS orders_sum, user_id FROM t_order GROUP BY user_id ORDER BY user_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_group_by_with_count" value="SELECT COUNT(order_id) AS orders_count, user_id FROM t_order GROUP BY user_id ORDER BY user_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_group_by_with_max" value="SELECT MAX(order_id) AS max_order_id, user_id FROM t_order GROUP BY user_id ORDER BY user_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_group_by_with_min" value="SELECT MIN(order_id) AS min_order_id, user_id FROM t_order GROUP BY user_id ORDER BY user_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_group_by_with_avg" value="SELECT AVG(order_id) AS orders_avg, user_id FROM t_order GROUP BY user_id ORDER BY user_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_group_by_with_order_by_desc" value="SELECT SUM(order_id) AS orders_sum, user_id FROM t_order GROUP BY user_id ORDER BY orders_sum DESC" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_group_by_without_grouped_column" value="SELECT count(*) AS items_count FROM t_order o JOIN t_order_item i ON o.user_id = i.user_id AND o.order_id = i.order_id WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? GROUP BY o.user_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_group_by_with_limit" value="SELECT user_id FROM t_order GROUP BY user_id ORDER BY user_id LIMIT ?" db-types="H2,MySQL" />
+ <sql-case id="select_group_by_with_order_by_and_limit" value="SELECT user_id, SUM(order_id) AS orders_sum FROM t_order GROUP BY user_id ORDER BY SUM(order_id) LIMIT ?" db-types="H2,MySQL" />
+ <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="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_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_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="H2,MySQL,SQLServer,SQL92" />
+ <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="H2,MySQL,SQLServer,SQL92" />
+ <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="H2,MySQL,SQLServer,SQL92" />
+ <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="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" />
<sql-case id="select_group_by_with_having_with_order_by" value="SELECT department_id, MIN(salary), MAX(salary) FROM employees GROUP BY department_id HAVING MIN(salary) < 5000 ORDER BY department_id" db-types="Oracle" />
<sql-case id="select_group_by_with_having_with_subquery" value="SELECT department_id, manager_id FROM employees GROUP BY department_id, manager_id HAVING (department_id, manager_id) IN (SELECT department_id, manager_id FROM employees x WHERE x.department_id = employees.department_id) ORDER BY department_id" db-types="Oracle" />
- <sql-case id="select_with_case_when_then_in_group_by_item_and_order_by_item" value="SELECT order_id FROM t_order GROUP BY CASE WHEN order_id > 0 AND order_id <= 10 THEN '(0,10]' WHEN order_id > 10 THEN '(10,+∞)' ELSE '' END ORDER BY CASE WHEN order_id > 0 AND order_id <= 10 THEN '(0,10]' WHEN order_id > 10 THEN '(10,+∞)' ELSE '' END" db-types="MySQL,PostgreSQL,openGauss,SQLServer,Oracle" />
+ <sql-case id="select_with_case_when_then_in_group_by_item_and_order_by_item" value="SELECT order_id FROM t_order GROUP BY CASE WHEN order_id > 0 AND order_id <= 10 THEN '(0,10]' WHEN order_id > 10 THEN '(10,+∞)' ELSE '' END ORDER BY CASE WHEN order_id > 0 AND order_id <= 10 THEN '(0,10]' WHEN order_id > 10 THEN '(10,+∞)' ELSE '' END" db-types="H2,MySQL,SQLServer" />
</sql-cases>
diff --git a/test/parser/src/main/resources/sql/supported/dml/select-order-by.xml b/test/parser/src/main/resources/sql/supported/dml/select-order-by.xml
index 9eccee8c0f8..5ad11dd0e6e 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select-order-by.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select-order-by.xml
@@ -17,13 +17,13 @@
-->
<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_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" />
- <sql-case id="select_order_by_for_nulls_first" value="SELECT o.order_id AS gen_order_id_ FROM t_order o ORDER BY o.order_id NULLS FIRST" db-types="Oracle" />
- <sql-case id="select_order_by_for_nulls_last" value="SELECT o.order_id AS gen_order_id_ FROM t_order o ORDER BY o.order_id ASC NULLS LAST" db-types="Oracle" />
- <sql-case id="select_order_by_with_multiple_stars" value="SELECT *, order_id, o.* FROM t_order o ORDER BY o.order_id" db-types="MySQL, SQLServer, PostgreSQL,openGauss" />
+ <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="H2,MySQL,SQL92" />
+ <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" db-types="H2,MySQL,SQLServer,SQL92" />
+ <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" db-types="H2,MySQL,SQLServer,SQL92" />
+ <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" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_order_by_for_nulls_first" value="SELECT o.order_id AS gen_order_id_ FROM t_order o ORDER BY o.order_id NULLS FIRST" db-types="PostgreSQL,openGauss,Oracle" />
+ <sql-case id="select_order_by_for_nulls_last" value="SELECT o.order_id AS gen_order_id_ FROM t_order o ORDER BY o.order_id ASC NULLS LAST" db-types="PostgreSQL,openGauss,Oracle" />
+ <sql-case id="select_order_by_with_multiple_stars" value="SELECT *, order_id, o.* FROM t_order o ORDER BY o.order_id" db-types="PostgreSQL,openGauss" />
<sql-case id="select_order_by_desc" value="SELECT * FROM employees WHERE job_id = 'PU_CLERK' ORDER BY salary DESC" db-types="Oracle" />
<sql-case id="select_order_by_asc_desc" value="SELECT salary, last_name FROM employees ORDER BY salary ASC, last_name DESC" db-types="Oracle" />
<sql-case id="select_order_by_with_alias_star_alias_name" value="SELECT o.* FROM t_order o ORDER BY o.order_id" db-types="H2,MySQL" />
diff --git a/test/parser/src/main/resources/sql/supported/dml/select-pagination.xml b/test/parser/src/main/resources/sql/supported/dml/select-pagination.xml
index e5e08b9f561..64d60fa0c1a 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select-pagination.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select-pagination.xml
@@ -18,7 +18,7 @@
<sql-cases>
<sql-case id="select_pagination_with_offset" 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 ?" db-types="PostgreSQL,openGauss" />
- <sql-case id="select_pagination_with_row_count" 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,H2,PostgreSQL,openGauss" />
+ <sql-case id="select_pagination_with_row_count" 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="PostgreSQL,openGauss" />
<sql-case id="select_pagination_with_top" 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_" db-types="SQLServer" />
<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" />
diff --git a/test/parser/src/main/resources/sql/supported/dml/select-relation.xml b/test/parser/src/main/resources/sql/supported/dml/select-relation.xml
index ddd5eeb5511..1b49647aaee 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select-relation.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select-relation.xml
@@ -20,8 +20,8 @@
<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" />
- <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_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" />
+ <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" />
<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" />
<sql-case id="select_full_outer_join_using_related_with_alias" value="SELECT department_id AS d_e_dept_id, e.last_name FROM departments d FULL OUTER JOIN employees e USING (department_id) ORDER BY department_id, e.last_name" db-types="Oracle" />
<sql-case id="select_cross_apply_join_related_with_alias" value="SELECT d.department_name, v.employee_id, v.last_name FROM departments d CROSS APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations') ORDER BY d.department_name, v.employee_id" db-types="Oracle" />
diff --git a/test/parser/src/main/resources/sql/supported/dml/select-union.xml b/test/parser/src/main/resources/sql/supported/dml/select-union.xml
index 871710ab64c..0c3233650b2 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select-union.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select-union.xml
@@ -19,8 +19,8 @@
<sql-cases>
<sql-case id="select_union" value="SELECT * FROM table1 UNION SELECT * FROM table2" db-types="MySQL,PostgreSQL,openGauss" />
<sql-case id="select_union_all" value="SELECT * FROM table1 UNION ALL SELECT * FROM table2" db-types="MySQL,PostgreSQL,openGauss" />
- <sql-case id="select_union_all_order_by" value="SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY id" db-types="MySQL,PostgreSQL,openGauss" />
- <sql-case id="select_union_all_order_by_limit" value="SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY id LIMIT 1, 1" db-types="MySQL,openGauss" />
+ <sql-case id="select_union_all_order_by" value="SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY id" db-types="PostgreSQL,openGauss" />
+ <sql-case id="select_union_all_order_by_limit" value="SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY id LIMIT 1, 1" db-types="openGauss" />
<sql-case id="select_intersect" value="SELECT * FROM table1 INTERSECT SELECT * FROM table2 INTERSECT SELECT * FROM table3" db-types="PostgreSQL,openGauss" />
<sql-case id="select_intersect_order_by" value="SELECT * FROM table1 INTERSECT SELECT * FROM table2 INTERSECT SELECT * FROM table3 ORDER BY id" db-types="PostgreSQL,openGauss" />
<sql-case id="select_intersect_order_by_limit" value="SELECT * FROM table1 INTERSECT SELECT * FROM table2 INTERSECT SELECT * FROM table3 ORDER BY id LIMIT 1, 1" db-types="openGauss" />
diff --git a/test/parser/src/main/resources/sql/supported/dml/select-with.xml b/test/parser/src/main/resources/sql/supported/dml/select-with.xml
index 75a1f7cdd3e..669d7bcfb19 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select-with.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select-with.xml
@@ -17,8 +17,8 @@
-->
<sql-cases>
- <sql-case id="select_with_subquery_factoring" value="WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total FROM departments d GROUP BY department_name) SELECT * FROM dept_costs WHERE dept_total > 304500 ORDER BY department_name" db-types="Oracle, SQLServer" />
- <sql-case id="select_with_subquery_factoring_with_binding_tables_without_join" value="WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name), avg_cost AS (SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY department_name" db-types="Oracle, SQLServer" />
+ <sql-case id="select_with_subquery_factoring" value="WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total FROM departments d GROUP BY department_name) SELECT * FROM dept_costs WHERE dept_total > 304500 ORDER BY department_name" db-types="SQLServer" />
+ <sql-case id="select_with_subquery_factoring_with_binding_tables_without_join" value="WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name), avg_cost AS (SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY department_name" db-types="SQLServer" />
<sql-case id="select_with_subquery_factoring_with_search_depth_first" value="WITH org_chart (eid, emp_last, mgr_id, reportLevel, salary, job_id) AS (SELECT employee_id, last_name, manager_id, reportLevel, salary, job_id FROM employees WHERE manager_id IS NULL) SEARCH DEPTH FIRST BY emp_last SET order1 SELECT emp_name, eid, mgr_id, salary, job_id FROM org_chart ORDER BY order1" db-types="Oracle" />
<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" />
diff --git a/test/parser/src/main/resources/sql/supported/dml/select.xml b/test/parser/src/main/resources/sql/supported/dml/select.xml
index eccc72045d6..65696873315 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select.xml
@@ -25,17 +25,17 @@
<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 = ?" />
<sql-case id="select_with_same_table_name_and_alias_column_with_owner" value="SELECT t_order.order_id,t_order.user_id,status FROM t_order t_order WHERE t_order.user_id = ? AND order_id = ?" db-types="MySQL,H2" />
- <sql-case id="select_not_equal_with_single_table" value="SELECT * FROM t_order_item WHERE item_id <> ? ORDER BY item_id" />
- <sql-case id="select_exclamation_equal_with_single_table" value="SELECT * FROM t_order_item WHERE item_id != ? ORDER BY item_id" />
- <sql-case id="select_not_in_with_single_table" value="SELECT * FROM t_order_item WHERE item_id IS NOT NULL AND item_id NOT IN (?, ?) ORDER BY item_id" />
- <sql-case id="select_not_between_with_single_table" value="SELECT * FROM t_order_item WHERE item_id IS NOT NULL AND item_id NOT BETWEEN ? AND ? ORDER BY item_id" />
+ <sql-case id="select_not_equal_with_single_table" value="SELECT * FROM t_order_item WHERE item_id <> ? ORDER BY item_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_exclamation_equal_with_single_table" value="SELECT * FROM t_order_item WHERE item_id != ? ORDER BY item_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_not_in_with_single_table" value="SELECT * FROM t_order_item WHERE item_id IS NOT NULL AND item_id NOT IN (?, ?) ORDER BY item_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_not_between_with_single_table" value="SELECT * FROM t_order_item WHERE item_id IS NOT NULL AND item_id NOT BETWEEN ? AND ? ORDER BY item_id" db-types="H2,MySQL,SQLServer,SQL92" />
<sql-case id="select_equal_with_single_table" value="SELECT * FROM t_order WHERE user_id = ? AND order_id = ?" />
<sql-case id="select_equal_with_single_table_and_lowercase_keyword" value="select * from t_order where user_id = ? and order_id = ?" />
- <sql-case id="select_in_with_single_table" value="SELECT * FROM t_order WHERE user_id IN (?, ?, ?) AND order_id IN (?, ?) ORDER BY user_id, order_id" />
- <sql-case id="select_between_with_single_table" value="SELECT * FROM t_order WHERE user_id BETWEEN ? AND ? AND order_id BETWEEN ? AND ? ORDER BY user_id, order_id" />
- <sql-case id="select_comparison_symbol_with_single_table" value="SELECT * FROM t_order WHERE user_id >= ? AND user_id <= ? AND order_id >= ? AND order_id <= ? ORDER BY user_id, order_id" />
+ <sql-case id="select_in_with_single_table" value="SELECT * FROM t_order WHERE user_id IN (?, ?, ?) AND order_id IN (?, ?) ORDER BY user_id, order_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_between_with_single_table" value="SELECT * FROM t_order WHERE user_id BETWEEN ? AND ? AND order_id BETWEEN ? AND ? ORDER BY user_id, order_id" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_comparison_symbol_with_single_table" value="SELECT * FROM t_order WHERE user_id >= ? AND user_id <= ? AND order_id >= ? AND order_id <= ? ORDER BY user_id, order_id" db-types="H2,MySQL,SQLServer,SQL92" />
<sql-case id="select_equal_with_same_sharding_column" value="SELECT * FROM t_order WHERE order_id = ? AND order_id = ?" />
- <sql-case id="select_in_with_same_sharding_column" value="SELECT * FROM t_order WHERE order_id IN (?, ?) AND order_id IN (?, ?) ORDER BY order_id" />
+ <sql-case id="select_in_with_same_sharding_column" value="SELECT * FROM t_order WHERE order_id IN (?, ?) AND order_id IN (?, ?) ORDER BY order_id" db-types="H2,MySQL,SQLServer,SQL92" />
<sql-case id="select_with_N_string_in_expression" value="SELECT * FROM t_order WHERE is_deleted = 'N'" />
<sql-case id="select_count_like" value="SELECT COUNT(*) FROM t_order WHERE (user_id = ? AND status LIKE ?)" />
<sql-case id="select_count_like_escape" value="SELECT COUNT(*) FROM t_order WHERE status LIKE ? escape '!' limit ? offset ?" db-types="PostgreSQL,openGauss"/>
@@ -44,10 +44,10 @@
<sql-case id="select_count_like_concat_postgres" value="SELECT count(0) AS orders_count FROM t_order o WHERE o.status LIKE CONCAT('%%', ?, '%%') AND o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?" db-types="PostgreSQL,openGauss" />
<sql-case id="select_like_with_single_quotes" value="select id from admin where fullname like 'a%'" db-types="MySQL" />
<sql-case id="select_count_tilde_concat" value="SELECT count(0) as orders_count FROM t_order o WHERE o.status ~~ CONCAT('%%', ?, '%%') AND o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?" db-types="PostgreSQL,openGauss" />
- <sql-case id="select_sharding_route_with_binding_tables" 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" />
- <sql-case id="select_full_route_with_binding_tables" 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 ORDER BY i.item_id" />
+ <sql-case id="select_sharding_route_with_binding_tables" 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" db-types="H2,MySQL,SQLServer,SQL92" />
+ <sql-case id="select_full_route_with_binding_tables" 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 ORDER BY i.item_id" db-types="H2,MySQL,SQLServer,SQL92" />
<!--TODO Need to verify case insensitivity of table names in sharding rule-->
- <sql-case id="select_sharding_route_with_broadcast_table" 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 JOIN t_broadcast_table c ON o.status = c.status WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? AND o.status = ? ORDER BY i.item_id" />
+ <sql-case id="select_sharding_route_with_broadcast_table" 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 JOIN t_broadcast_table c ON o.status = c.status WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? AND o.status = ? ORDER BY i.item_id" db-types="H2,MySQL,SQLServer,SQL92" />
<sql-case id="select_keyword_table_name_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 JOIN `select` c ON o.status = c.status WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? AND o.status = ? ORDER BY i.item_id" db-types="MySQL" />
<sql-case id="select_keyword_table_name_with_double_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 JOIN "select" c ON o.status = c.status WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? AND c.status = ? ORDER BY i.item_id" db-types="PostgreSQL,openGauss,Oracle" />
<sql-case id="select_keyword_table_name_with_square_brackets" 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 JOIN [select] c ON o.status = c.status WHERE o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ? AND c.status = ? ORDER BY i.item_id" db-types="SQLServer" />
@@ -92,7 +92,7 @@
<sql-case id="select_with_cast_as_unsigned_int" value="SELECT CAST(order_id AS UNSIGNED INT),user_id FROM t_order" db-types="MySQL" />
<sql-case id="select_with_cast_as_signed_integer" value="SELECT user_id,CAST(order_id AS SIGNED INTEGER) FROM t_order" db-types="MySQL" />
<sql-case id="select_with_cast_as_unsigned_integer" value="SELECT CAST(order_id AS UNSIGNED INTEGER),user_id FROM t_order" db-types="MySQL" />
- <sql-case id="select_with_simple_table" value="SELECT * FROM employees WHERE department_id = 30 ORDER BY last_name" db-types="MySQL, Oracle" />
+ <sql-case id="select_with_simple_table" value="SELECT * FROM employees WHERE department_id = 30 ORDER BY last_name" db-types="MySQL" />
<sql-case id="select_with_binding_tables_with_subquery_without_join" value="SELECT a.department_id "Department", a.num_emp/b.total_count "%_Employees", a.sal_sum/b.total_sal "%_Salary"
FROM (SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum FROM employees GROUP BY department_id) a, (SELECT COUNT(*) total_count, SUM(salary) total_sal FROM employees) b ORDER BY a.department_id" db-types="Oracle" />
<sql-case id="select_with_partitioned_table" value="SELECT * FROM sales PARTITION (sales_q2_2000) s WHERE s.amount_sold > 1500 ORDER BY cust_id, time_id, channel_id" db-types="Oracle" />
@@ -143,7 +143,7 @@
<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_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_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;" db-types="H2,MySQL,SQLServer,SQL92" />
<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" />
<sql-case id="select_xmlcolattval_function" value="SELECT XMLCOLATTVAL(e.employee_id AS EVALNAME 'ID', e.last_name AS name, e.salary) 'Emp Element' FROM employees e WHERE employee_id = 204;" db-types="Oracle" />