2356
-- test for sane behavior with noncanonical merge clauses, per bug #4926
2359
set enable_mergejoin = 1;
2360
set enable_hashjoin = 0;
2361
set enable_nestloop = 0;
2362
create temp table a (i integer);
2363
create temp table b (x integer, y integer);
2364
select * from a left join b on i = x and i = y and x = i;
2371
-- test NULL behavior of whole-row Vars, per bug #5025
2373
select t1.q2, count(t2.*)
2374
from int8_tbl t1 left join int8_tbl t2 on (t1.q2 = t2.q1)
2375
group by t1.q2 order by 1;
2377
-------------------+-------
2378
-4567890123456789 | 0
2381
4567890123456789 | 6
2384
select t1.q2, count(t2.*)
2385
from int8_tbl t1 left join (select * from int8_tbl) t2 on (t1.q2 = t2.q1)
2386
group by t1.q2 order by 1;
2388
-------------------+-------
2389
-4567890123456789 | 0
2392
4567890123456789 | 6
2395
select t1.q2, count(t2.*)
2396
from int8_tbl t1 left join (select * from int8_tbl offset 0) t2 on (t1.q2 = t2.q1)
2397
group by t1.q2 order by 1;
2399
-------------------+-------
2400
-4567890123456789 | 0
2403
4567890123456789 | 6
2406
select t1.q2, count(t2.*)
2407
from int8_tbl t1 left join
2408
(select q1, case when q2=1 then 1 else q2 end as q2 from int8_tbl) t2
2410
group by t1.q2 order by 1;
2412
-------------------+-------
2413
-4567890123456789 | 0
2416
4567890123456789 | 6