This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 487174d0430 [feature](planner) Support `select constant from dual`
syntax sugar (#34200)
487174d0430 is described below
commit 487174d0430fdc15e646074ccb56ec6428752dd9
Author: zy-kkk <zhongy...@gmail.com>
AuthorDate: Sun Apr 28 11:58:18 2024 +0800
[feature](planner) Support `select constant from dual` syntax sugar (#34200)
In MySQL, it's common to use a simplified syntax like `SELECT constant FROM
dual`
which is equivalent to just `SELECT constant`.
This syntax is often used by BI tools when utilizing MySQL connectors to
verify connection validity.
To enhance compatibility and ensure seamless integration with such tools,
we have now implemented this feature in Doris.
### Key Changes:
- Doris now interprets `SELECT constant FROM dual` as `SELECT constant`,
aligning with MySQL's behavior.
- This update ensures that BI tools can use standard MySQL connectors
without modifications or errors when connecting to Doris.
---
.../antlr4/org/apache/doris/nereids/DorisLexer.g4 | 4 +
.../antlr4/org/apache/doris/nereids/DorisParser.g4 | 1 +
fe/fe-core/src/main/cup/sql_parser.cup | 3 +
fe/fe-core/src/main/jflex/sql_scanner.flex | 1 +
regression-test/data/query_p0/dual/dual.out | 73 +++++++++++++++++
regression-test/suites/query_p0/dual/dual.groovy | 93 ++++++++++++++++++++++
6 files changed, 175 insertions(+)
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
index a976710afc2..b70d19b7e0a 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
@@ -225,6 +225,7 @@ DORIS_INTERNAL_TABLE_ID: 'DORIS_INTERNAL_TABLE_ID';
DOUBLE: 'DOUBLE';
DROP: 'DROP';
DROPP: 'DROPP';
+DUAL: 'DUAL';
DUPLICATE: 'DUPLICATE';
DYNAMIC: 'DYNAMIC';
ELSE: 'ELSE';
@@ -674,6 +675,9 @@ BRACKETED_COMMENT
: '/*' {!isHint()}? ( BRACKETED_COMMENT | . )*? ('*/' |
{markUnclosedComment();} EOF) -> channel(HIDDEN)
;
+FROM_DUAL
+ : 'FROM' WS+ 'DUAL' -> channel(HIDDEN);
+
WS
: [ \r\n\t]+ -> channel(HIDDEN)
;
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
index 64de9ecc0c6..b26019aa3ef 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
@@ -1089,6 +1089,7 @@ nonReserved
| DISTINCTPCSA
| DO
| DORIS_INTERNAL_TABLE_ID
+ | DUAL
| DYNAMIC
| ENABLE
| ENCRYPTKEY
diff --git a/fe/fe-core/src/main/cup/sql_parser.cup
b/fe/fe-core/src/main/cup/sql_parser.cup
index fc0b116bbea..e822ffb1508 100644
--- a/fe/fe-core/src/main/cup/sql_parser.cup
+++ b/fe/fe-core/src/main/cup/sql_parser.cup
@@ -363,6 +363,7 @@ terminal String
KW_DOUBLE,
KW_DROP,
KW_DROPP,
+ KW_DUAL,
KW_DUPLICATE,
KW_DYNAMIC,
KW_ELSE,
@@ -6097,6 +6098,8 @@ type_function_name ::=
from_clause ::=
KW_FROM table_ref_list:l
{: RESULT = new FromClause(l); :}
+ | KW_FROM KW_DUAL
+ {: RESULT = null; :}
;
table_ref_list ::=
diff --git a/fe/fe-core/src/main/jflex/sql_scanner.flex
b/fe/fe-core/src/main/jflex/sql_scanner.flex
index 7b0b3a72c5e..5ffb1df76c7 100644
--- a/fe/fe-core/src/main/jflex/sql_scanner.flex
+++ b/fe/fe-core/src/main/jflex/sql_scanner.flex
@@ -207,6 +207,7 @@ import org.apache.doris.qe.SqlModeHelper;
keywordMap.put("double", new Integer(SqlParserSymbols.KW_DOUBLE));
keywordMap.put("drop", new Integer(SqlParserSymbols.KW_DROP));
keywordMap.put("dropp", new Integer(SqlParserSymbols.KW_DROPP));
+ keywordMap.put("dual", new Integer(SqlParserSymbols.KW_DUAL));
keywordMap.put("duplicate", new
Integer(SqlParserSymbols.KW_DUPLICATE));
keywordMap.put("dynamic", new Integer(SqlParserSymbols.KW_DYNAMIC));
keywordMap.put("else", new Integer(SqlParserSymbols.KW_ELSE));
diff --git a/regression-test/data/query_p0/dual/dual.out
b/regression-test/data/query_p0/dual/dual.out
new file mode 100644
index 00000000000..fee517f8f2c
--- /dev/null
+++ b/regression-test/data/query_p0/dual/dual.out
@@ -0,0 +1,73 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+
+-- !sql --
+
+-- !sql --
+2
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+1
+2
+
+-- !sql --
+1
+
+-- !sql --
+1
+1
+1
+
+-- !sql --
+1
+
diff --git a/regression-test/suites/query_p0/dual/dual.groovy
b/regression-test/suites/query_p0/dual/dual.groovy
new file mode 100644
index 00000000000..eb001305b47
--- /dev/null
+++ b/regression-test/suites/query_p0/dual/dual.groovy
@@ -0,0 +1,93 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite('dual') {
+
+ qt_sql 'select 1 from dual'
+ qt_sql 'select 1 from dual where 1'
+ qt_sql 'select 1 from dual where 1 = 1'
+ qt_sql 'select 1 from dual where 0'
+ qt_sql 'select 1 from dual where 1 = 0'
+ qt_sql 'select 1+1 from dual'
+
+ // Testing constant expressions in more complex contexts
+ qt_sql 'select * from (select 1 as a from dual) sub'
+ qt_sql 'select 1 from dual group by 1'
+ qt_sql 'select 1 from dual having 1'
+ qt_sql 'select 1 from dual group by 1 having 1'
+ qt_sql 'select 1 from dual order by 1'
+ qt_sql 'select 1 from dual order by 1 desc'
+ qt_sql 'select 1 from dual order by 1 limit 1'
+ qt_sql 'select 1 from dual order by 1 limit 1 offset 1'
+ qt_sql 'select 1 from dual where 1 in (1)'
+ qt_sql 'select 1 from dual where 1 group by 1'
+ qt_sql 'select 1 from dual where 1 having 1'
+ qt_sql 'select 1 from dual where 1 group by 1 having 1'
+ qt_sql 'select 1 from dual where 1 order by 1'
+ qt_sql 'with cte as (select 1 as a from dual) select a from cte'
+ qt_sql 'select a from (select 1 as a from dual union all select 2 as a
from dual) u'
+ qt_sql 'select row_number() over (order by 1) from dual;'
+
+ // Dropping and creating a table named 'dual' to test behavior when dual
is a real table
+ sql 'drop table if exists `dual`'
+ sql '''
+ create table `dual` (
+ k0 int
+ )
+ distributed by hash(k0) buckets 16
+ properties(
+ 'replication_num'='1'
+ )
+ '''
+ sql 'insert into `dual` values (1)'
+ sql 'insert into `dual` values (2)'
+ sql 'insert into `dual` values (3)'
+
+ qt_sql 'select 1 from `dual`'
+ qt_sql 'select 1 from dual'
+
+ // Tests for dropping 'dual' and ensuring correct error handling
+ test {
+ sql 'drop table if exists dual'
+ exception """DUAL is keyword, maybe `DUAL`"""
+ }
+ sql 'drop table if exists `dual`'
+
+ // Test error handling when table does not exist
+ test {
+ sql "select 1 from `dual`"
+ exception "Table [dual] does not exist in database
[regression_test_query_p0_dual]"
+ }
+
+ // Disable and enable Nereids planner to check behavior differences
+ sql "set enable_nereids_planner = false"
+ test {
+ sql "select 1 from `dual`"
+ exception "Unknown table 'dual'"
+ }
+ sql "set enable_nereids_planner = true"
+
+ // Tests for unknown column errors
+ test {
+ sql "select a from dual"
+ exception "Unknown column 'a' in 'table list'"
+ }
+ test {
+ sql "select 1, a from dual"
+ exception "Unknown column 'a' in 'table list'"
+ }
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
