Wrong results of SELECT statements caused by OUTER JOIN.

[JZuming]

Describe what's wrong

The two semantically-equivalent SELECT statements should output the same results, but they did not.

Does it reproduce on recent release?

It can be reproduced in the latest version.

How to reproduce

Version: 23.5.1.1 (commit d509d7277b08627b05f7cc02049da904c53c507e)

Easy reproduce in ClickHouse fiddle: https://fiddle.clickhouse.com/de4ee763-9325-4d6a-9503-c8db94261340

Set up database

set join_use_nulls =  1;

create table t0 (c2 String, primary key(c2)) engine = MergeTree;
create table t1 (vkey UInt32, c8 String, primary key(vkey))engine = MergeTree;
create view t4 as 
select     
    ref_1.vkey as c_2_c48_2
  from 
    t0 as ref_0
      left outer join t1 as ref_1
      on (ref_0.c2 = ref_1.c8) ;
create table t5 (pkey UInt32, c52 UInt32, c56 String, primary key(pkey))engine = MergeTree;

insert into t0 values (null);
insert into t0 values ('');
insert into t1 values (59, '');
insert into t5 values (12000, null, '');
insert into t5 values  (22000, null, null);
insert into t5 values  (24000, 14, 'YLq?');
insert into t5 values  (30000, 0, '-');
insert into t5 values  (33000, null, 'Wm@c');
insert into t5 values (37000, 0, 'IB');
insert into t5 values (38000,  59, '');
insert into t5 values (56000, 0, null);
insert into t5 values (64000, 74, '');
insert into t5 values (72000, 36, 'q:/');
insert into t5 values (79000, null, '[P');
insert into t5 values (82000, 0, 'V-Qr');
insert into t5 values (88000, 44, '1Z ');
insert into t5 values (94000, 15, 'G]A5');
insert into t5 values (96000, -0, 'C8');
insert into t5 values (97000, 56,  null);

SELECT statement 1

select
    count(*)
  from
    t5 as ref_2
      left outer join (select
            ref_3.c_2_c48_2 as c_6_c185_6
          from
            t4 as ref_3
          ) as subq_1
      on (ref_2.c52 = subq_1.c_6_c185_6 )
  where intExp2(ref_2.pkey) <= 
      (case when ((subq_1.c_6_c185_6 = 1) and (not (subq_1.c_6_c185_6 = 1))) then 0 else hiveHash(ref_2.c56) end);

Because (subq_1.c_6_c185_6 = 1) and (not (subq_1.c_6_c185_6 = 1)) are contradictory, ((subq_1.c_6_c185_6 = 1) and (not (subq_1.c_6_c185_6 = 1))) must be not true. Therefore, (case when ((subq_1.c_6_c185_6 = 1) and (not (subq_1.c_6_c185_6 = 1))) then 0 else hiveHash(ref_2.c56) end) can be replaced with hiveHash(ref_2.c56), and I get the semantically-equivalent SELECT statement 2:

SELECT statement 2

select
    count(*)
  from
    t5 as ref_2
      left outer join (select
            ref_3.c_2_c48_2 as c_6_c185_6
          from
            t4 as ref_3
          ) as subq_1
      on (ref_2.c52 = subq_1.c_6_c185_6 )
  where intExp2(ref_2.pkey) <= hiveHash(ref_2.c56);

Expected behavior

The two SELECT statements output the same results.

Actual behavior

SELECT statement 1 outputs:

┌─count()─┐
│      16 │
└─────────┘

1 row in set. Elapsed: 0.029 sec.

SELECT statement 2 outputs:

┌─count()─┐
│      17 │
└─────────┘

1 row in set. Elapsed: 0.031 sec.

They are different.

Additional context

The earliest reproducible version is 21.11 in fiddle.

[den-crane]

It seems intExp2 produces the random garbage and corrupts memory

https://fiddle.clickhouse.com/11f70769-8edc-4a50-8f7a-87fcdaace59d

plus it could be one more problem in query_plan_filter_push_down, but it can be due to corruption https://fiddle.clickhouse.com/f5c53f4b-81c1-4d57-9f62-fb36e67ae7ed

[den-crane]

It's seems the only problem is that intExp2 produces garbage in case of huge arguments. You can use exp2 instead.

Also it's unclear what intExp2 should produce in case of 12000 as an argument.

pow(2,12000)

2290593203500326442498254071102877992464615830839054768055123450544313385107740379157387758658057318635099533562444284837656640890034066154573412691609539346515313162728959709610996486195486636741656944283948869330648470173371350813320809268809952407079715398039210502009557335794366205566676730638553849508752967747099096815391878861378575138900522123854153640002335525179230941551480812783648467474496157878125226171395342006341679075520576304970776016746818912261453204962575441115371836944715689550507388254572127394351748165073340540193304452987980296508746618030728963410359112463410918483243904968689085394227988296554063613709807896975047594167461331023628146001054998291892885044803396603840787819652704471574743685338683157788002035621474121034155871572968019805251898240972502308488120023873650020272835722752488449634887364713943526031912848227248826190464847696594892838239669305251912416877251755339086929524537835982837023543516588536916371046489422031070150882793338052642997925998158019209229038981588717128926097153382729134531621865313978608581541705515982751534447133263250347818367765137031003609793889758575377908303501066776654831199960534747537034342674382534000538109978641872766097082093090380663944422789696913654890020232228508254497953096787063044370098338492177314930216742550624871750833859476679189509568060273234671293915325999081148939130328420650376019730541961524092173016464047938013691439667184320360598111877751362775572507922668374235979682286834034089138475154767372727122932222887885208321879666030597579772887782987686468159942599573254088749600987758158350339985951647512170869758074602947384280183385924857960341339199730774135336869491956368516611377674237208178041919106870280789033916144099126661387307752660057804524225302437317858452782485229505751376109394446472280555391177171643150592302864136987885783315401782239495790781650110059887274595946783100447198954930537574190738099064718222518825147478490657161167548497523333968812279491147511996563545946244733928978286727530857216210239434430620144907278084466853892944205719869706010787649500341806904790181420256733072612769503473201816461274039931292984401423199725434093017076346603772533741966291435995993488135271310131253463508530232037816302115328138866864301429396394767471856713166350435955804654725436951706056632361702749907044372801683830358699136529946432620564283934315040535048881017547202538380788919253939272110382634932825138554381697728238695648751406557888234747518138465426828255208381310069117625217360239526199430454346435033842859303165451350797675107176380424351271898393077912093765743451201386745554882022414807362737862360998011111307606401895470442072037617747470820243516866198003957569584101060804661356296500120146645677141557786648630936176345539004262109110167208910075825348801584001722407106797155866549239788534766072563138170840191279476853418537351879721277733449450507730318950504047034492250690387355696568657085290734466234786952456543122517479114466613670208736084231367154565776282269608990568021682799022786745086696738347816102210900054189076993778672770596482065860737514336417130117445117040161323349063389003771777472580944833242545989973822564674460973839015552175709642226193756923409669234790206301159076383049447801135255878205328275264329908764826799101532490749635380687710149440400602422623804497742682401904233153226013937331725013335198352712395550422922110105171367715419816662500131430427440349387764312765762487031730568756628410847516600013244143506207393041830738377668972502903711649967733818943578923725532823256616542654631382911359993958629376

https://www.wolframalpha.com/input?i=pow%282%2C12000%29