2420
-- test incorrect failure to NULL pulled-up subexpressions
2423
create temp table a (
2425
constraint a_pk primary key (code)
2427
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pk" for table "a"
2428
create temp table b (
2430
num integer not null,
2431
constraint b_pk primary key (a, num)
2433
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pk" for table "b"
2434
create temp table c (
2437
constraint c_pk primary key (name)
2439
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "c_pk" for table "c"
2440
insert into a (code) values ('p');
2441
insert into a (code) values ('q');
2442
insert into b (a, num) values ('p', 1);
2443
insert into b (a, num) values ('p', 2);
2444
insert into c (name, a) values ('A', 'p');
2445
insert into c (name, a) values ('B', 'q');
2446
insert into c (name, a) values ('C', null);
2447
select c.name, ss.code, ss.b_cnt, ss.const
2449
(select a.code, coalesce(b_grp.cnt, 0) as b_cnt, -1 as const
2451
(select count(1) as cnt, b.a from b group by b.a) as b_grp
2456
name | code | b_cnt | const
2457
------+------+-------+-------
2420
2465
-- test the corner cases FULL JOIN ON TRUE and FULL JOIN ON FALSE
2422
2467
select * from int4_tbl a full join int4_tbl b on true;