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 result error #7043

Closed colorfulu closed 2 years ago

colorfulu commented 2 years ago

Steps to reproduce the behavior (Required)

DROP DATABASE IF EXISTS w19;
CREATE DATABASE w19;
USE w19;
admin set frontend config ("enable_decimal_v3" = "true");
CREATE TABLE IF NOT EXISTS t0 (c_0_0 DATETIME NULL ,c_0_1 BIGINT NULL ,c_0_2 BOOLEAN NOT NULL ,c_0_3 SMALLINT NOT NULL ,c_0_4 VARCHAR(1) NULL ,c_0_5 VARCHAR(1) NULL ,c_0_6 DECIMAL(37, 15)  NULL ,c_0_7 BOOLEAN NOT NULL ,c_0_8 DECIMAL(3, 2)  MIN NOT NULL ,c_0_9 TINYINT MIN NULL ,c_0_10 DECIMAL(10, 2)  MAX NOT NULL ,c_0_11 FLOAT MIN NULL ,c_0_12 DATE REPLACE_IF_NOT_NULL NOT NULL ,c_0_13 BOOLEAN REPLACE NULL ,c_0_14 BOOLEAN REPLACE NULL ,c_0_15 INT REPLACE_IF_NOT_NULL NOT NULL ,c_0_16 PERCENTILE PERCENTILE_UNION NULL ,c_0_17 CHAR(1) MIN NOT NULL ) AGGREGATE KEY (c_0_0,c_0_1,c_0_2,c_0_3,c_0_4,c_0_5,c_0_6,c_0_7) PARTITION BY RANGE(c_0_0) (START ("2010-01-01") END ("2021-12-31") EVERY (INTERVAL 292 day)) DISTRIBUTED BY HASH (c_0_7,c_0_0) BUCKETS 3 properties("replication_num"="3", "bloom_filter_columns"="c_0_3") ;
CREATE TABLE t1 (c_1_0 DATETIME NOT NULL ,c_1_1 BOOLEAN NULL ,c_1_2 BIGINT NULL ,c_1_3 CHAR(11) NULL ,c_1_4 SMALLINT NULL ,c_1_5 SMALLINT NOT NULL ,c_1_6 BOOLEAN NOT NULL ,c_1_7 DATE NOT NULL ,c_1_8 TINYINT MAX NOT NULL ,c_1_9 VARCHAR(21) MIN NOT NULL ,c_1_10 HLL HLL_UNION NULL ,c_1_11 HLL HLL_UNION NULL , INDEX index1_c_1_3(`c_1_3`) USING BITMAP) AGGREGATE KEY (c_1_0,c_1_1,c_1_2,c_1_3,c_1_4,c_1_5,c_1_6,c_1_7) PARTITION BY RANGE(c_1_0) (START ("2010-01-01") END ("2021-12-31") EVERY (INTERVAL 1095 day)) DISTRIBUTED BY HASH (c_1_1,c_1_6,c_1_0) BUCKETS 3 properties("replication_num"="3", "bloom_filter_columns"="c_1_3") ;
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES ('2010-01-08 08:00:14', false, NULL, 'WT', 3, 15, false, '2009-12-31', 7, '', hll_hash('EUa7DtO4E'), hll_hash('mbsmr4y'));
INSERT INTO t1 VALUES ('2010-01-20 08:00:14', true, 1894629272, 'I', 11, 2, true, '2010-01-07', 0, 'q8Oua', hll_hash('n'), hll_hash('')), ('2010-01-13 08:00:14', true, 644649284, 'ox', 4, 5, false, '2010-01-15', 3, '', hll_hash(''), hll_hash(''));
INSERT INTO t1 VALUES ('2010-01-14 08:00:14', NULL, -891830877, '', NULL, 3, false, '2010-01-21', 3, 'cne5fKzy', hll_hash('ZvVvcbrrc'), hll_hash('53PEA')), ('2010-01-13 08:00:14', true, 1194569391, '1e', 14, 1, false, '2010-01-28', 2, 'Nz', hll_hash(''), hll_hash('4KGEZV')), ('2010-01-25 08:00:14', false, -1339119814, '3', 4, 1, true, '2010-01-19', 4, 'Ej', hll_hash('GE'), hll_hash('Hm31'));
INSERT INTO t0 VALUES ('2010-01-11 08:00:14', 1894629272, false, 13, '', '', 5370310.095403644668867, true, 0.00, 2, 840294.04, 0.4050709463591464, '2010-01-23', false, false, NULL, percentile_hash('1194569391'), ''), ('2010-01-18 08:00:14', 1258632297, true, 10, '', '', 8843098.172944731565308, true, 0.05, 4, 205668.10, 0.4050709463591464, '2010-01-26', true, false, NULL, percentile_hash('731978974'), '');
INSERT INTO t1 VALUES ('2010-01-01 08:00:14', true, 1194569391, 'l660', 1, 15, true, '2010-01-01', 0, 'FD', hll_hash(''), hll_hash(''));
INSERT INTO t1 VALUES ('2010-01-27 08:00:14', NULL, 731978974, '', 3, 1, false, '2010-01-19', 5, '', hll_hash('IWW'), hll_hash('N')), ('2010-01-21 08:00:14', false, 1194569391, NULL, 14, 2, true, '2010-01-01', 2, 'Gh4', hll_hash('A'), hll_hash('m'));
INSERT INTO t0 VALUES ('2010-01-26 08:00:14', 108710695, false, 15, NULL, '', 762673.020696728359110, false, 0.06, 2, 329183.54, 0.4050709463591464, NULL, false, true, 8, percentile_hash('644649284'), '');
INSERT INTO t1 VALUES ('2010-01-25 08:00:14', true, -1752848329, '6', 11, 11, false, '2010-01-02', 3, '', hll_hash('q'), hll_hash('pECi'));
INSERT INTO t0 VALUES ('2010-01-31 08:00:14', -891830877, true, 13, '', '', 5047760.875380886739674, false, 0.05, NULL, 247370.56, 0.843937819710018, '2010-01-21', true, false, NULL, percentile_hash('-433449203'), '');
INSERT INTO t1 VALUES ('2010-01-10 08:00:14', false, NULL, 'k', 10, 8, false, '2010-01-02', 2, 'o9U', hll_hash('HcIJ4'), hll_hash('Jaw')), ('2010-01-10 08:00:14', false, NULL, NULL, 10, 8, true, '2010-01-02', 6, '69', hll_hash('bLriqfd'), hll_hash('T77OcDIy2'));
INSERT INTO t1 VALUES ('2010-01-16 08:00:14', false, 417856696, 'TRYN4', 1, 2, true, '2010-01-05', 6, 't', hll_hash('LxKuJoKLI'), hll_hash('UL7M')), ('2010-01-16 08:00:14', true, 1591505309, '', 9, 1, false, '2010-01-03', 7, '9Qa9UxC', hll_hash('wq'), hll_hash(''));
INSERT INTO t0 VALUES ('2010-01-22 08:00:14', -948986423, false, 6, '', '', 7503532.552610295264940, false, 0.00, 0, 848236.45, 0.843937819710018, '2010-01-21', true, NULL, 29, percentile_hash('-903844712'), ''), ('2010-01-20 08:00:14', 2141702375, true, 1, '', '', NULL, true, 0.02, 0, 989109.72, 0.8574181295079425, '2010-01-01', true, true, 29, percentile_hash('394285596'), '');
INSERT INTO t0 VALUES (NULL, 1189099178, false, 8, '', '', 4879542.603611921426096, false, 0.07, 7, 926203.28, 0.1513084757299633, '2010-01-31', true, false, 25, percentile_hash('466603089'), '');
INSERT INTO t1 VALUES ('2010-01-13 08:00:14', false, 1904183801, NULL, 15, 4, false, '2010-01-26', 5, 'UhF', hll_hash('ZzJ6P'), hll_hash('')), ('2010-01-12 08:00:14', true, 1194569391, NULL, 3, 12, true, '2010-01-28', 0, '', hll_hash('eq'), hll_hash('J'));
-- SELECT t1_214.c_1_0 FROM t1 AS t1_214, t0 AS t0_213;
-- cardinality: 96
-- SELECT t1_214.c_1_0 FROM t1 AS t1_214, t0 AS t0_213 WHERE (t1_214.c_1_2) IN ((SELECT subt1.c_1_2 FROM  (SELECT t0_213.c_0_0, t0_213.c_0_1, t0_213.c_0_2, t0_213.c_0_3, t0_213.c_0_4, t0_213.c_0_5, t0_213.c_0_6, t0_213.c_0_7, t0_213.c_0_8, t0_213.c_0_9, t0_213.c_0_10, t0_213.c_0_11, t0_213.c_0_12, t0_213.c_0_13, t0_213.c_0_14, t0_213.c_0_15, t0_213.c_0_17 FROM t0 AS t0_213) subt0 RIGHT JOIN (SELECT t1_214.c_1_0, t1_214.c_1_1, t1_214.c_1_2, t1_214.c_1_3, t1_214.c_1_4, t1_214.c_1_5, t1_214.c_1_6, t1_214.c_1_7, t1_214.c_1_8, t1_214.c_1_9 FROM t1 AS t1_214) subt1 ON subt0.c_0_0 = subt1.c_1_1 AND subt0.c_0_0 >= subt1.c_1_1 AND subt0.c_0_13 != subt1.c_1_9 AND subt0.c_0_13 > subt1.c_1_9 WHERE  ( t0_213.c_0_14 ) = ( ('1969-12-18') IN ('1970-01-20', '1970-01-04')  ) ) )  UNION ALL SELECT t1_214.c_1_0 FROM t1 AS t1_214, t0 AS t0_213 WHERE (NOT ((t1_214.c_1_2) IN ((SELECT subt1.c_1_2 FROM  (SELECT t0_213.c_0_0, t0_213.c_0_1, t0_213.c_0_2, t0_213.c_0_3, t0_213.c_0_4, t0_213.c_0_5, t0_213.c_0_6, t0_213.c_0_7, t0_213.c_0_8, t0_213.c_0_9, t0_213.c_0_10, t0_213.c_0_11, t0_213.c_0_12, t0_213.c_0_13, t0_213.c_0_14, t0_213.c_0_15, t0_213.c_0_17 FROM t0 AS t0_213) subt0 RIGHT JOIN (SELECT t1_214.c_1_0, t1_214.c_1_1, t1_214.c_1_2, t1_214.c_1_3, t1_214.c_1_4, t1_214.c_1_5, t1_214.c_1_6, t1_214.c_1_7, t1_214.c_1_8, t1_214.c_1_9 FROM t1 AS t1_214) subt1 ON subt0.c_0_0 = subt1.c_1_1 AND subt0.c_0_0 >= subt1.c_1_1 AND subt0.c_0_13 != subt1.c_1_9 AND subt0.c_0_13 > subt1.c_1_9 WHERE  ( t0_213.c_0_14 ) = ( ('1969-12-18') IN ('1970-01-20', '1970-01-04')  ) ) ) )) UNION ALL SELECT t1_214.c_1_0 FROM t1 AS t1_214, t0 AS t0_213 WHERE (((t1_214.c_1_2) IN ((SELECT subt1.c_1_2 FROM  (SELECT t0_213.c_0_0, t0_213.c_0_1, t0_213.c_0_2, t0_213.c_0_3, t0_213.c_0_4, t0_213.c_0_5, t0_213.c_0_6, t0_213.c_0_7, t0_213.c_0_8, t0_213.c_0_9, t0_213.c_0_10, t0_213.c_0_11, t0_213.c_0_12, t0_213.c_0_13, t0_213.c_0_14, t0_213.c_0_15, t0_213.c_0_17 FROM t0 AS t0_213) subt0 RIGHT JOIN (SELECT t1_214.c_1_0, t1_214.c_1_1, t1_214.c_1_2, t1_214.c_1_3, t1_214.c_1_4, t1_214.c_1_5, t1_214.c_1_6, t1_214.c_1_7, t1_214.c_1_8, t1_214.c_1_9 FROM t1 AS t1_214) subt1 ON subt0.c_0_0 = subt1.c_1_1 AND subt0.c_0_0 >= subt1.c_1_1 AND subt0.c_0_13 != subt1.c_1_9 AND subt0.c_0_13 > subt1.c_1_9 WHERE  ( t0_213.c_0_14 ) = ( ('1969-12-18') IN ('1970-01-20', '1970-01-04')  ) ) ) ) IS NULL);
-- cardinality: 114

Expected behavior (Required)

Real behavior (Required)

StarRocks version (Required)

lvchenyang-maker commented 2 years ago

2.4验证未修复,结果太长，未粘贴，可到此版本复现

+----------------------------+
| current_version()          |
+----------------------------+
| BRANCH-2.4-RELEASE 5f5713d |
+----------------------------+