Skip to content

Commit 0ee2610

Browse files
tglsfdctglsfdc
committed
Fix UNION/INTERSECT/EXCEPT so that when two inputs being merged have
same data type and same typmod, we show that typmod as the output typmod, rather than generic -1. This responds to several complaints over the past few years about UNIONs unexpectedly dropping length or precision info.
1 parent e860e74 commit 0ee2610

File tree

11 files changed

+79
-35
lines changed

11 files changed

+79
-35
lines changed

‎src/backend/nodes/copyfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.345 2006/08/02 01:59:45 joe Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.346 2006/08/10 02:36:28 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -1793,6 +1793,7 @@ _copySetOperationStmt(SetOperationStmt *from)
17931793
COPY_NODE_FIELD(larg);
17941794
COPY_NODE_FIELD(rarg);
17951795
COPY_NODE_FIELD(colTypes);
1796+
COPY_NODE_FIELD(colTypmods);
17961797

17971798
return newnode;
17981799
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@
1818
* Portions Copyright (c) 1994, Regents of the University of California
1919
*
2020
* IDENTIFICATION
21-
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.279 2006/08/02 01:59:45 joe Exp $
21+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.280 2006/08/10 02:36:28 tgl Exp $
2222
*
2323
*-------------------------------------------------------------------------
2424
*/
@@ -743,6 +743,7 @@ _equalSetOperationStmt(SetOperationStmt *a, SetOperationStmt *b)
743743
COMPARE_NODE_FIELD(larg);
744744
COMPARE_NODE_FIELD(rarg);
745745
COMPARE_NODE_FIELD(colTypes);
746+
COMPARE_NODE_FIELD(colTypmods);
746747

747748
return true;
748749
}

‎src/backend/nodes/outfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.280 2006/08/02 01:59:45 joe Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.281 2006/08/10 02:36:28 tgl Exp $
1212
*
1313
* NOTES
1414
* Every node type that can appear in stored rules' parsetrees *must*
@@ -1574,6 +1574,7 @@ _outSetOperationStmt(StringInfo str, SetOperationStmt *node)
15741574
WRITE_NODE_FIELD(larg);
15751575
WRITE_NODE_FIELD(rarg);
15761576
WRITE_NODE_FIELD(colTypes);
1577+
WRITE_NODE_FIELD(colTypmods);
15771578
}
15781579

15791580
static void

‎src/backend/nodes/readfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.193 2006/08/02 01:59:45 joe Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.194 2006/08/10 02:36:28 tgl Exp $
1212
*
1313
* NOTES
1414
* Path and Plan nodes do not have any readfuncs support, because we
@@ -245,6 +245,7 @@ _readSetOperationStmt(void)
245245
READ_NODE_FIELD(larg);
246246
READ_NODE_FIELD(rarg);
247247
READ_NODE_FIELD(colTypes);
248+
READ_NODE_FIELD(colTypmods);
248249

249250
READ_DONE();
250251
}

‎src/backend/optimizer/path/allpaths.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.150 2006/08/02 01:59:45 joe Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.151 2006/08/10 02:36:28 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -813,6 +813,10 @@ recurse_pushdown_safe(Node *setOp, Query *topquery,
813813
* Compare tlist's datatypes against the list of set-operation result types.
814814
* For any items that are different, mark the appropriate element of
815815
* differentTypes[] to show that this column will have type conversions.
816+
*
817+
* We don't have to care about typmods here: the only allowed difference
818+
* between set-op input and output typmods is input is a specific typmod
819+
* and output is -1, and that does not require a coercion.
816820
*/
817821
static void
818822
compare_tlist_datatypes(List *tlist, List *colTypes,

‎src/backend/optimizer/prep/prepjointree.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
*
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/optimizer/prep/prepjointree.c,v 1.39 2006/07/14 14:52:21 momjian Exp $
18+
* $PostgreSQL: pgsql/src/backend/optimizer/prep/prepjointree.c,v 1.40 2006/08/10 02:36:28 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -716,6 +716,7 @@ is_simple_union_all_recurse(Node *setOp, Query *setOpQuery, List *colTypes)
716716
Assert(subquery != NULL);
717717

718718
/* Leaf nodes are OK if they match the toplevel column types */
719+
/* We don't have to compare typmods here */
719720
return tlist_same_datatypes(subquery->targetList, colTypes, true);
720721
}
721722
else if (IsA(setOp, SetOperationStmt))

‎src/backend/optimizer/prep/prepunion.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@
2222
*
2323
*
2424
* IDENTIFICATION
25-
* $PostgreSQL: pgsql/src/backend/optimizer/prep/prepunion.c,v 1.132 2006/04/30 18:30:39 tgl Exp $
25+
* $PostgreSQL: pgsql/src/backend/optimizer/prep/prepunion.c,v 1.133 2006/08/10 02:36:28 tgl Exp $
2626
*
2727
*-------------------------------------------------------------------------
2828
*/
@@ -152,6 +152,10 @@ plan_set_operations(PlannerInfo *root, double tuple_fraction,
152152
* flag: if >= 0, add a resjunk output column indicating value of flag
153153
* refnames_tlist: targetlist to take column names from
154154
* *sortClauses: receives list of SortClauses for result plan, if any
155+
*
156+
* We don't have to care about typmods here: the only allowed difference
157+
* between set-op input and output typmods is input is a specific typmod
158+
* and output is -1, and that does not require a coercion.
155159
*/
156160
static Plan *
157161
recurse_set_operations(Node *setOp, PlannerInfo *root,

‎src/backend/optimizer/util/tlist.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/util/tlist.c,v 1.72 2006/03/05 15:58:32 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/util/tlist.c,v 1.73 2006/08/10 02:36:29 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -174,6 +174,8 @@ get_sortgrouplist_exprs(List *sortClauses, List *targetList)
174174
*
175175
* Resjunk columns are ignored if junkOK is true; otherwise presence of
176176
* a resjunk column will always cause a 'false' result.
177+
*
178+
* Note: currently no callers care about comparing typmods.
177179
*/
178180
bool
179181
tlist_same_datatypes(List *tlist, List *colTypes, bool junkOK)

‎src/backend/parser/analyze.c

Lines changed: 51 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.343 2006/08/02 14:14:22 tgl Exp $
9+
* $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.344 2006/08/10 02:36:29 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -131,7 +131,8 @@ static void transformFKConstraints(ParseState *pstate,
131131
bool skipValidation,
132132
bool isAddConstraint);
133133
static void applyColumnNames(List *dst, List *src);
134-
static List *getSetColTypes(ParseState *pstate, Node *node);
134+
static void getSetColTypes(ParseState *pstate, Node *node,
135+
List **colTypes, List **colTypmods);
135136
static void transformLockingClause(Query *qry, LockingClause *lc);
136137
static void transformConstraintAttrs(List *constraintList);
137138
static void transformColumnType(ParseState *pstate, ColumnDef *column);
@@ -2312,7 +2313,8 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
23122313
List *lockingClause;
23132314
Node *node;
23142315
ListCell *left_tlist,
2315-
*dtlist,
2316+
*lct,
2317+
*lcm,
23162318
*l;
23172319
List *targetvars,
23182320
*targetnames,
@@ -2395,9 +2397,10 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
23952397
targetnames = NIL;
23962398
left_tlist = list_head(leftmostQuery->targetList);
23972399

2398-
foreach(dtlist, sostmt->colTypes)
2400+
forboth(lct, sostmt->colTypes, lcm, sostmt->colTypmods)
23992401
{
2400-
Oid colType = lfirst_oid(dtlist);
2402+
Oid colType = lfirst_oid(lct);
2403+
int32 colTypmod = lfirst_int(lcm);
24012404
TargetEntry *lefttle = (TargetEntry *) lfirst(left_tlist);
24022405
char *colName;
24032406
TargetEntry *tle;
@@ -2408,7 +2411,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
24082411
expr = (Expr *) makeVar(leftmostRTI,
24092412
lefttle->resno,
24102413
colType,
2411-
-1,
2414+
colTypmod,
24122415
0);
24132416
tle = makeTargetEntry(expr,
24142417
(AttrNumber) pstate->p_next_resno++,
@@ -2609,8 +2612,12 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt)
26092612
SetOperationStmt *op = makeNode(SetOperationStmt);
26102613
List *lcoltypes;
26112614
List *rcoltypes;
2612-
ListCell *l;
2613-
ListCell *r;
2615+
List *lcoltypmods;
2616+
List *rcoltypmods;
2617+
ListCell *lct;
2618+
ListCell *rct;
2619+
ListCell *lcm;
2620+
ListCell *rcm;
26142621
const char *context;
26152622

26162623
context = (stmt->op == SETOP_UNION ? "UNION" :
@@ -2630,24 +2637,43 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt)
26302637
* Verify that the two children have the same number of non-junk
26312638
* columns, and determine the types of the merged output columns.
26322639
*/
2633-
lcoltypes = getSetColTypes(pstate, op->larg);
2634-
rcoltypes = getSetColTypes(pstate, op->rarg);
2640+
getSetColTypes(pstate, op->larg, &lcoltypes, &lcoltypmods);
2641+
getSetColTypes(pstate, op->rarg, &rcoltypes, &rcoltypmods);
26352642
if (list_length(lcoltypes) != list_length(rcoltypes))
26362643
ereport(ERROR,
26372644
(errcode(ERRCODE_SYNTAX_ERROR),
26382645
errmsg("each %s query must have the same number of columns",
26392646
context)));
2647+
Assert(list_length(lcoltypes) == list_length(lcoltypmods));
2648+
Assert(list_length(rcoltypes) == list_length(rcoltypmods));
26402649

26412650
op->colTypes = NIL;
2642-
forboth(l, lcoltypes, r, rcoltypes)
2651+
op->colTypmods = NIL;
2652+
/* don't have a "foreach4", so chase two of the lists by hand */
2653+
lcm = list_head(lcoltypmods);
2654+
rcm = list_head(rcoltypmods);
2655+
forboth(lct, lcoltypes, rct, rcoltypes)
26432656
{
2644-
Oid lcoltype = lfirst_oid(l);
2645-
Oid rcoltype = lfirst_oid(r);
2657+
Oid lcoltype = lfirst_oid(lct);
2658+
Oid rcoltype = lfirst_oid(rct);
2659+
int32 lcoltypmod = lfirst_int(lcm);
2660+
int32 rcoltypmod = lfirst_int(rcm);
26462661
Oid rescoltype;
2662+
int32 rescoltypmod;
26472663

2664+
/* select common type, same as CASE et al */
26482665
rescoltype = select_common_type(list_make2_oid(lcoltype, rcoltype),
26492666
context);
2667+
/* if same type and same typmod, use typmod; else default */
2668+
if (lcoltype == rcoltype && lcoltypmod == rcoltypmod)
2669+
rescoltypmod = lcoltypmod;
2670+
else
2671+
rescoltypmod = -1;
26502672
op->colTypes = lappend_oid(op->colTypes, rescoltype);
2673+
op->colTypmods = lappend_int(op->colTypmods, rescoltypmod);
2674+
2675+
lcm = lnext(lcm);
2676+
rcm = lnext(rcm);
26512677
}
26522678

26532679
return (Node *) op;
@@ -2656,17 +2682,19 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt)
26562682

26572683
/*
26582684
* getSetColTypes
2659-
* Get output column types of an (already transformed) set-op node
2685+
* Get output column types/typmods of an (already transformed) set-op node
26602686
*/
2661-
static List *
2662-
getSetColTypes(ParseState *pstate, Node *node)
2687+
static void
2688+
getSetColTypes(ParseState *pstate, Node *node,
2689+
List **colTypes, List **colTypmods)
26632690
{
2691+
*colTypes = NIL;
2692+
*colTypmods = NIL;
26642693
if (IsA(node, RangeTblRef))
26652694
{
26662695
RangeTblRef *rtr = (RangeTblRef *) node;
26672696
RangeTblEntry *rte = rt_fetch(rtr->rtindex, pstate->p_rtable);
26682697
Query *selectQuery = rte->subquery;
2669-
List *result = NIL;
26702698
ListCell *tl;
26712699

26722700
Assert(selectQuery != NULL);
@@ -2677,23 +2705,23 @@ getSetColTypes(ParseState *pstate, Node *node)
26772705

26782706
if (tle->resjunk)
26792707
continue;
2680-
result = lappend_oid(result, exprType((Node *) tle->expr));
2708+
*colTypes = lappend_oid(*colTypes,
2709+
exprType((Node *) tle->expr));
2710+
*colTypmods = lappend_int(*colTypmods,
2711+
exprTypmod((Node *) tle->expr));
26812712
}
2682-
return result;
26832713
}
26842714
else if (IsA(node, SetOperationStmt))
26852715
{
26862716
SetOperationStmt *op = (SetOperationStmt *) node;
26872717

26882718
/* Result already computed during transformation of node */
26892719
Assert(op->colTypes != NIL);
2690-
return op->colTypes;
2720+
*colTypes = op->colTypes;
2721+
*colTypmods = op->colTypmods;
26912722
}
26922723
else
2693-
{
26942724
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
2695-
return NIL; /* keep compiler quiet */
2696-
}
26972725
}
26982726

26992727
/* Attach column names from a ColumnDef list to a TargetEntry list */

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.347 2006/08/06 03:53:44 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.348 2006/08/10 02:36:29 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200608051
56+
#define CATALOG_VERSION_NO 200608091
5757

5858
#endif

‎src/include/nodes/parsenodes.h

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.320 2006/08/02 01:59:47 joe Exp $
10+
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.321 2006/08/10 02:36:29 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -769,7 +769,8 @@ typedef struct SetOperationStmt
769769
/* Eventually add fields for CORRESPONDING spec here */
770770

771771
/* Fields derived during parse analysis: */
772-
List *colTypes; /* list of OIDs of output column types */
772+
List *colTypes; /* OID list of output column type OIDs */
773+
List *colTypmods; /* integer list of output column typmods */
773774
} SetOperationStmt;
774775

775776

0 commit comments

Comments
 (0)