You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2023/12/16 04:55:01 UTC
(doris) branch master updated: [fix](jdbc catalog) fix JdbcScanNode `NOT` CompoundPredicate filter expr handling errors (#28497)
This is an automated email from the ASF dual-hosted git repository.
yiguolei 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 a3e2c6affeb [fix](jdbc catalog) fix JdbcScanNode `NOT` CompoundPredicate filter expr handling errors (#28497)
a3e2c6affeb is described below
commit a3e2c6affeb5038229096f9e45933ddc05d77569
Author: zy-kkk <zh...@gmail.com>
AuthorDate: Sat Dec 16 12:54:55 2023 +0800
[fix](jdbc catalog) fix JdbcScanNode `NOT` CompoundPredicate filter expr handling errors (#28497)
---
.../doris/planner/external/jdbc/JdbcScanNode.java | 27 +++++++++--
.../jdbc/test_clickhouse_jdbc_catalog.out | Bin 4955 -> 4991 bytes
.../jdbc/test_mysql_jdbc_catalog.out | 18 ++++++++
.../jdbc/test_oracle_jdbc_catalog.out | 50 +++++++++++++++++++++
.../jdbc/test_pg_jdbc_catalog.out | 6 +++
.../jdbc/test_sqlserver_jdbc_catalog.out | 8 ++++
.../jdbc/test_clickhouse_jdbc_catalog.groovy | 2 +
.../jdbc/test_mysql_jdbc_catalog.groovy | 2 +
.../jdbc/test_oracle_jdbc_catalog.groovy | 14 ++++++
.../jdbc/test_pg_jdbc_catalog.groovy | 2 +
.../jdbc/test_sqlserver_jdbc_catalog.groovy | 2 +
11 files changed, 127 insertions(+), 4 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java
index 4eccaff4774..1ebff21a234 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java
@@ -21,6 +21,7 @@ import org.apache.doris.analysis.Analyzer;
import org.apache.doris.analysis.BinaryPredicate;
import org.apache.doris.analysis.BoolLiteral;
import org.apache.doris.analysis.CompoundPredicate;
+import org.apache.doris.analysis.CompoundPredicate.Operator;
import org.apache.doris.analysis.DateLiteral;
import org.apache.doris.analysis.Expr;
import org.apache.doris.analysis.ExprSubstitutionMap;
@@ -328,13 +329,31 @@ public class JdbcScanNode extends ExternalScanNode {
if (expr instanceof CompoundPredicate) {
StringBuilder result = new StringBuilder();
CompoundPredicate compoundPredicate = (CompoundPredicate) expr;
+
+ // If the operator is 'NOT', prepend 'NOT' to the start of the string
+ if (compoundPredicate.getOp() == Operator.NOT) {
+ result.append("NOT ");
+ }
+
+ // Iterate through all children of the CompoundPredicate
for (Expr child : compoundPredicate.getChildren()) {
+ // Recursively call conjunctExprToString for each child and append to the result
result.append(conjunctExprToString(tableType, child, tbl));
- result.append(" ").append(compoundPredicate.getOp().toString()).append(" ");
+
+ // If the operator is not 'NOT', append the operator after each child expression
+ if (!(compoundPredicate.getOp() == Operator.NOT)) {
+ result.append(" ").append(compoundPredicate.getOp().toString()).append(" ");
+ }
+ }
+
+ // For operators other than 'NOT', remove the extra appended operator at the end
+ // This is necessary for operators like 'AND' or 'OR' that appear between child expressions
+ if (!(compoundPredicate.getOp() == Operator.NOT)) {
+ result.setLength(result.length() - compoundPredicate.getOp().toString().length() - 2);
}
- // Remove the last operator
- result.setLength(result.length() - compoundPredicate.getOp().toString().length() - 2);
- return result.toString();
+
+ // Return the processed string trimmed of any extra spaces
+ return result.toString().trim();
}
if (expr.contains(DateLiteral.class) && expr instanceof BinaryPredicate) {
diff --git a/regression-test/data/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.out b/regression-test/data/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.out
index 30401d56d4c..4eba3e43641 100644
Binary files a/regression-test/data/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.out and b/regression-test/data/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.out differ
diff --git a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out
index ab9a45e02fc..c81d86828c6 100644
--- a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out
@@ -245,6 +245,24 @@ VIEWS
1 \N
2 2022-01-01
+-- !test_filter_not --
+张三1 11 12345678 123 321312 1999-02-13T00:00 中国 男 0
+张三2 11 12345671 123 321312 1999-02-13T00:00 中国 男 0
+张三3 11 12345673 123 321312 1999-02-13T00:00 中国 男 0
+张三4 11 123456711 123 321312 1999-02-13T00:00 中国 男 0
+张三5 11 1232134567 123 321312 1999-02-13T00:00 中国 男 0
+张三6 11 124314567 123 321312 1999-02-13T00:00 中国 男 0
+张三7 11 123445167 123 321312 1998-02-13T00:00 中国 男 0
+
+-- !test_filter_not_old_plan --
+张三1 11 12345678 123 321312 1999-02-13T00:00 中国 男 0
+张三2 11 12345671 123 321312 1999-02-13T00:00 中国 男 0
+张三3 11 12345673 123 321312 1999-02-13T00:00 中国 男 0
+张三4 11 123456711 123 321312 1999-02-13T00:00 中国 男 0
+张三5 11 1232134567 123 321312 1999-02-13T00:00 中国 男 0
+张三6 11 124314567 123 321312 1999-02-13T00:00 中国 男 0
+张三7 11 123445167 123 321312 1998-02-13T00:00 中国 男 0
+
-- !test_insert1 --
doris1 18
diff --git a/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out b/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
index 0545ddafac5..afb3a35b994 100644
--- a/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
@@ -78,6 +78,31 @@
-- !filter3 --
1 1 china beijing alice abcdefghrjkmnopq
+-- !filter4 --
+1 alice 20 99.5
+3 jerry 23 88.0
+4 andy 21 93.0
+
+-- !filter5 --
+1 alice 20 99.5
+2 bob 21 90.5
+3 jerry 23 88.0
+4 andy 21 93.0
+
+-- !filter6 --
+1 alice 20 99.5
+4 andy 21 93.0
+
+-- !filter7 --
+3 jerry 23 88.0
+
+-- !filter8 --
+4 andy 21 93.0
+
+-- !filter9 --
+3 jerry 23 88.0
+4 andy 21 93.0
+
-- !date1 --
1 2022-01-21T05:23:01 \N \N
2 2022-11-12T20:32:56 \N \N
@@ -102,6 +127,31 @@
-- !date7 --
3 \N 2019-11-12T20:33:57.999 \N \N \N \N \N
+-- !filter4_old_plan --
+1 alice 20 99.5
+3 jerry 23 88.0
+4 andy 21 93.0
+
+-- !filter5_old_plan --
+1 alice 20 99.5
+2 bob 21 90.5
+3 jerry 23 88.0
+4 andy 21 93.0
+
+-- !filter6_old_plan --
+1 alice 20 99.5
+4 andy 21 93.0
+
+-- !filter7_old_plan --
+3 jerry 23 88.0
+
+-- !filter8_old_plan --
+4 andy 21 93.0
+
+-- !filter9_old_plan --
+3 jerry 23 88.0
+4 andy 21 93.0
+
-- !test_insert1 --
doris1 18
diff --git a/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out b/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
index 5d1a91b9ce3..3920c1d7f90 100644
--- a/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
@@ -2119,6 +2119,12 @@ true abc def 2022-10-11 1.234 1 2 99 2022-10-22T10:59:59 34.123
-32768 -2147483648 true testtsetab -9223372036854775808
32767 2147483647 false testtsetab 9223372036854775807
+-- !filter4 --
+234 bcd
+
+-- !filter4_old --
+234 bcd
+
-- !test12 --
1 false 12.123456 10.16.10.14/32 10.16.10.14 ff:ff:ff:ff:ff:aa 1010101010 01010 1 {"id":1}
2 false 12.123456 10.16.10.14/32 10.16.10.14 ff:ff:ff:ff:ff:ff 0000001010 0000001010 2 {"id":1}
diff --git a/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out b/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
index 9906bb10f55..7624abc4589 100644
--- a/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
@@ -53,6 +53,14 @@
-- !filter3 --
1 Make Doris Great! Make Doris Great! Make Doris Great! Make Doris Great! Make Doris Great! Make Doris Great!
+-- !filter4 --
+2 alice 19
+3 bob 20
+
+-- !filter4_old --
+2 alice 19
+3 bob 20
+
-- !id --
2
diff --git a/regression-test/suites/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.groovy b/regression-test/suites/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.groovy
index 8497fec18aa..56c9d097cd1 100644
--- a/regression-test/suites/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.groovy
+++ b/regression-test/suites/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.groovy
@@ -78,6 +78,8 @@ suite("test_clickhouse_jdbc_catalog", "p0,external,clickhouse,external_docker,ex
order_qt_filter """ select k1,k2 from type where 1 = 1 order by 1 ; """
order_qt_filter2 """ select k1,k2 from type where 1 = 1 and k1 = true order by 1 ; """
order_qt_filter3 """ select k1,k2 from type where k1 = true order by 1 ; """
+ order_qt_filter4 """ select k28 from type where k28 not like '%String%' order by 1 ; """
+ order_qt_filter4_old """ select /*+ SET_VAR(enable_nereids_planner=false) */ k28 from type where k28 not like '%String%' order by 1 ; """
sql "set jdbc_clickhouse_query_final = true;"
order_qt_final1 """select * from final_test"""
sql "set jdbc_clickhouse_query_final = false;"
diff --git a/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy b/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
index 7517f52fa3c..af297da4869 100644
--- a/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
+++ b/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
@@ -172,6 +172,8 @@ suite("test_mysql_jdbc_catalog", "p0,external,mysql,external_docker,external_doc
order_qt_dt """select * from ${dt}; """
order_qt_dt_null """select * from ${dt_null} order by 1; """
order_qt_test_dz """select * from ${test_zd} order by 1; """
+ order_qt_test_filter_not """select * from ${ex_tb13} where name not like '%张三0%' order by 1; """
+ order_qt_test_filter_not_old_plan """select /*+ SET_VAR(enable_nereids_planner=false) */ * from ${ex_tb13} where name not like '%张三0%' order by 1; """
// test insert
String uuid1 = UUID.randomUUID().toString();
diff --git a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
index e42f29165b6..3d8d5e29f28 100644
--- a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
+++ b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
@@ -120,6 +120,12 @@ suite("test_oracle_jdbc_catalog", "p0,external,oracle,external_docker,external_d
order_qt_filter1 """ select * from TEST_CHAR where ID = 1 order by ID; """
order_qt_filter2 """ select * from TEST_CHAR where 1 = 1 order by ID; """
order_qt_filter3 """ select * from TEST_CHAR where ID = 1 and 1 = 1 order by ID; """
+ order_qt_filter4 """ select * from STUDENT where NAME NOT like '%bob%' order by ID; """
+ order_qt_filter5 """ select * from STUDENT where NAME NOT like '%bob%' or NAME NOT LIKE '%jerry%' order by ID; """
+ order_qt_filter6 """ select * from STUDENT where NAME NOT like '%bob%' and NAME NOT LIKE '%jerry%' order by ID; """
+ order_qt_filter7 """ select * from STUDENT where NAME NOT like '%bob%' and NAME LIKE '%jerry%' order by ID; """
+ order_qt_filter8 """ select * from STUDENT where NAME NOT like '%bob%' and ID = 4 order by ID; """
+ order_qt_filter9 """ SELECT * FROM STUDENT WHERE (NAME NOT LIKE '%bob%' AND AGE > 20) OR (SCORE < 90 AND NOT (NAME = 'alice' OR AGE <= 18)) order by ID; """
order_qt_date1 """ select * from TEST_DATE where T1 > '2022-01-21 00:00:00' or T1 < '2022-01-22 00:00:00'; """
order_qt_date2 """ select * from TEST_DATE where T1 > '2022-01-21 00:00:00' and T1 < '2022-01-22 00:00:00'; """
order_qt_date3 """ select * from TEST_DATE where (T1 > '2022-01-21 00:00:00' and T1 < '2022-01-22 00:00:00') or T1 > '2022-01-20 00:00:00'; """
@@ -128,6 +134,14 @@ suite("test_oracle_jdbc_catalog", "p0,external,oracle,external_docker,external_d
order_qt_date6 """ select * from TEST_DATE where (T1 < '2022-01-22 00:00:00' or T1 > '2022-01-20 00:00:00') and (T1 < '2022-01-23 00:00:00' or T1 > '2022-01-19 00:00:00'); """
order_qt_date7 """select * from TEST_TIMESTAMP where T2 < str_to_date('2020-12-21 12:34:56', '%Y-%m-%d %H:%i:%s');"""
+ // for old planner
+ order_qt_filter4_old_plan """ select /*+ SET_VAR(enable_nereids_planner=false) */ * from STUDENT where NAME NOT like '%bob%' order by ID; """
+ order_qt_filter5_old_plan """ select /*+ SET_VAR(enable_nereids_planner=false) */ * from STUDENT where NAME NOT like '%bob%' or NAME NOT LIKE '%jerry%' order by ID; """
+ order_qt_filter6_old_plan """ select /*+ SET_VAR(enable_nereids_planner=false) */ * from STUDENT where NAME NOT like '%bob%' and NAME NOT LIKE '%jerry%' order by ID; """
+ order_qt_filter7_old_plan """ select /*+ SET_VAR(enable_nereids_planner=false) */ * from STUDENT where NAME NOT like '%bob%' and NAME LIKE '%jerry%' order by ID; """
+ order_qt_filter8_old_plan """ select /*+ SET_VAR(enable_nereids_planner=false) */ * from STUDENT where NAME NOT like '%bob%' and ID = 4 order by ID; """
+ order_qt_filter9_old_plan """ SELECT /*+ SET_VAR(enable_nereids_planner=false) */ * FROM STUDENT WHERE (NAME NOT LIKE '%bob%' AND AGE > 20) OR (SCORE < 90 AND NOT (NAME = 'alice' OR AGE <= 18)) order by ID; """
+
// The result of TEST_RAW will change
// So instead of qt, we're using sql here.
sql """ select * from TEST_RAW order by ID; """
diff --git a/regression-test/suites/external_table_p0/jdbc/test_pg_jdbc_catalog.groovy b/regression-test/suites/external_table_p0/jdbc/test_pg_jdbc_catalog.groovy
index d44c24a4792..dad935acdc2 100644
--- a/regression-test/suites/external_table_p0/jdbc/test_pg_jdbc_catalog.groovy
+++ b/regression-test/suites/external_table_p0/jdbc/test_pg_jdbc_catalog.groovy
@@ -105,6 +105,8 @@ suite("test_pg_jdbc_catalog", "p0,external,pg,external_docker,external_docker_pg
order_qt_test9 """ select * from test7 order by id; """
order_qt_test10 """ select * from test8 order by id; """
order_qt_test11 """ select * from test9 order by id1; """
+ order_qt_filter4 """ select * from test3 where name not like '%abc%' order by id; """
+ order_qt_filter4_old """ select /*+ SET_VAR(enable_nereids_planner=false) */ * from test3 where name not like '%abc%' order by id; """
sql """ use ${ex_schema_name2}"""
order_qt_test12 """ select * from test10 order by id; """
diff --git a/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy b/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
index fde19f7a606..068f781fad6 100644
--- a/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
+++ b/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
@@ -76,6 +76,8 @@ suite("test_sqlserver_jdbc_catalog", "p0,external,sqlserver,external_docker,exte
order_qt_filter1 """ select * from test_char where 1 = 1 order by id; """
order_qt_filter2 """ select * from test_char where 1 = 1 and id = 1 order by id; """
order_qt_filter3 """ select * from test_char where id = 1 order by id; """
+ order_qt_filter4 """ select * from student where name not like '%doris%' order by id; """
+ order_qt_filter4_old """ select /*+ SET_VAR(enable_nereids_planner=false) */ * from student where name not like '%doris%' order by id; """
order_qt_id """ select count(*) from (select * from t_id) as a; """
order_qt_all_type """ select * from all_type order by id; """
sql """ drop table if exists internal.${internal_db_name}.all_type; """
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org