This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit fa27dedb89afef9f4d4425f4384ed41b2836c519 Author: zy-kkk <[email protected]> AuthorDate: Tue Jul 11 14:04:49 2023 +0800 [fix](jdbc scan) `1=1` does not translate to `TRUE` (#21688) For most database systems, they recognize where 1=1 but not where true, so we should send the original 1=1 to the database --- .../docker-compose/clickhouse/clickhouse.yaml.tpl | 2 +- .../docker-compose/clickhouse/init/04-insert.sql | 5 +- .../org/apache/doris/planner/JdbcScanNode.java | 3 + .../test_clickhouse_jdbc_catalog.out | Bin 2583 -> 2710 bytes .../jdbc_catalog_p0/test_mysql_jdbc_catalog.out | 69 ++++++++++----------- .../jdbc_catalog_p0/test_oracle_jdbc_catalog.out | 11 ++++ .../data/jdbc_catalog_p0/test_pg_jdbc_catalog.out | 11 ++++ .../test_sqlserver_jdbc_catalog.out | 9 +++ .../test_clickhouse_jdbc_catalog.groovy | 3 + .../jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy | 3 + .../test_oracle_jdbc_catalog.groovy | 3 + .../jdbc_catalog_p0/test_pg_jdbc_catalog.groovy | 3 + .../test_sqlserver_jdbc_catalog.groovy | 3 + 13 files changed, 88 insertions(+), 37 deletions(-) diff --git a/docker/thirdparties/docker-compose/clickhouse/clickhouse.yaml.tpl b/docker/thirdparties/docker-compose/clickhouse/clickhouse.yaml.tpl index b9e26d1c21..3832680dea 100644 --- a/docker/thirdparties/docker-compose/clickhouse/clickhouse.yaml.tpl +++ b/docker/thirdparties/docker-compose/clickhouse/clickhouse.yaml.tpl @@ -19,7 +19,7 @@ version: "2.1" services: doris--clickhouse: - image: "clickhouse/clickhouse-server:22.8.15.23-alpine" + image: "clickhouse/clickhouse-server:latest" restart: always environment: CLICKHOUSE_PASSWORD: 123456 diff --git a/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql b/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql index 2e48d12975..9c6785587a 100644 --- a/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql +++ b/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql @@ -23,7 +23,10 @@ INSERT INTO doris_test.type VALUES INSERT INTO doris_test.number (`k6`, `k7`, `k8`, `k9`, `k10`, `k11`, `k12`, `k13`, `k14`, `k15`, `k16`, `k17`, `k18`, `k19`) VALUES - (-3.4028235e38, -1.7976931348623157e308, -128, -32768, -2147483648, -9223372036854775808, -170141183460469231731687303715884105728, -57896044618658097711785492504343953926634992332820282019728792003956564819968, 0, 0, 0, 0, 0, 0), + (-3.4028235e38, -1.7976931348623157e308, -128, -32768, -2147483648, -9223372036854775808, -170141183460469231731687303715884105728, -57896044618658097711785492504343953926634992332820282019728792003956564819968, 0, 0, 0, 0, 0, 0); +INSERT INTO doris_test.number +(`k6`, `k7`, `k8`, `k9`, `k10`, `k11`, `k12`, `k13`, `k14`, `k15`, `k16`, `k17`, `k18`, `k19`) +VALUES (3.4028235e38, 1.7976931348623157e308, 127, 32767, 2147483647, 9223372036854775807, 170141183460469231731687303715884105727, 57896044618658097711785492504343953926634992332820282019728792003956564819967, 255, 65535, 4294967295, 18446744073709551615, 340282366920938463463374607431768211455, 115792089237316195423570985008687907853269984665640564039457584007913129639935); INSERT INTO doris_test.student values (1, 'doris', 18), (2, 'alice', 19), (3, 'bob', 20); diff --git a/fe/fe-core/src/main/java/org/apache/doris/planner/JdbcScanNode.java b/fe/fe-core/src/main/java/org/apache/doris/planner/JdbcScanNode.java index 77ff2239e1..86cadc847d 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/planner/JdbcScanNode.java +++ b/fe/fe-core/src/main/java/org/apache/doris/planner/JdbcScanNode.java @@ -134,6 +134,9 @@ public class JdbcScanNode extends ExternalScanNode { for (Expr p : conjunctsList) { if (shouldPushDownConjunct(jdbcType, p)) { String filter = conjunctExprToString(jdbcType, p); + if (filter.equals("TRUE")) { + filter = "1 = 1"; + } filters.add(filter); conjuncts.remove(p); } diff --git a/regression-test/data/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.out b/regression-test/data/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.out index aa5dfc6540..a677bba662 100644 Binary files a/regression-test/data/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.out and b/regression-test/data/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.out differ diff --git a/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out b/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out index c5c488982f..f04589e6a2 100644 --- a/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out +++ b/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out @@ -166,52 +166,46 @@ bca 2022-11-02 2022-11-02 8012 vivo 123456789012345678901234567890123.12345 12345678901234567890123456789012.12345 1234567890123456789012345678901234.12345 123456789012345678901234567890123.12345 123456789012345678901234567890123456789012345678901234567890.12345 123456789012345678901234567890123456789012345678901234567890.12345 -- !information_schema -- -ADMINISTRABLE_ROLE_AUTHORIZATIONS -APPLICABLE_ROLES CHARACTER_SETS -CHECK_CONSTRAINTS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY COLUMNS -COLUMNS_EXTENSIONS COLUMN_PRIVILEGES -COLUMN_STATISTICS -ENABLED_ROLES ENGINES EVENTS FILES +GLOBAL_STATUS +GLOBAL_VARIABLES INNODB_BUFFER_PAGE INNODB_BUFFER_PAGE_LRU INNODB_BUFFER_POOL_STATS -INNODB_CACHED_INDEXES INNODB_CMP INNODB_CMPMEM INNODB_CMPMEM_RESET INNODB_CMP_PER_INDEX INNODB_CMP_PER_INDEX_RESET INNODB_CMP_RESET -INNODB_COLUMNS -INNODB_DATAFILES -INNODB_FIELDS -INNODB_FOREIGN -INNODB_FOREIGN_COLS INNODB_FT_BEING_DELETED INNODB_FT_CONFIG INNODB_FT_DEFAULT_STOPWORD INNODB_FT_DELETED INNODB_FT_INDEX_CACHE INNODB_FT_INDEX_TABLE -INNODB_INDEXES +INNODB_LOCKS +INNODB_LOCK_WAITS INNODB_METRICS -INNODB_SESSION_TEMP_TABLESPACES -INNODB_TABLES -INNODB_TABLESPACES -INNODB_TABLESPACES_BRIEF -INNODB_TABLESTATS +INNODB_SYS_COLUMNS +INNODB_SYS_DATAFILES +INNODB_SYS_FIELDS +INNODB_SYS_FOREIGN +INNODB_SYS_FOREIGN_COLS +INNODB_SYS_INDEXES +INNODB_SYS_TABLES +INNODB_SYS_TABLESPACES +INNODB_SYS_TABLESTATS +INNODB_SYS_VIRTUAL INNODB_TEMP_TABLE_INFO INNODB_TRX -INNODB_VIRTUAL -KEYWORDS KEY_COLUMN_USAGE OPTIMIZER_TRACE PARAMETERS @@ -220,31 +214,19 @@ PLUGINS PROCESSLIST PROFILING REFERENTIAL_CONSTRAINTS -RESOURCE_GROUPS -ROLE_COLUMN_GRANTS -ROLE_ROUTINE_GRANTS -ROLE_TABLE_GRANTS ROUTINES SCHEMATA -SCHEMATA_EXTENSIONS SCHEMA_PRIVILEGES +SESSION_STATUS +SESSION_VARIABLES STATISTICS -ST_GEOMETRY_COLUMNS -ST_SPATIAL_REFERENCE_SYSTEMS -ST_UNITS_OF_MEASURE TABLES TABLESPACES -TABLESPACES_EXTENSIONS -TABLES_EXTENSIONS TABLE_CONSTRAINTS -TABLE_CONSTRAINTS_EXTENSIONS TABLE_PRIVILEGES TRIGGERS -USER_ATTRIBUTES USER_PRIVILEGES VIEWS -VIEW_ROUTINE_USAGE -VIEW_TABLE_USAGE -- !auto_default_t -- 0 @@ -256,6 +238,23 @@ VIEW_TABLE_USAGE \N 2023-06-17T10:00 +-- !filter1 -- +1 6 1 1 2099.18 3 8 1554296.82 68781940.49 d 8 5 0 d a 7 9 + +-- !filter2 -- +1 6 1 1 2099.18 3 8 1554296.82 68781940.49 d 8 5 0 d a 7 9 +2 8 9 8 2900.42 1 6 97486621.73 59634489.39 c 3 2 0 a e 7 4 +3 5 7 3 6276.86 8 9 32758730.38 10260499.72 c 8 1 0 d c 9 2 +4 3 7 5 2449.00 6 3 91359059.28 64743145.92 e 7 8 0 b d 8 4 +5 6 4 5 9137.82 2 7 26526675.70 90098303.36 a 6 7 0 d e 4 1 +6 3 6 8 7601.25 4 9 49117098.47 46499188.80 c 3 3 0 c d 4 8 +7 3 2 8 5297.81 9 3 23753694.20 96930000.64 c 7 2 0 b e 1 5 +8 3 6 7 3683.85 5 7 26056250.91 1127755.43 b 7 6 0 d b 4 7 +9 3 9 1 4785.38 1 5 95199488.12 94869703.42 a 4 4 0 c d 2 4 + +-- !filter3 -- +1 6 1 1 2099.18 3 8 1554296.82 68781940.49 d 8 5 0 d a 7 9 + -- !test_insert1 -- doris1 18 @@ -294,5 +293,5 @@ sys \N 302 \N 502 602 4.14159 \N 6.14159 \N -124 -302 2013 -402 -502 -602 \N 2012-10-26T02:08:39.345700 2013-10-26T08:09:18 -5.14145 \N -7.1400 row2 \N 09:11:09.567 text2 0xe86f6c6c6f20576f726c67 \N \N 0x2f \N 0x88656c6c9f Value3 201 301 401 501 601 3.14159 4.1415926 5.14159 true -123 -301 2012 -401 -501 -601 2012-10-30 2012-10-25T12:05:36.345700 2012-10-25T08:08:08 -4.14145 -5.1400000001 -6.1400 row1 line1 09:09:09.567 text1 0x48656c6c6f20576f726c64 {"age":30,"city":"London","name":"Alice"} Option1,Option3 0x2a 0x48656c6c6f00000000000000 0x48656c6c6f Value2 202 302 402 502 602 4.14159 5.1415926 6.14159 false -124 -302 2013 -402 -502 -602 2012-11-01 2012-10-26T02:08:39.345700 2013-10-26T08:09:18 -5.14145 -6.1400000001 -7.1400 row2 line2 09:11:09.567 text2 0xe86f6c6c6f20576f726c67 {"age":18,"city":"ChongQing","name":"Gaoxin"} Option1,Option2 0x2f 0x58676c6c6f00000000000000 0x88656c6c9f Value3 -203 303 403 503 603 7.14159 8.1415926 9.14159 false \N -402 2017 -602 -902 -1102 2012-11-02 \N 2013-10-27T08:11:18 -5.14145 -6.1400000000001 -7.1400 row3 line3 09:11:09.567 text3 0xe86f6c6c6f20576f726c67 {"age":24,"city":"ChongQing","name":"ChenQi"} Option2 0x2f 0x58676c6c6f00000000000000 \N Value1 +203 303 403 503 603 7.14159 8.1415926 9.14159 false \N -402 2017 -602 -902 -1102 2012-11-02 2023-07-10T09:34:21.204900 2013-10-27T08:11:18 -5.14145 -6.1400000000001 -7.1400 row3 line3 09:11:09.567 text3 0xe86f6c6c6f20576f726c67 {"age":24,"city":"ChongQing","name":"ChenQi"} Option2 0x2f 0x58676c6c6f00000000000000 \N Value1 diff --git a/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out b/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out index df7d46628a..9fe36e5634 100644 --- a/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out +++ b/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out @@ -67,6 +67,17 @@ 1 10000000 2 123460000000 +-- !filter1 -- +1 1 china beijing alice abcdefghrjkmnopq + +-- !filter2 -- +1 1 china beijing alice abcdefghrjkmnopq +2 2 china shanghai bob abcdefghrjkmnopq +3 3 Americ new york Jerry abcdefghrjkmnopq + +-- !filter3 -- +1 1 china beijing alice abcdefghrjkmnopq + -- !test_insert1 -- doris1 18 diff --git a/regression-test/data/jdbc_catalog_p0/test_pg_jdbc_catalog.out b/regression-test/data/jdbc_catalog_p0/test_pg_jdbc_catalog.out index e2c444894e..9d63aa961c 100644 --- a/regression-test/data/jdbc_catalog_p0/test_pg_jdbc_catalog.out +++ b/regression-test/data/jdbc_catalog_p0/test_pg_jdbc_catalog.out @@ -2150,6 +2150,17 @@ true abc def 2022-10-11 1.234 1 2 99 2022-10-22T10:59:59 34.123 1 jsonb {"nullKey":null,"arrayKey":["element1",2,false,null,{"nestedKey":"nestedValue"}],"floatKey":123.45,"objectKey":{"nestedStringKey":"nestedStringValue","nestedIntegerKey":67890},"stringKey":"stringValue","booleanKey":true,"integerKey":12345} 2 jsonb2 \N +-- !filter1 -- +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} +3 false 12.123456 10.16.10.14/32 10.16.10.14 ff:ff:ff:ff:ff:ff 0000001010 0001010 3 {"id":1} + +-- !filter2 -- +1 false 12.123456 10.16.10.14/32 10.16.10.14 ff:ff:ff:ff:ff:aa 1010101010 01010 1 {"id":1} + +-- !filter3 -- +1 false 12.123456 10.16.10.14/32 10.16.10.14 ff:ff:ff:ff:ff:aa 1010101010 01010 1 {"id":1} + -- !test_insert1 -- doris1 18 diff --git a/regression-test/data/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.out b/regression-test/data/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.out index 7ad59d0b81..d689f4cf6c 100644 --- a/regression-test/data/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.out +++ b/regression-test/data/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.out @@ -44,3 +44,12 @@ -- !dt -- 2023-06-25 14:30:45 2023-06-25T14:30:45 2023-06-25T14:30 2023-06-25T14:30:45.123456 2023-06-25 14:30:45.1234567 -07:00 +-- !filter1 -- +1 Make Doris Great! Make Doris Great! Make Doris Great! Make Doris Great! Make Doris Great! Make Doris Great! + +-- !filter2 -- +1 Make Doris Great! Make Doris Great! Make Doris Great! Make Doris Great! Make Doris Great! Make Doris Great! + +-- !filter3 -- +1 Make Doris Great! Make Doris Great! Make Doris Great! Make Doris Great! Make Doris Great! Make Doris Great! + diff --git a/regression-test/suites/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.groovy b/regression-test/suites/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.groovy index 4d3dec95f5..8675d04931 100644 --- a/regression-test/suites/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.groovy +++ b/regression-test/suites/jdbc_catalog_p0/test_clickhouse_jdbc_catalog.groovy @@ -55,6 +55,9 @@ suite("test_clickhouse_jdbc_catalog", "p0") { sql """ insert into internal.${internal_db_name}.${inDorisTable} select * from student; """ order_qt_in_tb """ select id, name, age from internal.${internal_db_name}.${inDorisTable} order by id; """ order_qt_system """ show tables from `system`; """ + 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 ; """ sql """ drop catalog if exists ${catalog_name} """ } diff --git a/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy b/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy index 5551217c98..28f8bfa4a7 100644 --- a/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy +++ b/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy @@ -105,6 +105,9 @@ suite("test_mysql_jdbc_catalog", "p0") { order_qt_auto_default_t """insert into ${auto_default_t}(name) values('a'); """ order_qt_dt """select * from ${dt}; """ order_qt_dt_null """select * from ${dt_null} order by 1; """ + order_qt_filter1 """select * from ${ex_tb17} where id = 1; """ + order_qt_filter2 """select * from ${ex_tb17} where 1=1 order by 1; """ + order_qt_filter3 """select * from ${ex_tb17} where id = 1 and 1 = 1; """ // test insert String uuid1 = UUID.randomUUID().toString(); diff --git a/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy b/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy index 1d31af4a00..a1fc12761b 100644 --- a/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy +++ b/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy @@ -65,6 +65,9 @@ suite("test_oracle_jdbc_catalog", "p0") { order_qt_test8 """ select * from TEST_NUMBER2 order by ID; """ order_qt_test9 """ select * from TEST_NUMBER3 order by ID; """ order_qt_test10 """ select * from TEST_NUMBER4 order by ID; """ + 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; """ // The result of TEST_RAW will change // So instead of qt, we're using sql here. diff --git a/regression-test/suites/jdbc_catalog_p0/test_pg_jdbc_catalog.groovy b/regression-test/suites/jdbc_catalog_p0/test_pg_jdbc_catalog.groovy index 555b058380..9d74c70c8e 100644 --- a/regression-test/suites/jdbc_catalog_p0/test_pg_jdbc_catalog.groovy +++ b/regression-test/suites/jdbc_catalog_p0/test_pg_jdbc_catalog.groovy @@ -73,6 +73,9 @@ suite("test_pg_jdbc_catalog", "p0") { order_qt_dt_test """ select * from dt_test order by 1; """ order_qt_json_test """ select * from json_test order by 1; """ order_qt_jsonb_test """ select * from jsonb_test order by 1; """ + order_qt_filter1 """ select * from test10 where 1 = 1 order by id; """ + order_qt_filter2 """ select * from test10 where id = 1 order by id; """ + order_qt_filter3 """ select * from test10 where 1 = 1 and id = 1 order by id; """ // test insert String uuid1 = UUID.randomUUID().toString(); diff --git a/regression-test/suites/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.groovy b/regression-test/suites/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.groovy index 932f40a8e0..65fa53695e 100644 --- a/regression-test/suites/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.groovy +++ b/regression-test/suites/jdbc_catalog_p0/test_sqlserver_jdbc_catalog.groovy @@ -61,6 +61,9 @@ suite("test_sqlserver_jdbc_catalog", "p0") { order_qt_test7 """ select * from test_decimal order by id; """ order_qt_test8 """ select * from test_text order by id; """ order_qt_dt """ select * from DateAndTime; """ + 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; """ sql """ drop catalog if exists ${catalog_name} """ --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
