Skip to content

Commit b9cff97

Browse files
peterepetere
committed
Don't allow CREATE TABLE AS to create a column with invalid collation
It is possible that an expression ends up with a collatable type but without a collation. CREATE TABLE AS could then create a table based on that. But such a column cannot be dumped with valid SQL syntax, so we disallow creating such a column. per test report from Noah Misch
1 parent 8d3b421 commit b9cff97

File tree

6 files changed

+28
-8
lines changed

6 files changed

+28
-8
lines changed

‎src/backend/catalog/heap.c

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -429,6 +429,7 @@ CheckAttributeNamesTypes(TupleDesc tupdesc, char relkind,
429429
{
430430
CheckAttributeType(NameStr(tupdesc->attrs[i]->attname),
431431
tupdesc->attrs[i]->atttypid,
432+
tupdesc->attrs[i]->attcollation,
432433
allow_system_table_mods);
433434
}
434435
}
@@ -442,7 +443,7 @@ CheckAttributeNamesTypes(TupleDesc tupdesc, char relkind,
442443
* --------------------------------
443444
*/
444445
void
445-
CheckAttributeType(const char *attname, Oid atttypid,
446+
CheckAttributeType(const char *attname, Oid atttypid, Oid attcollation,
446447
bool allow_system_table_mods)
447448
{
448449
char att_typtype = get_typtype(atttypid);
@@ -493,12 +494,24 @@ CheckAttributeType(const char *attname, Oid atttypid,
493494

494495
if (attr->attisdropped)
495496
continue;
496-
CheckAttributeType(NameStr(attr->attname), attr->atttypid,
497+
CheckAttributeType(NameStr(attr->attname), attr->atttypid, attr->attcollation,
497498
allow_system_table_mods);
498499
}
499500

500501
relation_close(relation, AccessShareLock);
501502
}
503+
504+
/*
505+
* This might not be strictly invalid per SQL standard, but it is
506+
* pretty useless, and it cannot be dumped, so we must disallow
507+
* it.
508+
*/
509+
if (type_is_collatable(atttypid) && !OidIsValid(attcollation))
510+
ereport(ERROR,
511+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
512+
errmsg("no collation was derived for column \"%s\" with collatable type %s",
513+
attname, format_type_be(atttypid)),
514+
errhint("Use the COLLATE clause to set the collation explicitly.")));
502515
}
503516

504517
/*

‎src/backend/catalog/index.c

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -352,6 +352,8 @@ ConstructTupleDescriptor(Relation heapRelation,
352352
to->atthasdef = false;
353353
to->attislocal = true;
354354
to->attinhcount = 0;
355+
356+
to->attcollation = collationObjectId[i];
355357
}
356358
else
357359
{
@@ -388,6 +390,8 @@ ConstructTupleDescriptor(Relation heapRelation,
388390
to->atttypmod = -1;
389391
to->attislocal = true;
390392

393+
to->attcollation = collationObjectId[i];
394+
391395
ReleaseSysCache(tuple);
392396

393397
/*
@@ -399,11 +403,9 @@ ConstructTupleDescriptor(Relation heapRelation,
399403
* whether a table column is of a safe type (which is why we
400404
* needn't check for the non-expression case).
401405
*/
402-
CheckAttributeType(NameStr(to->attname), to->atttypid, false);
406+
CheckAttributeType(NameStr(to->attname), to->atttypid, to->attcollation, false);
403407
}
404408

405-
to->attcollation = collationObjectId[i];
406-
407409
/*
408410
* We do not yet have the correct relation OID for the index, so just
409411
* set it invalid for now. InitializeAttributeOids() will fix it

‎src/backend/commands/tablecmds.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4206,7 +4206,7 @@ ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
42064206
typeOid = HeapTupleGetOid(typeTuple);
42074207

42084208
/* make sure datatype is legal for a column */
4209-
CheckAttributeType(colDef->colname, typeOid, false);
4209+
CheckAttributeType(colDef->colname, typeOid, collOid, false);
42104210

42114211
/* construct new attribute's pg_attribute entry */
42124212
attribute.attrelid = myrelid;
@@ -6515,7 +6515,7 @@ ATPrepAlterColumnType(List **wqueue,
65156515
typenameTypeIdModColl(NULL, typeName, &targettype, &targettypmod, &targetcollid);
65166516

65176517
/* make sure datatype is legal for a column */
6518-
CheckAttributeType(colName, targettype, false);
6518+
CheckAttributeType(colName, targettype, targetcollid, false);
65196519

65206520
if (tab->relkind == RELKIND_RELATION)
65216521
{

‎src/include/catalog/heap.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -117,7 +117,7 @@ extern Form_pg_attribute SystemAttributeByName(const char *attname,
117117
extern void CheckAttributeNamesTypes(TupleDesc tupdesc, char relkind,
118118
bool allow_system_table_mods);
119119

120-
extern void CheckAttributeType(const char *attname, Oid atttypid,
120+
extern void CheckAttributeType(const char *attname, Oid atttypid, Oid attcollation,
121121
bool allow_system_table_mods);
122122

123123
#endif /* HEAP_H */

‎src/test/regress/expected/collate.linux.utf8.out

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -627,6 +627,9 @@ ERROR: collation mismatch between implicit collations "en_US.utf8" and "C"
627627
LINE 1: SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM colla...
628628
^
629629
HINT: You can override the collation by applying the COLLATE clause to one or both expressions.
630+
CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3; -- fail
631+
ERROR: no collation was derived for column "b" with collatable type text
632+
HINT: Use the COLLATE clause to set the collation explicitly.
630633
-- casting
631634
SELECT CAST('42' AS text COLLATE "C");
632635
ERROR: COLLATE clause not allowed in cast target

‎src/test/regress/sql/collate.linux.utf8.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -188,6 +188,8 @@ SELECT a, b COLLATE "C" FROM collate_test1 UNION SELECT a, b FROM collate_test3
188188
SELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM collate_test3 ORDER BY 2; -- fail
189189
SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM collate_test3 ORDER BY 2; -- fail
190190

191+
CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3; -- fail
192+
191193

192194
-- casting
193195

0 commit comments

Comments
 (0)