3

We are running the following select statement on postgres 10 and 11 and notice unexpected behaviour while processing the subquery

See example:

Correct query

--> subquery column name exists in subquery

select * from information_schema."tables" t
where t.table_schema in (select schemaname from pg_tables t2);

Error no column schemaname2

--> subquery column name does not exists in both tables

select * from information_schema."tables" t
where t.table_schema in (select schemaname2 from pg_tables t2);

NO Error although the column table_schema does not exist in pg_tables

--> subquery column name does not in exists in subquery but in main query -> all rows from the main query are selected

select * from information_schema."tables" t
where t.table_schema in (select table_schema from pg_tables t2);

Error due to the use of table alias

--> our proposed workaround -> always use aliases then postgres is forced to validate column name

select * from information_schema."tables" t
where t.table_schema in (select t2.table_schema from pg_tables t2);

Special thx to https://stackoverflow.com/users/8579285/oved-s for verification and constant support

F.Lazarescu
  • 1,385
  • 2
  • 16
  • 31
Holger
  • 31
  • 2
  • Check if this can help: https://www.postgresql.org/docs/10/bug-reporting.html – Ankit Deshpande May 14 '19 at 12:23
  • 1
    Not a bug, this is how it's required by the SQL standard. See also here: https://dba.stackexchange.com/questions/218871/ –  May 14 '19 at 12:30
  • @a_horse_with_no_name That is some interesting behavior! I'm assuming it's performing a correlated subquery since the column name must match a column name in the outer query. Since no correlation is specified it's cross joining giving a positive hit for all records. This explains why the Alias fails. – JNevill May 14 '19 at 12:34
  • If the column doesn't exist in the scope of the sub-query the SQL standard mandates that a column with that name from the outer query should be used. The query that works in your example (but shouldn't) is essentially the same as `where t.table_schema = table_schema` (instead of a IN condition) –  May 14 '19 at 12:41

0 Answers0