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 40516486082b87eecdcaaa199f2189100bbdbdea Author: meiyi <[email protected]> AuthorDate: Wed Oct 11 18:22:16 2023 +0800 [fix](point-query) Support mow table with sequence column (#25308) --- be/src/service/point_query_executor.cpp | 2 +- .../data/point_query_p0/test_point_query.out | 168 ++++++++++++ .../suites/point_query_p0/test_point_query.groovy | 293 +++++++++++---------- 3 files changed, 324 insertions(+), 139 deletions(-) diff --git a/be/src/service/point_query_executor.cpp b/be/src/service/point_query_executor.cpp index c7d1b523621..dbb77c4a99a 100644 --- a/be/src/service/point_query_executor.cpp +++ b/be/src/service/point_query_executor.cpp @@ -284,7 +284,7 @@ Status PointQueryExecutor::_lookup_row_key() { } // Get rowlocation and rowset, ctx._rowset_ptr will acquire wrap this ptr auto rowset_ptr = std::make_unique<RowsetSharedPtr>(); - st = (_tablet->lookup_row_key(_row_read_ctxs[i]._primary_key, true, specified_rowsets, + st = (_tablet->lookup_row_key(_row_read_ctxs[i]._primary_key, false, specified_rowsets, &location, INT32_MAX /*rethink?*/, segment_caches, rowset_ptr.get())); if (st.is<ErrorCode::KEY_NOT_FOUND>()) { diff --git a/regression-test/data/point_query_p0/test_point_query.out b/regression-test/data/point_query_p0/test_point_query.out index e6f5ad49e69..b5a2aaa9cb6 100644 --- a/regression-test/data/point_query_p0/test_point_query.out +++ b/regression-test/data/point_query_p0/test_point_query.out @@ -83,3 +83,171 @@ -- !sql -- 0 1 2 3 +-- !point_select -- +1231 119291.110000000 ddd laooq \N 2020-01-01 12:36:38 \N 1022-01-01 \N 1.111 \N [119181.111100000, 819019.119100000, NULL] + +-- !point_select -- +1231 119291.110000000 ddd laooq \N 2020-01-01 12:36:38 \N 1022-01-01 \N 1.111 \N [119181.111100000, 819019.119100000, NULL] + +-- !point_select -- +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] + +-- !point_select -- +1232 12222.991211350 xxx laooq 2023-01-02 2020-01-01 12:36:38 522.762 2022-01-01 true 212.111 \N \N + +-- !point_select -- +251 120939.111300000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa laooq 2030-01-02 2020-01-01 12:36:38 251.0 7022-01-01 true 90696620686827832.374 [11111.000000000] [] + +-- !point_select -- +252 120939.111300000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa laooq 2030-01-02 2020-01-01 12:36:38 252.0 7022-01-01 false 90696620686827832.374 \N [0.000000000] + +-- !point_select -- +298 120939.111300000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa laooq 2030-01-02 2020-01-01 12:36:38 298.0 7022-01-01 true 90696620686827832.374 [] [] + +-- !point_select -- +1235 991129292901.111380000 dd \N 2120-01-02 2020-01-01 12:36:38 652.692 5022-01-01 false 90696620686827832.374 [119181.111100000] ["aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"] + +-- !point_select -- +646464 6C616F6F71 + +-- !point_select -- +646464 6C616F6F71 + +-- !point_select -- +646464 6C616F6F71 + +-- !point_select -- +1235 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 true 1.111 [119291.192910000] ["111", "222", "333"] 1 + +-- !point_select -- +1235 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 true 1.111 [119291.192910000] ["111", "222", "333"] 1 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] \N 5630 0 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] \N 5630 0 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 + +-- !sql -- +1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, NULL] \N \N + +-- !sql -- +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] \N + +-- !sql -- +6120202020646464 6C616F6F71 32.92200050354004 + +-- !sql -- +1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, NULL] \N \N + +-- !sql -- +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] \N + +-- !sql -- +1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, NULL] \N \N + +-- !sql -- +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] \N + +-- !sql -- +0 1 2 3 + +-- !point_select -- +1231 119291.110000000 ddd laooq \N 2020-01-01 12:36:38 \N 1022-01-01 \N 1.111 \N [119181.111100000, 819019.119100000, NULL] + +-- !point_select -- +1231 119291.110000000 ddd laooq \N 2020-01-01 12:36:38 \N 1022-01-01 \N 1.111 \N [119181.111100000, 819019.119100000, NULL] + +-- !point_select -- +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] + +-- !point_select -- +1232 12222.991211350 xxx laooq 2023-01-02 2020-01-01 12:36:38 522.762 2022-01-01 true 212.111 \N \N + +-- !point_select -- +251 120939.111300000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa laooq 2030-01-02 2020-01-01 12:36:38 251.0 7022-01-01 true 90696620686827832.374 [11111.000000000] [] + +-- !point_select -- +252 120939.111300000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa laooq 2030-01-02 2020-01-01 12:36:38 252.0 7022-01-01 false 90696620686827832.374 \N [0.000000000] + +-- !point_select -- +298 120939.111300000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa laooq 2030-01-02 2020-01-01 12:36:38 298.0 7022-01-01 true 90696620686827832.374 [] [] + +-- !point_select -- +1235 991129292901.111380000 dd \N 2120-01-02 2020-01-01 12:36:38 652.692 5022-01-01 false 90696620686827832.374 [119181.111100000] ["aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"] + +-- !point_select -- +646464 6C616F6F71 + +-- !point_select -- +646464 6C616F6F71 + +-- !point_select -- +646464 6C616F6F71 + +-- !point_select -- +1235 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 true 1.111 [119291.192910000] ["111", "222", "333"] 1 + +-- !point_select -- +1235 120939.111300000 a ddd laooq 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 true 1.111 [119291.192910000] ["111", "222", "333"] 1 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] \N 5630 0 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] \N 5630 0 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 + +-- !point_select -- +1235 120939.111300000 a ddd xxxxxx 2030-01-02 2020-01-01 12:36:38 22.822 7022-01-01 false 1929111.111 [119291.192910000] ["111", "222", "333"] 2 + +-- !sql -- +1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, NULL] \N \N + +-- !sql -- +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] \N + +-- !sql -- +6120202020646464 6C616F6F71 32.92200050354004 + +-- !sql -- +1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, NULL] \N \N + +-- !sql -- +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] \N + +-- !sql -- +1231 119291.110000000 ddd laooq \N 2020-01-01T12:36:38 \N 1022-01-01 \N 1.111 [119181.111100000, 819019.119100000, NULL] \N \N + +-- !sql -- +1237 120939.111300000 a ddd laooq 2030-01-02 2020-01-01T12:36:38 22.822 7022-01-01 false 90696620686827832.374 [1.100000000, 2.200000000, 3.300000000, 4.400000000, 5.500000000] [] \N + +-- !sql -- +0 1 2 3 + diff --git a/regression-test/suites/point_query_p0/test_point_query.groovy b/regression-test/suites/point_query_p0/test_point_query.groovy index 48cea40718c..09825d5d6fd 100644 --- a/regression-test/suites/point_query_p0/test_point_query.groovy +++ b/regression-test/suites/point_query_p0/test_point_query.groovy @@ -36,8 +36,8 @@ suite("test_point_query") { // e.g: jdbc:mysql://locahost:8080 sql_port = urlWithoutSchema.substring(urlWithoutSchema.indexOf(":") + 1) } - // set server side prepared statment url - def url="jdbc:mysql://" + sql_ip + ":" + sql_port + "/" + realDb + "?&useServerPrepStmts=true" + // set server side prepared statement url + def prepare_url = "jdbc:mysql://" + sql_ip + ":" + sql_port + "/" + realDb + "?&useServerPrepStmts=true" def generateString = {len -> def str = "" @@ -47,6 +47,13 @@ suite("test_point_query") { return str } + def nprep_sql = { sql_str -> + def url_without_prep = "jdbc:mysql://" + sql_ip + ":" + sql_port + "/" + realDb + connect(user = user, password = password, url = url_without_prep) { + sql sql_str + } + } + sql """DROP TABLE IF EXISTS ${tableName}""" test { // abnormal case @@ -64,7 +71,9 @@ suite("test_point_query") { """ exception "errCode = 2, detailMessage = Row store column rely on light schema change, enable light schema change first" } - sql """ + + def create_table_sql = { property -> + return String.format(""" CREATE TABLE IF NOT EXISTS ${tableName} ( `k1` int(11) NULL COMMENT "", `k2` decimalv3(27, 9) NULL COMMENT "", @@ -86,143 +95,151 @@ suite("test_point_query") { "store_row_column" = "true", "enable_unique_key_merge_on_write" = "true", "light_schema_change" = "true", - "storage_format" = "V2" - ) - """ - sql """ INSERT INTO ${tableName} VALUES(1231, 119291.11, "ddd", "laooq", null, "2020-01-01 12:36:38", null, "1022-01-01 11:30:38", null, 1.111112, [119181.1111, 819019.1191, null], null) """ - sql """ INSERT INTO ${tableName} VALUES(1232, 12222.99121135, "xxx", "laooq", "2023-01-02", "2020-01-01 12:36:38", 522.762, "2022-01-01 11:30:38", 1, 212.111, null, null) """ - sql """ INSERT INTO ${tableName} VALUES(1233, 1.392932911, "yyy", "laooq", "2024-01-02", "2020-01-01 12:36:38", 52.862, "3022-01-01 11:30:38", 0, 5973903488739435.668, [119181.1111, null, 819019.1191], ["dijiiixxx"]) """ - sql """ INSERT INTO ${tableName} VALUES(1234, 12919291.129191137, "xxddd", "laooq", "2025-01-02", "2020-01-01 12:36:38", 552.872, "4022-01-01 11:30:38", 1, 5973903488739435.668, [1888109181.192111, 192129019.1191], ["1", "2", "3"]) """ - sql """ INSERT INTO ${tableName} VALUES(1235, 991129292901.11138, "dd", null, "2120-01-02", "2020-01-01 12:36:38", 652.692, "5022-01-01 11:30:38", 0, 90696620686827832.374, [119181.1111], ["${generateString(251)}"]) """ - sql """ INSERT INTO ${tableName} VALUES(1236, 100320.11139, "laa ddd", "laooq", "2220-01-02", "2020-01-01 12:36:38", 2.7692, "6022-01-01 11:30:38", 1, 23698.299, [], ["${generateString(251)}"]) """ - sql """ INSERT INTO ${tableName} VALUES(1237, 120939.11130, "a ddd", "laooq", "2030-01-02", "2020-01-01 12:36:38", 22.822, "7022-01-01 11:30:38", 0, 90696620686827832.374, [1.1, 2.2, 3.3, 4.4, 5.5], []) """ - sql """ INSERT INTO ${tableName} VALUES(251, 120939.11130, "${generateString(251)}", "laooq", "2030-01-02", "2020-01-01 12:36:38", 251, "7022-01-01 11:30:38", 1, 90696620686827832.374, [11111], []) """ - sql """ INSERT INTO ${tableName} VALUES(252, 120939.11130, "${generateString(252)}", "laooq", "2030-01-02", "2020-01-01 12:36:38", 252, "7022-01-01 11:30:38", 0, 90696620686827832.374, [0], null) """ - sql """ INSERT INTO ${tableName} VALUES(298, 120939.11130, "${generateString(298)}", "laooq", "2030-01-02", "2020-01-01 12:36:38", 298, "7022-01-01 11:30:38", 1, 90696620686827832.374, [], []) """ - - def nprep_sql = {sql_str-> - def url_without_prep ="jdbc:mysql://" + sql_ip + ":" + sql_port + "/" + realDb - connect(user=user, password=password, url=url_without_prep) { - sql sql_str - } + %s + "storage_format" = "V2") + """, property) } - // def url = context.config.jdbcUrl - def result1 = connect(user=user, password=password, url=url) { - def stmt = prepareStatement "select * from ${tableName} where k1 = ? and k2 = ? and k3 = ?" - assertEquals(stmt.class, com.mysql.cj.jdbc.ServerPreparedStatement); - stmt.setInt(1, 1231) - stmt.setBigDecimal(2, new BigDecimal("119291.11")) - stmt.setString(3, "ddd") - qe_point_select stmt - stmt.setInt(1, 1231) - stmt.setBigDecimal(2, new BigDecimal("119291.11")) - stmt.setString(3, "ddd") - qe_point_select stmt - stmt.setInt(1, 1237) - stmt.setBigDecimal(2, new BigDecimal("120939.11130")) - stmt.setString(3, "a ddd") - qe_point_select stmt - - stmt.setInt(1, 1232) - stmt.setBigDecimal(2, new BigDecimal("12222.99121135")) - stmt.setString(3, 'xxx') - qe_point_select stmt - - stmt.setInt(1, 251) - stmt.setBigDecimal(2, new BigDecimal("120939.11130")) - stmt.setString(3, generateString(251)) - qe_point_select stmt - - stmt.setInt(1, 252) - stmt.setBigDecimal(2, new BigDecimal("120939.11130")) - stmt.setString(3, generateString(252)) - qe_point_select stmt - - stmt.setInt(1, 298) - stmt.setBigDecimal(2, new BigDecimal("120939.11130")) - stmt.setString(3, generateString(298)) - qe_point_select stmt - stmt.close() - - stmt = prepareStatement "select * from ${tableName} where k1 = 1235 and k2 = ? and k3 = ?" - assertEquals(stmt.class, com.mysql.cj.jdbc.ServerPreparedStatement); - stmt.setBigDecimal(1, new BigDecimal("991129292901.11138")) - stmt.setString(2, "dd") - qe_point_select stmt - - def stmt_fn = prepareStatement "select hex(k3), hex(k4) from ${tableName} where k1 = ? and k2 =? and k3 = ?" - assertEquals(stmt_fn.class, com.mysql.cj.jdbc.ServerPreparedStatement); - stmt_fn.setInt(1, 1231) - stmt_fn.setBigDecimal(2, new BigDecimal("119291.11")) - stmt_fn.setString(3, "ddd") - qe_point_select stmt_fn - qe_point_select stmt_fn - qe_point_select stmt_fn - - nprep_sql """ - ALTER table ${tableName} ADD COLUMN new_column0 INT default "0"; - """ - sleep(1); - nprep_sql """ INSERT INTO ${tableName} VALUES(1235, 120939.11130, "a ddd", "laooq", "2030-01-02", "2020-01-01 12:36:38", 22.822, "7022-01-01 11:30:38", 1, 1.1111299, [119291.19291], ["111", "222", "333"], 1) """ - stmt.setBigDecimal(1, new BigDecimal("120939.11130")) - stmt.setString(2, "a ddd") - qe_point_select stmt - qe_point_select stmt - // invalidate cache - nprep_sql """ INSERT INTO ${tableName} VALUES(1235, 120939.11130, "a ddd", "xxxxxx", "2030-01-02", "2020-01-01 12:36:38", 22.822, "7022-01-01 11:30:38", 0, 1929111.1111,[119291.19291], ["111", "222", "333"], 2) """ - qe_point_select stmt - qe_point_select stmt - qe_point_select stmt - nprep_sql """ - ALTER table ${tableName} ADD COLUMN new_column1 INT default "0"; - """ - qe_point_select stmt - qe_point_select stmt - nprep_sql """ - ALTER table ${tableName} DROP COLUMN new_column1; - """ - qe_point_select stmt - qe_point_select stmt - // sql """ - // ALTER table ${tableName} ADD COLUMN new_column1 INT default "0"; - // """ - // qe_point_select stmt - } - // disable useServerPrepStmts - url = context.config.jdbcUrl - def result2 = connect(user=user, password=password, url=url) { - qt_sql """select * from ${tableName} where k1 = 1231 and k2 = 119291.11 and k3 = 'ddd'""" - qt_sql """select * from ${tableName} where k1 = 1237 and k2 = 120939.11130 and k3 = 'a ddd'""" - qt_sql """select hex(k3), hex(k4), k7 + 10.1 from ${tableName} where k1 = 1237 and k2 = 120939.11130 and k3 = 'a ddd'""" - // prepared text - sql """ prepare stmt1 from select * from ${tableName} where k1 = % and k2 = % and k3 = % """ - qt_sql """execute stmt1 using (1231, 119291.11, 'ddd')""" - qt_sql """execute stmt1 using (1237, 120939.11130, 'a ddd')""" - - sql """prepare stmt2 from select * from ${tableName} where k1 = % and k2 = % and k3 = %""" - qt_sql """execute stmt2 using (1231, 119291.11, 'ddd')""" - qt_sql """execute stmt2 using (1237, 120939.11130, 'a ddd')""" - tableName = "test_query" + for (int i = 0; i < 3; i++) { + tableName = realDb + ".tbl_point_query" + i sql """DROP TABLE IF EXISTS ${tableName}""" - sql """CREATE TABLE ${tableName} ( - `customer_key` bigint(20) NULL, - `customer_btm_value_0` text NULL, - `customer_btm_value_1` text NULL, - `customer_btm_value_2` text NULL - ) ENGINE=OLAP - UNIQUE KEY(`customer_key`) - COMMENT 'OLAP' - DISTRIBUTED BY HASH(`customer_key`) BUCKETS 16 - PROPERTIES ( - "replication_allocation" = "tag.location.default: 1", - "storage_format" = "V2", - "light_schema_change" = "true", - "store_row_column" = "true", - "enable_unique_key_merge_on_write" = "true", - "disable_auto_compaction" = "false" - );""" - sql """insert into ${tableName} values (0, "1", "2", "3")""" - qt_sql "select * from test_query where customer_key = 0" + if (i == 0) { + def sql0 = create_table_sql("") + sql """ ${sql0} """ + } else if (i == 1) { + def sql1 = create_table_sql("\"function_column.sequence_type\" = 'int',") + sql """ ${sql1} """ + } else { + def sql2 = create_table_sql("\"function_column.sequence_col\" = 'k6',") + sql """ ${sql2} """ + } + sql """ INSERT INTO ${tableName} VALUES(1231, 119291.11, "ddd", "laooq", null, "2020-01-01 12:36:38", null, "1022-01-01 11:30:38", null, 1.111112, [119181.1111, 819019.1191, null], null) """ + sql """ INSERT INTO ${tableName} VALUES(1232, 12222.99121135, "xxx", "laooq", "2023-01-02", "2020-01-01 12:36:38", 522.762, "2022-01-01 11:30:38", 1, 212.111, null, null) """ + sql """ INSERT INTO ${tableName} VALUES(1233, 1.392932911, "yyy", "laooq", "2024-01-02", "2020-01-01 12:36:38", 52.862, "3022-01-01 11:30:38", 0, 5973903488739435.668, [119181.1111, null, 819019.1191], ["dijiiixxx"]) """ + sql """ INSERT INTO ${tableName} VALUES(1234, 12919291.129191137, "xxddd", "laooq", "2025-01-02", "2020-01-01 12:36:38", 552.872, "4022-01-01 11:30:38", 1, 5973903488739435.668, [1888109181.192111, 192129019.1191], ["1", "2", "3"]) """ + sql """ INSERT INTO ${tableName} VALUES(1235, 991129292901.11138, "dd", null, "2120-01-02", "2020-01-01 12:36:38", 652.692, "5022-01-01 11:30:38", 0, 90696620686827832.374, [119181.1111], ["${generateString(251)}"]) """ + sql """ INSERT INTO ${tableName} VALUES(1236, 100320.11139, "laa ddd", "laooq", "2220-01-02", "2020-01-01 12:36:38", 2.7692, "6022-01-01 11:30:38", 1, 23698.299, [], ["${generateString(251)}"]) """ + sql """ INSERT INTO ${tableName} VALUES(1237, 120939.11130, "a ddd", "laooq", "2030-01-02", "2020-01-01 12:36:38", 22.822, "7022-01-01 11:30:38", 0, 90696620686827832.374, [1.1, 2.2, 3.3, 4.4, 5.5], []) """ + sql """ INSERT INTO ${tableName} VALUES(251, 120939.11130, "${generateString(251)}", "laooq", "2030-01-02", "2020-01-01 12:36:38", 251, "7022-01-01 11:30:38", 1, 90696620686827832.374, [11111], []) """ + sql """ INSERT INTO ${tableName} VALUES(252, 120939.11130, "${generateString(252)}", "laooq", "2030-01-02", "2020-01-01 12:36:38", 252, "7022-01-01 11:30:38", 0, 90696620686827832.374, [0], null) """ + sql """ INSERT INTO ${tableName} VALUES(298, 120939.11130, "${generateString(298)}", "laooq", "2030-01-02", "2020-01-01 12:36:38", 298, "7022-01-01 11:30:38", 1, 90696620686827832.374, [], []) """ + + def result1 = connect(user=user, password=password, url=prepare_url) { + def stmt = prepareStatement "select /*+ SET_VAR(enable_nereids_planner=false) */ * from ${tableName} where k1 = ? and k2 = ? and k3 = ?" + assertEquals(stmt.class, com.mysql.cj.jdbc.ServerPreparedStatement); + stmt.setInt(1, 1231) + stmt.setBigDecimal(2, new BigDecimal("119291.11")) + stmt.setString(3, "ddd") + qe_point_select stmt + stmt.setInt(1, 1231) + stmt.setBigDecimal(2, new BigDecimal("119291.11")) + stmt.setString(3, "ddd") + qe_point_select stmt + stmt.setInt(1, 1237) + stmt.setBigDecimal(2, new BigDecimal("120939.11130")) + stmt.setString(3, "a ddd") + qe_point_select stmt + + stmt.setInt(1, 1232) + stmt.setBigDecimal(2, new BigDecimal("12222.99121135")) + stmt.setString(3, 'xxx') + qe_point_select stmt + + stmt.setInt(1, 251) + stmt.setBigDecimal(2, new BigDecimal("120939.11130")) + stmt.setString(3, generateString(251)) + qe_point_select stmt + + stmt.setInt(1, 252) + stmt.setBigDecimal(2, new BigDecimal("120939.11130")) + stmt.setString(3, generateString(252)) + qe_point_select stmt + + stmt.setInt(1, 298) + stmt.setBigDecimal(2, new BigDecimal("120939.11130")) + stmt.setString(3, generateString(298)) + qe_point_select stmt + stmt.close() + + stmt = prepareStatement "select /*+ SET_VAR(enable_nereids_planner=false) */ * from ${tableName} where k1 = 1235 and k2 = ? and k3 = ?" + assertEquals(stmt.class, com.mysql.cj.jdbc.ServerPreparedStatement); + stmt.setBigDecimal(1, new BigDecimal("991129292901.11138")) + stmt.setString(2, "dd") + qe_point_select stmt + + def stmt_fn = prepareStatement "select /*+ SET_VAR(enable_nereids_planner=false) */ hex(k3), hex(k4) from ${tableName} where k1 = ? and k2 =? and k3 = ?" + assertEquals(stmt_fn.class, com.mysql.cj.jdbc.ServerPreparedStatement); + stmt_fn.setInt(1, 1231) + stmt_fn.setBigDecimal(2, new BigDecimal("119291.11")) + stmt_fn.setString(3, "ddd") + qe_point_select stmt_fn + qe_point_select stmt_fn + qe_point_select stmt_fn + + nprep_sql """ + ALTER table ${tableName} ADD COLUMN new_column0 INT default "0"; + """ + sleep(1); + nprep_sql """ INSERT INTO ${tableName} VALUES(1235, 120939.11130, "a ddd", "laooq", "2030-01-02", "2020-01-01 12:36:38", 22.822, "7022-01-01 11:30:38", 1, 1.1111299, [119291.19291], ["111", "222", "333"], 1) """ + stmt.setBigDecimal(1, new BigDecimal("120939.11130")) + stmt.setString(2, "a ddd") + qe_point_select stmt + qe_point_select stmt + // invalidate cache + nprep_sql """ INSERT INTO ${tableName} VALUES(1235, 120939.11130, "a ddd", "xxxxxx", "2030-01-02", "2020-01-01 12:36:38", 22.822, "7022-01-01 11:30:38", 0, 1929111.1111,[119291.19291], ["111", "222", "333"], 2) """ + qe_point_select stmt + qe_point_select stmt + qe_point_select stmt + nprep_sql """ + ALTER table ${tableName} ADD COLUMN new_column1 INT default "0"; + """ + qe_point_select stmt + qe_point_select stmt + nprep_sql """ + ALTER table ${tableName} DROP COLUMN new_column1; + """ + qe_point_select stmt + qe_point_select stmt + + // sql """ + // ALTER table ${tableName} ADD COLUMN new_column1 INT default "0"; + // """ + // qe_point_select stmt + } + // disable useServerPrepStmts + def result2 = connect(user=user, password=password, url=context.config.jdbcUrl) { + qt_sql """select /*+ SET_VAR(enable_nereids_planner=false) */ * from ${tableName} where k1 = 1231 and k2 = 119291.11 and k3 = 'ddd'""" + qt_sql """select /*+ SET_VAR(enable_nereids_planner=false) */ * from ${tableName} where k1 = 1237 and k2 = 120939.11130 and k3 = 'a ddd'""" + qt_sql """select /*+ SET_VAR(enable_nereids_planner=false) */ hex(k3), hex(k4), k7 + 10.1 from ${tableName} where k1 = 1237 and k2 = 120939.11130 and k3 = 'a ddd'""" + // prepared text + sql """ prepare stmt1 from select * from ${tableName} where k1 = % and k2 = % and k3 = % """ + qt_sql """execute stmt1 using (1231, 119291.11, 'ddd')""" + qt_sql """execute stmt1 using (1237, 120939.11130, 'a ddd')""" + + sql """prepare stmt2 from select * from ${tableName} where k1 = % and k2 = % and k3 = %""" + qt_sql """execute stmt2 using (1231, 119291.11, 'ddd')""" + qt_sql """execute stmt2 using (1237, 120939.11130, 'a ddd')""" + tableName = "test_query" + sql """DROP TABLE IF EXISTS ${tableName}""" + sql """CREATE TABLE ${tableName} ( + `customer_key` bigint(20) NULL, + `customer_btm_value_0` text NULL, + `customer_btm_value_1` text NULL, + `customer_btm_value_2` text NULL + ) ENGINE=OLAP + UNIQUE KEY(`customer_key`) + COMMENT 'OLAP' + DISTRIBUTED BY HASH(`customer_key`) BUCKETS 16 + PROPERTIES ( + "replication_allocation" = "tag.location.default: 1", + "storage_format" = "V2", + "light_schema_change" = "true", + "store_row_column" = "true", + "enable_unique_key_merge_on_write" = "true", + "disable_auto_compaction" = "false" + );""" + sql """insert into ${tableName} values (0, "1", "2", "3")""" + qt_sql "select /*+ SET_VAR(enable_nereids_planner=false) */ * from test_query where customer_key = 0" + } } } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
