search

StarRocks / starrocks

The world's fastest open query engine for sub-second analytics both on and off the data lakehouse. With the flexibility to support nearly any scenario, StarRocks provides best-in-class performance for multi-dimensional analytics, real-time analytics, and ad-hoc queries. A Linux Foundation project.
https://starrocks.io
Apache License 2.0
8.94k stars 1.79k forks source link

[sqlancer] query of result set mismatch #17204

Closed andyziye closed 1 year ago

andyziye commented 1 year ago

Steps to reproduce the behavior (Required)

DROP DATABASE IF EXISTS w1;
CREATE DATABASE w1;
USE w1;
admin set frontend config ("enable_decimal_v3" = "true");
CREATE TABLE IF NOT EXISTS t1 (c_1_0 DATE NOT NULL) PRIMARY KEY (c_1_0) PARTITION BY RANGE(c_1_0) (START ("2010-01-01") END ("2022-12-31") EVERY (INTERVAL 153 day)) DISTRIBUTED BY HASH (c_1_0) BUCKETS 3 properties("replication_num"="3") ;
INSERT INTO t1 VALUES ('2010-01-14'), ('2010-01-25');
SELECT t1_22.c_1_0 FROM t1 AS t1_22;
-- cardinality: 2
SELECT t1_22.c_1_0 FROM t1 AS t1_22 WHERE (t1_22.c_1_0)  BETWEEN (CAST((((((('')||('')))||('')))||(CONCAT_WS('', 'D', 'q', '', 'm') )) AS DATE ) ) AND (t1_22.c_1_0)  UNION ALL SELECT t1_22.c_1_0 FROM t1 AS t1_22 WHERE (NOT ((t1_22.c_1_0)  BETWEEN (CAST((((((('')||('')))||('')))||(CONCAT_WS('', 'D', 'q', '', 'm') )) AS DATE ) ) AND (t1_22.c_1_0) )) UNION ALL SELECT t1_22.c_1_0 FROM t1 AS t1_22 WHERE (((t1_22.c_1_0)  BETWEEN (CAST((((((('')||('')))||('')))||(CONCAT_WS('', 'D', 'q', '', 'm') )) AS DATE ) ) AND (t1_22.c_1_0) ) IS NULL);
-- cardinality: 4

Expected behavior (Required)

Real behavior (Required)

mysql> SELECT t1_22.c_1_0 FROM t1 AS t1_22;
+------------+
| c_1_0      |
+------------+
| 2010-01-14 |
| 2010-01-25 |
+------------+
2 rows in set (0.01 sec)

mysql> SELECT t1_22.c_1_0 FROM t1 AS t1_22 WHERE (t1_22.c_1_0)  BETWEEN (CAST((((((('')||('')))||('')))||(CONCAT_WS('', 'D', 'q', '', 'm') )) AS DATE ) ) AND (t1_22.c_1_0)  UNION ALL SELECT t1_22.c_1_0 FROM t1 AS t1_22 WHERE (NOT ((t1_22.c_1_0)  BETWEEN (CAST((((((('')||('')))||('')))||(CONCAT_WS('', 'D', 'q', '', 'm') )) AS DATE ) ) AND (t1_22.c_1_0) )) UNION ALL SELECT t1_22.c_1_0 FROM t1 AS t1_22 WHERE (((t1_22.c_1_0)  BETWEEN (CAST((((((('')||('')))||('')))||(CONCAT_WS('', 'D', 'q', '', 'm') )) AS DATE ) ) AND (t1_22.c_1_0) ) IS NULL);
+------------+
| c_1_0      |
+------------+
| 2010-01-14 |
| 2010-01-25 |
| 2010-01-14 |
| 2010-01-25 |
+------------+
4 rows in set (0.03 sec)

StarRocks version (Required)

andyziye commented 1 year ago

Similar problem

DROP DATABASE IF EXISTS w0;
CREATE DATABASE w0;
USE w0;
admin set frontend config ("enable_decimal_v3" = "true");
CREATE TABLE IF NOT EXISTS t0 (c_0_0 DATE NULL ,c_0_1 DECIMAL(35, 20)  NULL ,c_0_2 VARCHAR(1) NOT NULL ,c_0_3 INT NULL ,c_0_4 PERCENTILE PERCENTILE_UNION NULL ,c_0_5 BOOLEAN REPLACE NULL ,c_0_6 DATETIME MIN NOT NULL ,c_0_7 BIGINT REPLACE NOT NULL ,c_0_8 DECIMAL(15, 6)  MAX NULL ,c_0_9 HLL HLL_UNION NULL ,c_0_10 PERCENTILE PERCENTILE_UNION NOT NULL ) AGGREGATE KEY (c_0_0,c_0_1,c_0_2,c_0_3) PARTITION BY RANGE(c_0_0) (START ("2010-01-01") END ("2022-12-31") EVERY (INTERVAL 296 day)) DISTRIBUTED BY HASH (c_0_0,c_0_2) BUCKETS 3 properties("replication_num"="3") ;
CREATE TABLE IF NOT EXISTS t1 (c_1_0 DATE NOT NULL,c_1_1 LARGEINT NOT NULL,c_1_2 BOOLEAN NOT NULL,c_1_3 VARCHAR(1) NOT NULL,c_1_4 VARCHAR(21) NOT NULL,c_1_5 DOUBLE NOT NULL,c_1_6 ARRAY<STRING> NOT NULL,c_1_7 ARRAY<STRING> NOT NULL,c_1_8 ARRAY<INT> NOT NULL,c_1_9 DECIMAL(14, 1)  NOT NULL,c_1_10 VARCHAR(11) NOT NULL,c_1_11 ARRAY<STRING> NOT NULL) PRIMARY KEY (c_1_0,c_1_1,c_1_2,c_1_3) PARTITION BY RANGE(c_1_0) (START ("2010-01-01") END ("2022-12-31") EVERY (INTERVAL 96 day)) DISTRIBUTED BY HASH (c_1_0) BUCKETS 3 properties("replication_num"="3") ;
CREATE TABLE IF NOT EXISTS t2 (c_2_0 DATE NOT NULL,c_2_1 DATETIME NOT NULL,c_2_2 INT NOT NULL,c_2_3 TINYINT NOT NULL,c_2_4 VARCHAR(11) NOT NULL,c_2_5 BOOLEAN NOT NULL,c_2_6 DATETIME NOT NULL,c_2_7 DATE NOT NULL,c_2_8 ARRAY<STRING> NOT NULL,c_2_9 VARCHAR(31) NOT NULL,c_2_10 BOOLEAN NOT NULL, INDEX index1_c_2_0(`c_2_0`) USING BITMAP, INDEX index1_c_2_3(`c_2_3`) USING BITMAP, INDEX index1_c_2_9(`c_2_9`) USING BITMAP) PRIMARY KEY (c_2_0,c_2_1,c_2_2,c_2_3,c_2_4,c_2_5,c_2_6,c_2_7) PARTITION BY RANGE(c_2_0) (START ("2010-01-01") END ("2022-12-31") EVERY (INTERVAL 1186 day)) DISTRIBUTED BY HASH (c_2_1,c_2_5,c_2_0,c_2_2,c_2_4) BUCKETS 3 properties("replication_num"="3") ;
INSERT INTO t1 VALUES ('2010-01-23', -76184580, true, '', 'X2witcyxg', 0.6194191409580027, [], ["N2gB8Sr", "lN", "", "c8"], [-76184580, -1332634903, -76184580], 282709063663.4, 'b', ["vsWBCi3", "eM", ""]), ('2010-01-29', -1193522185, true, '', '', 0.4064345572121473, ["v0snM", "fZpl"], ["w3w", "", "YzKU", "", "f6cHpR"], [-1332634903, 813492911, 1451070645, 9223372036854775807, -1193522185, 1651281419], 922417505921.6, '', ["", "fp", "x", "V", "H7IVtY", ""]), ('2010-01-21', 1664524175, true, '', 'OamEfr', 0.1489776816907643, ["q", "", "", "aRg", "", "FtE", "", "", "laRCOBi", "C", "ARWkiAnn"], ["b", "Dx", "E3", "fSHR1CeR", ""], [-575900156, 707160446, -202389667, 1131850718, -202389667], 977143185761.1, 'a', ["", "7G", "Ec", "Dzy", "GFw", "", "bc", "52l8D4", "5", "U0HDE8eE", "54vudFdS", "4", "xPHqXn", "9", "h"]);
INSERT INTO t1 VALUES ('2010-01-11', -1193522185, true, '', 'VzkOoTCh5', -1.193522185E9, ["XR", "R", "", "6r8QkKu", "xrCLlv", "Egt", "zbplwW", "kL1J4", "m", "", "AHDt0aP", "", "o", "hPbMJrtx6", ""], ["4", "0p", "CZ7vQ9", "Q7", "", "X", "g", "", "", "2DSDu", "", "kjPPNfEw", "2"], [-202389667, 867791088, 526905780], 870338510406.2, 'azmKsb', ["Gt", "U", "0cWbOp"]), ('2010-01-02', 1245659195, false, '', 'h4', 0.44095647529035054, ["kbT", "Lq5piP", "", "rbyw", "bUD", "WmH", "", "", "WkQIRj", "NqWjKJCFl", "VL"], ["ct", "U", "", "M", "bBhTz", "9R", "", "9t48E7m", "ppnxS", "r6P", "", "xKr", "M", "RXxVUAmwJ"], [-187208352, 813492911, 1393351860], 646316106906.2, '', ["BnfDkIi", "7", "", "eMjkCcbt", "c"]), ('2010-01-23', 1393351860, false, '', 'UI', 0.9962010546236592, ["Sa", "", "SN8acTo", "EwF", "", "", ""], ["K6x5", "", "", "oV", "G", ""], [516932942], 410278830463.3, '', ["o", "9", "T2", "", "E", "U"]);
INSERT INTO t2 VALUES ('2010-01-04', '2010-01-30 08:00:14', 28, 4, 'kdRNB', true, '2010-01-11 08:00:14', '2010-01-20', ["", "", "FFcT", "B7u", "uKx3Ad", "M1", "fxRlFOie", "z", "gNd", "V"], '', true), ('2010-01-20', '2010-01-21 08:00:14', 25, 7, '1', false, '2010-01-31 08:00:14', '2010-01-29', ["", "w5u", "GS", "m4FJliO2", "iDqe", "", "sqv", "VwxNLF8", "", "", "hd2x", "jlvPqx"], 'S', true);
INSERT INTO t2 VALUES ('2010-01-29', '2010-01-31 08:00:14', 26, 5, 'BQlH5NHX', false, '2010-01-20 08:00:14', '2010-01-24', ["1Ss", "HRJfO0", "", "6", ""], 'JUuK', false);
INSERT INTO t1 VALUES ('2010-01-19', 1735939976, true, '', '7R2zluK', 0.9219947996945124, ["CN96kYMkJ", "9", "", "ld5TSLMlX", "PjIL", "B", "V3aTR", "5T", "wb3", "c2ZDa", "6P4bp"], ["gVxGY"], [813492911], 424729720845.6, 'ylIp', ["Q", "O6ibHlr", "u", "dVQnEKbJ", "K4", "G"]), ('2010-01-03', 526905780, false, '', 'I', 0.5485509470782004, ["", "hU", "", "", "TJHZy1tK", "VMkwh1H", "Wk"], ["X", "", "", "dyLz6kqJ9", "", "v8B1WiFP"], [473123316, 1131850718, -2001854476, -1432168522, 1153152596, -1570628308, 2032499487, 1735939976, -144705086, 1245659195, -1432168522, -1914477473, 430277710], 786279451635.0, 'N', ["r", "", "9", "PlulJFpt", "", "n", "", "dn1f", "G"]);
INSERT INTO t2 VALUES ('2010-01-17', '2010-01-30 08:00:14', 11, 7, 'TzY', true, '2010-01-13 08:00:14', '2010-01-21', ["", "IkofP5N7u", "", "H1", "PCBex", "NkTG8E54F", "hDV", "8"], 'QZaKbV', true), ('2010-01-13', '2010-01-18 08:00:14', 11, 1, 'Nz', false, '2010-01-05 08:00:14', '2010-01-03', [], 'UATe', true);
INSERT INTO t1 VALUES ('2010-01-26', -1742458664, true, '', '1Xb27fm0', -1.432168522E9, ["HYu05WK7t", "", "aG", "n", "asSn", "", "fV54nSa", "0K9m", "Q", "orl9Eipqv", "f4WvuC", "razX"], ["N3", "", "cQsFQv", "rx", "Thiir", "l", "hwS4K", "jO", "hAfQ", "VsWtW4J", "5", "PT4n2", "Gd2", "E"], [138596251, 2076434438, 1736411347, 502175796, 2094173586, 1736411347, 766724532, -202389667, 1451070645], 127106722452.1, 'gz5', ["Bp"]);
INSERT INTO t0 VALUES ('2010-01-17', 0.00000515170914555831, '', 3, percentile_hash('188223657'), false, '2010-01-20 08:00:14', -1332634903, 815.801262, hll_hash(''), percentile_hash('-829224942'));
INSERT INTO t0 VALUES ('2010-01-11', 0.00000614807536767174, '', 19, percentile_hash('-1321120054'), false, '2010-01-05 08:00:14', -1512236407, 390.301871, hll_hash('q'), percentile_hash('-1'));
INSERT INTO t1 VALUES ('2010-01-04', 1393351860, true, '', '4hccS', 5.16932942E8, [], ["32", "Q", "B", "vLn", "", "2g4moVQI5"], [1258647387, 707160446, 1664524175, 433821946, 516932942, -1688190573, 1736411347, 1153152596], 254627194905.7, 'qrFld', ["UCGjKnc", "", "B", "3m67s", "0hon83", "XikK1LC", "pQY", "", "rcSOVZtPi", "", "00PJ", "txT"]), ('2010-01-24', 848960536, false, '', '', 0.7835072299391865, ["", "7", "L6dV6ww", "I", "", "", "", "TBAORhu7", "8", "", "yELwg", "a"], [], [-826899671, -1431660666, -797341062, 2065016909, -1975815407, 1091775036, -106252425, -797341062, 181082994, 1177368573, 136099833, 138596251], 351639168834.5, '2f', []);
INSERT INTO t2 VALUES ('2010-01-10', '2010-01-03 08:00:14', 3, 2, '0D', false, '2010-01-24 08:00:14', '2010-01-24', ["igLV", "m8l", "FhL", "", "h1e"], '', false);
INSERT INTO t0 VALUES ('2010-01-04', 0.00000953289337558504, '', 3, percentile_hash('289450978'), false, '2010-01-25 08:00:14', 2032499487, 55.401787, hll_hash('4N0IaB011'), percentile_hash('1816457782')), ('2010-01-20', 0.00000220966112372874, '', 5, percentile_hash('2055997721'), true, '2010-01-20 08:00:14', -1227231963, 857.316784, hll_hash('x0'), percentile_hash('1145317160')), ('2010-01-20', 0.00000478527650380931, '', NULL, percentile_hash('-1914477473'), false, '2010-01-07 08:00:14', 138596251, 920.858045, hll_hash('Bq'), percentile_hash('-1753596027'));
INSERT INTO t0 VALUES ('2010-01-19', 0.00000175287970086306, '', NULL, percentile_hash('-1958308398'), false, '2010-01-08 08:00:14', -1936762160, NULL, hll_hash('R'), percentile_hash('867791088')), ('2010-01-12', 0.00000319951291765041, '', 22, percentile_hash('-1936762160'), NULL, '2010-01-07 08:00:14', 9223372036854775807, 151.467561, hll_hash('PEep3eE'), percentile_hash('506252858'));
INSERT INTO t0 VALUES ('2010-01-25', NULL, '', 16, percentile_hash('912439825'), false, '2010-01-08 08:00:14', 506252858, NULL, hll_hash('uWvhldQ8v'), percentile_hash('-1648439724'));
INSERT INTO t2 VALUES ('2010-01-15', '2010-01-04 08:00:14', 11, 3, '20', false, '2010-01-09 08:00:14', '2010-01-21', ["H5", "mKdsaf", "", "a", "42KrzJ", "skMT", "o29yn"], '3kFENze', true);
INSERT INTO t1 VALUES ('2010-01-10', -1332634903, true, '', '', 0.18484307308806414, ["", "", "CWAPlT4a", "J", "0nCk", "nvk1T5", "A1qRE"], ["l0xvO", "cP5mRCfP", "p2X", "vRRvn", "CKY", "5npFImy2", "s9r"], [], 268115179809.9, 'azC', ["", "zP", "", "", "F"]), ('2010-01-01', 636832503, true, '', 'X', 0.38431476188219194, ["12DR", "2X", "", "OlrUku08Q", "", "d", "", "2k2R", "", "V", "xM", "pOIwoRBF", "Qrp"], ["of01p", "lC", "J", "R", "", "CT9R", "Qe6Z7", "sgWpR", "Ol", "x", "GUHWq2", "oraBSGPj6", "v6"], [], 852656027959.8, 'Y8NT', ["O", "4jerCVC", "", "", "", "eQyPER", "2J8sceyC", "U", "e", "", "PTeY2Rn"]);
INSERT INTO t0 VALUES ('2010-01-28', 0.00000792282446878748, '', 21, percentile_hash('-709347608'), false, '2010-01-29 08:00:14', -1508200553, 676.470451, hll_hash('f'), percentile_hash('1168110289')), ('2010-01-13', NULL, '', 9, percentile_hash('-123789925'), true, '2010-01-02 08:00:14', -1975815407, 694.114057, hll_hash(''), percentile_hash('-264358685'));
-- SELECT t2_8.c_2_0 FROM t2 AS t2_8;
-- cardinality: 7
-- SELECT t2_8.c_2_0 FROM t2 AS t2_8 WHERE (t2_8.c_2_0)  BETWEEN (CAST(true AS DATE ) ) AND (t2_8.c_2_0)  UNION ALL SELECT t2_8.c_2_0 FROM t2 AS t2_8 WHERE (NOT ((t2_8.c_2_0)  BETWEEN (CAST(true AS DATE ) ) AND (t2_8.c_2_0) )) UNION ALL SELECT t2_8.c_2_0 FROM t2 AS t2_8 WHERE (((t2_8.c_2_0)  BETWEEN (CAST(true AS DATE ) ) AND (t2_8.c_2_0) ) IS NULL);
-- cardinality: 14