cnosdb
cnosdb copied to clipboard
cnosdb
[BUG][sqlancer]Query results are inconsistent
Describe the bug
Query results are inconsistent version:cnosdb 2.4.1, revision 9b25565a6c8ed5a12726475c0be6cb099ef980b2 deploy:3meta+2querytskv Expect the results of the two queries to be consistent
To Reproduce
CREATE TABLE IF NOT EXISTS m2(f0 DOUBLE , TAGS(t0));
INSERT m2(TIME, t0, f0) VALUES(CAST (5527508865659092033 AS TIMESTAMP), '', 0.89517588331997111072979578239028342068195343017578125), (CAST (3370129766534803024 AS TIMESTAMP), 'q',0.67819301430531153673797462033689953386783599853515625), (CAST (5527508865659092033 AS TIMESTAMP), '3370129766534803024', 0.86803751284729713244558979567955248057842254638671875);
INSERT m2(TIME, t0, f0) VALUES(CAST (7691908678682285434 AS TIMESTAMP), '', 0.3047096532643067678947090826113708317279815673828125);
INSERT m2(TIME, t0, f0) VALUES(CAST (2137128400002393761 AS TIMESTAMP), 'gxr?a', 0.77609738289854079962282185078947804868221282958984375);
query:
5:
SELECT ALL f0
FROM m2
WHERE NOT (m2.t0 LIKE 'YF'
OR 4089716184911369747 IN (1776523607, -1810642242));
4:
SELECT SUM(count)
FROM (
SELECT CAST(NOT (m2.t0 LIKE 'YF'
OR 4089716184911369747 IN (1776523607, -1810642242)) AS BIGINT) AS count
FROM m2
) res;
Expected behavior
No response
Additional context
No response
DROP DATABASE IF EXISTS database40;
2458 CREATE DATABASE database40;
2459 CREATE TABLE m0(f0 DOUBLE , TAGS(t0));
2460 CREATE TABLE m1(f0 BIGINT , TAGS(t0));
2461 CREATE TABLE m2(f0 STRING , TAGS(t0, t1));
2462 CREATE TABLE m3(f0 DOUBLE , TAGS(t0));
2463 INSERT m0(TIME, t0, f0) VALUES(CAST (7560611922813582569 AS TIMESTAMP), '#?XG*', 0.94473904808871622673649426360498182475566864013 671875);
2464 INSERT m2(TIME, t0, f0, t1) VALUES(CAST (702811672581843424 AS TIMESTAMP), 'sP', 'U/?f#gPOu', '0_?*K%DL');
2465 INSERT m2(TIME, t0, f0, t1) VALUES(CAST (4353733532353349423 AS TIMESTAMP), '', 'e7?/MD?h', NULL), (CAST (7560611922813582569 AS T IMESTAMP), 'wY#', '-1681154822', '?{E1|P'), (CAST (911089881132274762 AS TIMESTAMP), '?{E1|P', '>f90U', 'OE8X');
2466 INSERT m0(TIME, t0, f0) VALUES(CAST (702811672581843424 AS TIMESTAMP), '1062099219', 911089881132274816);
2467 INSERT m1(TIME, t0, f0) VALUES(CAST (5078017830014740325 AS TIMESTAMP), 'q*uXgO(', NULL), (CAST (7295837062891169576 AS TIMESTAMP) , '0.9447390480887162', 1819106613), (CAST (5078017830014740325 AS TIMESTAMP), 'U/?f#gPOu', 802860071);
2468 INSERT m0(TIME, t0, f0) VALUES(CAST (5914775519342431841 AS TIMESTAMP), '_9', 0.49598204735870898840488507630652748048305511474609 375);
2469 INSERT m3(TIME, t0, f0) VALUES(CAST (2068732521885852585 AS TIMESTAMP), 'rk<sS', 0.71613828440106908068685243051731958985328674316 40625);
2470 INSERT m1(TIME, t0, f0) VALUES(CAST (4353733532353349423 AS TIMESTAMP), 'l ', 1195044575), (CAST (4353733532353349423 AS TIME STAMP), 'wFy?', -1681154822), (CAST (397718760900196080 AS TIMESTAMP), '|{', 5914775519342431841);
2471 INSERT m3(TIME, t0, f0) VALUES(CAST (2390309447048135199 AS TIMESTAMP), '2390309447048135199', 0.940459360161079116480209449946414 6792888641357421875);
2472 INSERT m0(TIME, t0, f0) VALUES(CAST (8594291917606645066 AS TIMESTAMP), '', 0.3992288266381970318619210047472734004259109497070312 5);
2473 INSERT m3(TIME, t0, f0) VALUES(CAST (7035639907309180358 AS TIMESTAMP), '/E4xzS!', 0.083037212871210352993500691809458658099174499 51171875), (CAST (702811672581843424 AS TIMESTAMP), '??wer1', 0.62367290322845037042753801870276220142841339111328125), (CAST (507 8017830014740325 AS TIMESTAMP), '7035639907309180358', 0.9404593601610791164802094499464146792888641357421875);
INSERT m3(TIME, t0, f0) VALUES(CAST (1062099219 AS TIMESTAMP), '', 0.17487612747641934074493974549113772809505462646484375), (CAST (1195044575 AS TIMESTAMP), '', 2068732521885852672), (CAST (4517282738681377775 AS TIMESTAMP), '&?', 0.23671150881215019534664634 193177334964275360107421875);
2475 INSERT m2(TIME, t0, f0, t1) VALUES(CAST (3011140665339531554 AS TIMESTAMP), 'VE', '', ')\GC{B t'), (CAST (7295837062891169576 AS T IMESTAMP), 'SK?/^^M?e()j', '7560611922813582569', '\^MiG \^>*'), (CAST (5425749832351819353 AS TIMESTAMP), '93', '0.495982047 358709', '^(');
2476 INSERT m1(TIME, t0, f0) VALUES(CAST (3011140665339531554 AS TIMESTAMP), '3011140665339531554', -1119863231);
2477 INSERT m0(TIME, t0, f0) VALUES(CAST (5425749832351819353 AS TIMESTAMP), '4353733532353349423', 0.506919893145408750889657767402241 00649356842041015625);
2478 INSERT m0(TIME, t0, f0) VALUES(CAST (8835573370910159358 AS TIMESTAMP), 'm?', 0.94473904808871622673649426360498182475566864013671 875), (CAST (7043795396058817312 AS TIMESTAMP), '3011140665339531554', 7560611922813582336), (CAST (2029504697667206562 AS TIMESTA MP), '', 0.6209127097695255059761620941571891307830810546875);
2479 INSERT m0(TIME, t0, f0) VALUES(CAST (7560611922813582569 AS TIMESTAMP), 'G?I
2480 ', 0);
2481 INSERT m3(TIME, t0, f0) VALUES(CAST (4517282738681377775 AS TIMESTAMP), '', 0.335363554136893338153413424151949584484100341796875) , (CAST (3395146115527449916 AS TIMESTAMP), '1062099219', 0.44095050936826185594696880798437632620334625244140625), (CAST (3527327 325542162082 AS TIMESTAMP), '397718760900196080', 0.95079887926271988707327409429126419126987457275390625);
2482 INSERT m3(TIME, t0, f0) VALUES(CAST (7009731682965442788 AS TIMESTAMP), '', 0.859756439156855289951408849447034299373626708984375) , (CAST (4353733532353349423 AS TIMESTAMP), '^aac', 0.95079887926271988707327409429126419126987457275390625), (CAST (3016054893039 511301 AS TIMESTAMP), '802860071', 0.32968049227520135335822715205722488462924957275390625);
2483 INSERT m0(TIME, t0, f0) VALUES(CAST (7560611922813582569 AS TIMESTAMP), '^K', 0.0041016921083727453378742211498320102691650390625) , (CAST (7035639907309180358 AS TIMESTAMP), '', 0.6209127097695255059761620941571891307830810546875), (CAST (2249805080221391376 A S TIMESTAMP), 'M|UD', 397718760900196096);
2484 INSERT m2(TIME, t0, f0, t1) VALUES(CAST (7171080575430022603 AS TIMESTAMP), 'T.+*x&?b', '7171080575430022603', '^MB'), (CAST (7009 731682965442788 AS TIMESTAMP), '', 'n''7R+tOVH', '92'), (CAST (3057746886904539840 AS TIMESTAMP), '', '&?', '702811672581843424');
2485 INSERT m3(TIME, t0, f0) VALUES(CAST (7043795396058817312 AS TIMESTAMP), '', 0.9155361294770848479274150122364517301321029663085937 5), (CAST (7171080575430022603 AS TIMESTAMP), 'XO+nu-D\b', 0.3651020025196685114821093520731665194034576416015625), (CAST (7753631 386048892579 AS TIMESTAMP), 'X?(m*!2z', 0.8453916129980971749802165504661388695240020751953125);
2486 INSERT m2(TIME, t0, f0, t1) VALUES(CAST (1426092843442891671 AS TIMESTAMP), '2106416007', '&''U]rvq5', ''), (CAST (128699764809543 6015 AS TIMESTAMP), 'C
2487 ', '5914775519342431841', '0.44095050936826186'), (CAST (2249831353883593120 AS TIMESTAMP), 'FALSE', 'obt%', '397718760900196080') , (CAST (3883603772997213301 AS TIMESTAMP), '', '''M_?Q3*e#', '+0k'), (CAST (1073061598027385446 AS TIMESTAMP), 'Nv', '', '');
INSERT m3(TIME, t0, f0) VALUES(CAST (6757832219388447659 AS TIMESTAMP), '', 3395146115527450112);
2489 INSERT m0(TIME, t0, f0) VALUES(CAST (45312237467104085 AS TIMESTAMP), '^k', 0.3651020025196685114821093520731665194034576416015625 );
2490 INSERT m0(TIME, t0, f0) VALUES(CAST (6309720971339180267 AS TIMESTAMP), 'LC', 0.66094709628098879949931188093614764511585235595703 125);
-- SELECT f0 FROM m3 WHERE ('') BETWEEN (m3.t0) AND ('-812925097');
2492 -- count: 4
2493 -- SELECT SUM(count) FROM (SELECT ALL CAST( ('') BETWEEN (m3.t0) AND ('-812925097') AS BIGINT) as count FROM m3) as res;
2494 -- count: 1;
This error is caused by the tag_scan Execution plan used in the query, tag_scan Execution plan filters the same tag values, resulting in fewer rows, similar to the issue case: https://github.com/cnosdb/cnosdb/issues/2053
TableScan has time and field columns, so it is not converted to TagScan
Only the tag column is available and is therefore converted to TagScan
