*** a/contrib/btree_gist/expected/float4.out --- b/contrib/btree_gist/expected/float4.out *************** *** 78,84 **** SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3; ---------------------------------------------------- Limit -> Index Only Scan using float4idx on float4tmp ! Order By: (a <-> (-179)::real) (3 rows) SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3; --- 78,84 ---- ---------------------------------------------------- Limit -> Index Only Scan using float4idx on float4tmp ! Order By: (a <-> '-179'::real) (3 rows) SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3; *** a/contrib/btree_gist/expected/float8.out --- b/contrib/btree_gist/expected/float8.out *************** *** 78,84 **** SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3; ----------------------------------------------------- Limit -> Index Only Scan using float8idx on float8tmp ! Order By: (a <-> (-1890)::double precision) (3 rows) SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3; --- 78,84 ---- ----------------------------------------------------- Limit -> Index Only Scan using float8idx on float8tmp ! Order By: (a <-> '-1890'::double precision) (3 rows) SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3; *** a/contrib/btree_gist/expected/int2.out --- b/contrib/btree_gist/expected/int2.out *************** *** 78,84 **** SELECT a, a <-> '237' FROM int2tmp ORDER BY a <-> '237' LIMIT 3; ------------------------------------------------ Limit -> Index Only Scan using int2idx on int2tmp ! Order By: (a <-> 237::smallint) (3 rows) SELECT a, a <-> '237' FROM int2tmp ORDER BY a <-> '237' LIMIT 3; --- 78,84 ---- ------------------------------------------------ Limit -> Index Only Scan using int2idx on int2tmp ! Order By: (a <-> '237'::smallint) (3 rows) SELECT a, a <-> '237' FROM int2tmp ORDER BY a <-> '237' LIMIT 3; *** a/contrib/btree_gist/expected/int8.out --- b/contrib/btree_gist/expected/int8.out *************** *** 74,84 **** SELECT count(*) FROM int8tmp WHERE a > 464571291354841::int8; EXPLAIN (COSTS OFF) SELECT a, a <-> '464571291354841' FROM int8tmp ORDER BY a <-> '464571291354841' LIMIT 3; ! QUERY PLAN ! --------------------------------------------------- Limit -> Index Only Scan using int8idx on int8tmp ! Order By: (a <-> 464571291354841::bigint) (3 rows) SELECT a, a <-> '464571291354841' FROM int8tmp ORDER BY a <-> '464571291354841' LIMIT 3; --- 74,84 ---- EXPLAIN (COSTS OFF) SELECT a, a <-> '464571291354841' FROM int8tmp ORDER BY a <-> '464571291354841' LIMIT 3; ! QUERY PLAN ! ----------------------------------------------------- Limit -> Index Only Scan using int8idx on int8tmp ! Order By: (a <-> '464571291354841'::bigint) (3 rows) SELECT a, a <-> '464571291354841' FROM int8tmp ORDER BY a <-> '464571291354841' LIMIT 3; *** a/contrib/btree_gist/expected/numeric.out --- b/contrib/btree_gist/expected/numeric.out *************** *** 190,201 **** SELECT count(*) FROM numerictmp WHERE a > 0 ; SET enable_bitmapscan=off; EXPLAIN (COSTS OFF) SELECT * FROM numerictmp WHERE a BETWEEN 1 AND 300 ORDER BY a; ! QUERY PLAN ! ----------------------------------------------------------------- Sort Sort Key: a -> Index Only Scan using numericidx on numerictmp ! Index Cond: ((a >= 1::numeric) AND (a <= 300::numeric)) (4 rows) SELECT * FROM numerictmp WHERE a BETWEEN 1 AND 300 ORDER BY a; --- 190,201 ---- SET enable_bitmapscan=off; EXPLAIN (COSTS OFF) SELECT * FROM numerictmp WHERE a BETWEEN 1 AND 300 ORDER BY a; ! QUERY PLAN ! --------------------------------------------------------------------- Sort Sort Key: a -> Index Only Scan using numericidx on numerictmp ! Index Cond: ((a >= '1'::numeric) AND (a <= '300'::numeric)) (4 rows) SELECT * FROM numerictmp WHERE a BETWEEN 1 AND 300 ORDER BY a; *** a/contrib/postgres_fdw/expected/postgres_fdw.out --- b/contrib/postgres_fdw/expected/postgres_fdw.out *************** *** 874,880 **** SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1; Output: c1, c2, c3, c4, c5, c6, c7, c8 -> Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 ! Filter: (t1.tableoid = 1259::oid) Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" (6 rows) --- 874,880 ---- Output: c1, c2, c3, c4, c5, c6, c7, c8 -> Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 ! Filter: (t1.tableoid = '1259'::oid) Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" (6 rows) *** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *************** *** 15,20 **** --- 15,21 ---- */ #include "postgres.h" + #include #include #include *************** *** 8018,8024 **** get_const_expr(Const *constval, deparse_context *context, int showtype) Oid typoutput; bool typIsVarlena; char *extval; - bool isfloat = false; bool needlabel; if (constval->constisnull) --- 8019,8024 ---- *************** *** 8043,8090 **** get_const_expr(Const *constval, deparse_context *context, int showtype) extval = OidOutputFunctionCall(typoutput, constval->constvalue); switch (constval->consttype) { - case INT2OID: case INT4OID: ! case INT8OID: ! case OIDOID: ! case FLOAT4OID: ! case FLOAT8OID: case NUMERICOID: { ! /* ! * These types are printed without quotes unless they contain ! * values that aren't accepted by the scanner unquoted (e.g., ! * 'NaN'). Note that strtod() and friends might accept NaN, ! * so we can't use that to test. ! * ! * In reality we only need to defend against infinity and NaN, ! * so we need not get too crazy about pattern matching here. ! * ! * There is a special-case gotcha: if the constant is signed, ! * we need to parenthesize it, else the parser might see a ! * leading plus/minus as binding less tightly than adjacent ! * operators --- particularly, the cast that we might attach ! * below. ! */ ! if (strspn(extval, "0123456789+-eE.") == strlen(extval)) ! { ! if (extval[0] == '+' || extval[0] == '-') ! appendStringInfo(buf, "(%s)", extval); ! else ! appendStringInfoString(buf, extval); ! if (strcspn(extval, "eE.") != strlen(extval)) ! isfloat = true; /* it looks like a float */ ! } ! else ! appendStringInfo(buf, "'%s'", extval); } break; case BITOID: case VARBITOID: appendStringInfo(buf, "B'%s'", extval); break; case BOOLOID: --- 8043,8104 ---- extval = OidOutputFunctionCall(typoutput, constval->constvalue); + /* + * Determine the right representation for the literal, and whether the + * ::typename label is needed (for showtype == 0). + * + * XXX this code has to be kept in sync with the behavior of the parser, + * especially make_const. + */ switch (constval->consttype) { case INT4OID: ! ! /* ! * INT4 can be printed without any decoration, unless it is ! * negative; in that case print it as '-nnn'::integer to ensure ! * that the output will re-parse as a constant, not as a constant ! * plus operator. In most cases we could get away with printing ! * (-nnn) instead, because of the way that gram.y handles negative ! * literals; but that doesn't work for INT_MIN, and it doesn't ! * seem that much prettier anyway. ! */ ! if (extval[0] != '-') ! { ! appendStringInfoString(buf, extval); ! needlabel = false; ! } ! else ! { ! appendStringInfo(buf, "'%s'", extval); ! needlabel = true; /* we must attach a cast */ ! } ! break; ! case NUMERICOID: + + /* + * NUMERIC can be printed without quotes if it looks like a float + * constant (not an integer, and not Infinity or NaN) and doesn't + * have a leading sign (for the same reason as for INT4). + */ + if (isdigit((unsigned char) extval[0]) && + strcspn(extval, "eE.") != strlen(extval)) { ! appendStringInfoString(buf, extval); ! needlabel = (constval->consttypmod >= 0); ! } ! else ! { ! appendStringInfo(buf, "'%s'", extval); ! needlabel = true; /* we must attach a cast */ } break; case BITOID: case VARBITOID: appendStringInfo(buf, "B'%s'", extval); + needlabel = true; break; case BOOLOID: *************** *** 8092,8136 **** get_const_expr(Const *constval, deparse_context *context, int showtype) appendStringInfoString(buf, "true"); else appendStringInfoString(buf, "false"); break; - default: - simple_quote_literal(buf, extval); - break; - } - - pfree(extval); - - if (showtype < 0) - return; - - /* - * For showtype == 0, append ::typename unless the constant will be - * implicitly typed as the right type when it is read in. - * - * XXX this code has to be kept in sync with the behavior of the parser, - * especially make_const. - */ - switch (constval->consttype) - { - case BOOLOID: - case INT4OID: case UNKNOWNOID: ! /* These types can be left unlabeled */ needlabel = false; break; - case NUMERICOID: - /* - * Float-looking constants will be typed as numeric, but if - * there's a specific typmod we need to show it. - */ - needlabel = !isfloat || (constval->consttypmod >= 0); - break; default: needlabel = true; break; } if (needlabel || showtype > 0) appendStringInfo(buf, "::%s", format_type_with_typemod(constval->consttype, --- 8106,8127 ---- appendStringInfoString(buf, "true"); else appendStringInfoString(buf, "false"); + needlabel = false; break; case UNKNOWNOID: ! simple_quote_literal(buf, extval); needlabel = false; break; default: + simple_quote_literal(buf, extval); needlabel = true; break; } + + pfree(extval); + if (needlabel || showtype > 0) appendStringInfo(buf, "::%s", format_type_with_typemod(constval->consttype, *** a/src/test/regress/expected/equivclass.out --- b/src/test/regress/expected/equivclass.out *************** *** 104,114 **** set enable_mergejoin = off; -- explain (costs off) select * from ec0 where ff = f1 and f1 = '42'::int8; ! QUERY PLAN ! ---------------------------------- Index Scan using ec0_pkey on ec0 ! Index Cond: (ff = 42::bigint) ! Filter: (f1 = 42::bigint) (3 rows) explain (costs off) --- 104,114 ---- -- explain (costs off) select * from ec0 where ff = f1 and f1 = '42'::int8; ! QUERY PLAN ! ----------------------------------- Index Scan using ec0_pkey on ec0 ! Index Cond: (ff = '42'::bigint) ! Filter: (f1 = '42'::bigint) (3 rows) explain (costs off) *************** *** 139,150 **** explain (costs off) explain (costs off) select * from ec1, ec2 where ff = x1 and ff = '42'::int8; ! QUERY PLAN ! --------------------------------------------------------------- Nested Loop Join Filter: (ec1.ff = ec2.x1) -> Index Scan using ec1_pkey on ec1 ! Index Cond: ((ff = 42::bigint) AND (ff = 42::bigint)) -> Seq Scan on ec2 (5 rows) --- 139,150 ---- explain (costs off) select * from ec1, ec2 where ff = x1 and ff = '42'::int8; ! QUERY PLAN ! ------------------------------------------------------------------- Nested Loop Join Filter: (ec1.ff = ec2.x1) -> Index Scan using ec1_pkey on ec1 ! Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint)) -> Seq Scan on ec2 (5 rows) *************** *** 161,174 **** explain (costs off) explain (costs off) select * from ec1, ec2 where ff = x1 and '42'::int8 = x1; ! QUERY PLAN ! ---------------------------------------- Nested Loop Join Filter: (ec1.ff = ec2.x1) -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = 42::bigint) -> Seq Scan on ec2 ! Filter: (42::bigint = x1) (6 rows) explain (costs off) --- 161,174 ---- explain (costs off) select * from ec1, ec2 where ff = x1 and '42'::int8 = x1; ! QUERY PLAN ! ----------------------------------------- Nested Loop Join Filter: (ec1.ff = ec2.x1) -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = '42'::bigint) -> Seq Scan on ec2 ! Filter: ('42'::bigint = x1) (6 rows) explain (costs off) *************** *** 210,216 **** explain (costs off) ----------------------------------------------------- Nested Loop -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = 42::bigint) -> Append -> Index Scan using ec1_expr2 on ec1 ec1_1 Index Cond: (((ff + 2) + 1) = ec1.f1) --- 210,216 ---- ----------------------------------------------------- Nested Loop -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = '42'::bigint) -> Append -> Index Scan using ec1_expr2 on ec1 ec1_1 Index Cond: (((ff + 2) + 1) = ec1.f1) *************** *** 229,248 **** explain (costs off) union all select ff + 4 as x from ec1) as ss1 where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1; ! QUERY PLAN ! --------------------------------------------------------------- Nested Loop Join Filter: ((((ec1_1.ff + 2) + 1)) = ec1.f1) -> Index Scan using ec1_pkey on ec1 ! Index Cond: ((ff = 42::bigint) AND (ff = 42::bigint)) Filter: (ff = f1) -> Append -> Index Scan using ec1_expr2 on ec1 ec1_1 ! Index Cond: (((ff + 2) + 1) = 42::bigint) -> Index Scan using ec1_expr3 on ec1 ec1_2 ! Index Cond: (((ff + 3) + 1) = 42::bigint) -> Index Scan using ec1_expr4 on ec1 ec1_3 ! Index Cond: ((ff + 4) = 42::bigint) (12 rows) explain (costs off) --- 229,248 ---- union all select ff + 4 as x from ec1) as ss1 where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1; ! QUERY PLAN ! ------------------------------------------------------------------- Nested Loop Join Filter: ((((ec1_1.ff + 2) + 1)) = ec1.f1) -> Index Scan using ec1_pkey on ec1 ! Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint)) Filter: (ff = f1) -> Append -> Index Scan using ec1_expr2 on ec1 ec1_1 ! Index Cond: (((ff + 2) + 1) = '42'::bigint) -> Index Scan using ec1_expr3 on ec1 ec1_2 ! Index Cond: (((ff + 3) + 1) = '42'::bigint) -> Index Scan using ec1_expr4 on ec1 ec1_3 ! Index Cond: ((ff + 4) = '42'::bigint) (12 rows) explain (costs off) *************** *** 265,271 **** explain (costs off) Nested Loop -> Nested Loop -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = 42::bigint) -> Append -> Index Scan using ec1_expr2 on ec1 ec1_1 Index Cond: (((ff + 2) + 1) = ec1.f1) --- 265,271 ---- Nested Loop -> Nested Loop -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = '42'::bigint) -> Append -> Index Scan using ec1_expr2 on ec1 ec1_1 Index Cond: (((ff + 2) + 1) = ec1.f1) *************** *** 321,327 **** explain (costs off) -> Sort Sort Key: ec1.f1 USING < -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = 42::bigint) (20 rows) -- check partially indexed scan --- 321,327 ---- -> Sort Sort Key: ec1.f1 USING < -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = '42'::bigint) (20 rows) -- check partially indexed scan *************** *** 341,347 **** explain (costs off) ----------------------------------------------------- Nested Loop -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = 42::bigint) -> Append -> Index Scan using ec1_expr2 on ec1 ec1_1 Index Cond: (((ff + 2) + 1) = ec1.f1) --- 341,347 ---- ----------------------------------------------------- Nested Loop -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = '42'::bigint) -> Append -> Index Scan using ec1_expr2 on ec1 ec1_1 Index Cond: (((ff + 2) + 1) = ec1.f1) *************** *** 378,383 **** explain (costs off) -> Sort Sort Key: ec1.f1 USING < -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = 42::bigint) (14 rows) --- 378,383 ---- -> Sort Sort Key: ec1.f1 USING < -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = '42'::bigint) (14 rows) *** a/src/test/regress/expected/join.out --- b/src/test/regress/expected/join.out *************** *** 2544,2559 **** SELECT qq, unique1 ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2 USING (qq) INNER JOIN tenk1 c ON qq = unique2; ! QUERY PLAN ! ------------------------------------------------------------------------------------------------------- Nested Loop -> Hash Full Join ! Hash Cond: (COALESCE(a.q1, 0::bigint) = COALESCE(b.q2, (-1)::bigint)) -> Seq Scan on int8_tbl a -> Hash -> Seq Scan on int8_tbl b -> Index Scan using tenk1_unique2 on tenk1 c ! Index Cond: (unique2 = COALESCE((COALESCE(a.q1, 0::bigint)), (COALESCE(b.q2, (-1)::bigint)))) (8 rows) SELECT qq, unique1 --- 2544,2559 ---- ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2 USING (qq) INNER JOIN tenk1 c ON qq = unique2; ! QUERY PLAN ! --------------------------------------------------------------------------------------------------------- Nested Loop -> Hash Full Join ! Hash Cond: (COALESCE(a.q1, '0'::bigint) = COALESCE(b.q2, '-1'::bigint)) -> Seq Scan on int8_tbl a -> Hash -> Seq Scan on int8_tbl b -> Index Scan using tenk1_unique2 on tenk1 c ! Index Cond: (unique2 = COALESCE((COALESCE(a.q1, '0'::bigint)), (COALESCE(b.q2, '-1'::bigint)))) (8 rows) SELECT qq, unique1 *************** *** 3003,3012 **** select * from ) ss where fault = 122 order by fault; ! QUERY PLAN ! ----------------------------------------------------------------- Nested Loop Left Join ! Filter: ((COALESCE(tenk1.unique1, (-1)) + int8_tbl.q1) = 122) -> Seq Scan on int8_tbl -> Index Scan using tenk1_unique2 on tenk1 Index Cond: (int8_tbl.q2 = unique2) --- 3003,3012 ---- ) ss where fault = 122 order by fault; ! QUERY PLAN ! -------------------------------------------------------------------------- Nested Loop Left Join ! Filter: ((COALESCE(tenk1.unique1, '-1'::integer) + int8_tbl.q1) = 122) -> Seq Scan on int8_tbl -> Index Scan using tenk1_unique2 on tenk1 Index Cond: (int8_tbl.q2 = unique2) *************** *** 4012,4025 **** explain (verbose, costs off) select * from int8_tbl a left join lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1; ! QUERY PLAN ! ---------------------------------------------------------------- Nested Loop Left Join ! Output: a.q1, a.q2, b.q1, b.q2, (COALESCE(a.q2, 42::bigint)) -> Seq Scan on public.int8_tbl a Output: a.q1, a.q2 -> Seq Scan on public.int8_tbl b ! Output: b.q1, b.q2, COALESCE(a.q2, 42::bigint) Filter: (a.q2 = b.q1) (7 rows) --- 4012,4025 ---- select * from int8_tbl a left join lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1; ! QUERY PLAN ! ------------------------------------------------------------------ Nested Loop Left Join ! Output: a.q1, a.q2, b.q1, b.q2, (COALESCE(a.q2, '42'::bigint)) -> Seq Scan on public.int8_tbl a Output: a.q1, a.q2 -> Seq Scan on public.int8_tbl b ! Output: b.q1, b.q2, COALESCE(a.q2, '42'::bigint) Filter: (a.q2 = b.q1) (7 rows) *************** *** 4235,4266 **** select * from lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 ) on c.q2 = ss2.q1, lateral (select ss2.y offset 0) ss3; ! QUERY PLAN ! ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop ! Output: c.q1, c.q2, a.q1, a.q2, b.q1, (COALESCE(b.q2, 42::bigint)), d.q1, (COALESCE((COALESCE(b.q2, 42::bigint)), d.q2)), ((COALESCE((COALESCE(b.q2, 42::bigint)), d.q2))) -> Hash Right Join ! Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, 42::bigint)), (COALESCE((COALESCE(b.q2, 42::bigint)), d.q2)) Hash Cond: (d.q1 = c.q2) -> Nested Loop ! Output: a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, 42::bigint)), (COALESCE((COALESCE(b.q2, 42::bigint)), d.q2)) -> Hash Left Join ! Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, 42::bigint)) Hash Cond: (a.q2 = b.q1) -> Seq Scan on public.int8_tbl a Output: a.q1, a.q2 -> Hash ! Output: b.q1, (COALESCE(b.q2, 42::bigint)) -> Seq Scan on public.int8_tbl b ! Output: b.q1, COALESCE(b.q2, 42::bigint) -> Seq Scan on public.int8_tbl d ! Output: d.q1, COALESCE((COALESCE(b.q2, 42::bigint)), d.q2) -> Hash Output: c.q1, c.q2 -> Seq Scan on public.int8_tbl c Output: c.q1, c.q2 -> Result ! Output: (COALESCE((COALESCE(b.q2, 42::bigint)), d.q2)) (24 rows) -- case that breaks the old ph_may_need optimization --- 4235,4266 ---- lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 ) on c.q2 = ss2.q1, lateral (select ss2.y offset 0) ss3; ! QUERY PLAN ! ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop ! Output: c.q1, c.q2, a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)), d.q1, (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)), ((COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2))) -> Hash Right Join ! Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, '42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) Hash Cond: (d.q1 = c.q2) -> Nested Loop ! Output: a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, '42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) -> Hash Left Join ! Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)) Hash Cond: (a.q2 = b.q1) -> Seq Scan on public.int8_tbl a Output: a.q1, a.q2 -> Hash ! Output: b.q1, (COALESCE(b.q2, '42'::bigint)) -> Seq Scan on public.int8_tbl b ! Output: b.q1, COALESCE(b.q2, '42'::bigint) -> Seq Scan on public.int8_tbl d ! Output: d.q1, COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2) -> Hash Output: c.q1, c.q2 -> Seq Scan on public.int8_tbl c Output: c.q1, c.q2 -> Result ! Output: (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) (24 rows) -- case that breaks the old ph_may_need optimization *** a/src/test/regress/expected/rowtypes.out --- b/src/test/regress/expected/rowtypes.out *************** *** 284,293 **** ERROR: cannot compare dissimilar column types bigint and integer at record colu explain (costs off) select * from int8_tbl i8 where i8 in (row(123,456)::int8_tbl, '(4567890123456789,123)'); ! QUERY PLAN ! ------------------------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 ! Filter: (i8.* = ANY (ARRAY[ROW(123::bigint, 456::bigint)::int8_tbl, '(4567890123456789,123)'::int8_tbl])) (2 rows) select * from int8_tbl i8 --- 284,293 ---- explain (costs off) select * from int8_tbl i8 where i8 in (row(123,456)::int8_tbl, '(4567890123456789,123)'); ! QUERY PLAN ! ----------------------------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 ! Filter: (i8.* = ANY (ARRAY[ROW('123'::bigint, '456'::bigint)::int8_tbl, '(4567890123456789,123)'::int8_tbl])) (2 rows) select * from int8_tbl i8 *** a/src/test/regress/expected/union.out --- b/src/test/regress/expected/union.out *************** *** 654,666 **** SELECT * FROM UNION SELECT 2 AS t, 4 AS x) ss WHERE x > 3; ! QUERY PLAN ! ---------------------------------------------------------------------------- Subquery Scan on ss Filter: (ss.x > 3) -> Unique -> Sort ! Sort Key: (1), (((random() * 3::double precision))::integer) -> Append -> Result -> Result --- 654,666 ---- UNION SELECT 2 AS t, 4 AS x) ss WHERE x > 3; ! QUERY PLAN ! ------------------------------------------------------------------------------ Subquery Scan on ss Filter: (ss.x > 3) -> Unique -> Sort ! Sort Key: (1), (((random() * '3'::double precision))::integer) -> Append -> Result -> Result *** a/src/test/regress/expected/with.out --- b/src/test/regress/expected/with.out *************** *** 2083,2090 **** SELECT * FROM parent; EXPLAIN (VERBOSE, COSTS OFF) WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 ) DELETE FROM a USING wcte WHERE aa = q2; ! QUERY PLAN ! ------------------------------------------------ Delete on public.a Delete on public.a Delete on public.b --- 2083,2090 ---- EXPLAIN (VERBOSE, COSTS OFF) WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 ) DELETE FROM a USING wcte WHERE aa = q2; ! QUERY PLAN ! ---------------------------------------------------- Delete on public.a Delete on public.a Delete on public.b *************** *** 2094,2100 **** DELETE FROM a USING wcte WHERE aa = q2; -> Insert on public.int8_tbl Output: int8_tbl.q2 -> Result ! Output: 42::bigint, 47::bigint -> Nested Loop Output: a.ctid, wcte.* Join Filter: (a.aa = wcte.q2) --- 2094,2100 ---- -> Insert on public.int8_tbl Output: int8_tbl.q2 -> Result ! Output: '42'::bigint, '47'::bigint -> Nested Loop Output: a.ctid, wcte.* Join Filter: (a.aa = wcte.q2)