BUG #12276: Using old name of a renamed or dropped column in a sub-query does not throw error - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      12276
Logged by:          Collin Peters
Email address:      collin.peters@gmail.com
PostgreSQL version: 9.1.14
Operating system:   Debian Squeeze 6.0.10
Description:

Please see SQL below. We just had this happen in our production system,
thankfully in a non-serious scenario.

Basically if you rename a column, and then use the original name in a
subquery, that sub-query does not complain that the column no longer exists,
and seems to complete ok. This will cause whatever SELECT or DELETE that is
using the sub-query to continue on its merry way and cause havoc.

---------------------------------------
SELECT version();

CREATE TABLE orders
(
  order_id integer NOT NULL,
  name text NOT NULL,
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

CREATE TABLE order_lines
(
  order_lines_id integer NOT NULL,
  order_id integer NOT NULL,
  value text NOT NULL,
  CONSTRAINT order_lines_pk PRIMARY KEY (order_lines_id),
  CONSTRAINT orders_fk FOREIGN KEY (order_id)
      REFERENCES orders (order_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
);

CREATE TABLE users
(
  user_id integer NOT NULL,
  order_id integer NOT NULL,
  foo text NOT NULL,
  bar text NOT NULL,
  CONSTRAINT users_pk PRIMARY KEY (user_id),
  CONSTRAINT order_fk FOREIGN KEY (order_id)
      REFERENCES orders (order_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
);

INSERT INTO orders VALUES (100000, 'a');
INSERT INTO orders VALUES (100001, 'b');
INSERT INTO orders VALUES (100002, 'c');

INSERT INTO order_lines VALUES (1, 100000, 'foo');
INSERT INTO order_lines VALUES (2, 100000, 'bar');
INSERT INTO order_lines VALUES (3, 100000, 'baz');

INSERT INTO order_lines VALUES (4, 100001, 'foo');
INSERT INTO order_lines VALUES (5, 100001, 'bar');
INSERT INTO order_lines VALUES (6, 100001, 'baz');

INSERT INTO order_lines VALUES (7, 100002, 'foo');
INSERT INTO order_lines VALUES (8, 100002, 'bar');
INSERT INTO order_lines VALUES (9, 100002, 'baz');

INSERT INTO users VALUES (1, 100000, 'x', 'y');
INSERT INTO users VALUES (2, 100000, 'x', 'y');
INSERT INTO users VALUES (3, 100001, 'x', 'y');
INSERT INTO users VALUES (4, 100001, 'x', 'y');
INSERT INTO users VALUES (5, 100002, 'x', 'y');
INSERT INTO users VALUES (6, 100002, 'x', 'y');

-- delete all order lines with value 'foo' and matching order_id
DELETE
FROM    order_lines
WHERE   value = 'foo'
AND     order_id IN (
    SELECT  order_id
    FROM    users
    WHERE   user_id = 1
);

-- worked - two rows remain
SELECT     *
FROM     order_lines
WHERE     value = 'foo';

-- rename column in users table
ALTER TABLE users RENAME COLUMN order_id TO order_id_bak;
-- Or...
-- ALTER TABLE users DROP COLUMN order_id;

-- select on value of 'bar' and orders for user 1
-- FAIL - returns all three order lines of 'bar'
SELECT     *
FROM    order_lines
WHERE   value = 'bar'
AND     order_id IN (
    -- THIS SHOULD FAIL!! THIS COLUMN NAME DOES NOT EXIST ANYMORE
    SELECT  order_id
    FROM    users
    WHERE   user_id = 1
);

-- same delete as above, except value of 'bar'
DELETE
FROM    order_lines
WHERE   value = 'bar'
AND     order_id IN (
    -- THIS SHOULD FAIL!! THIS COLUMN NAME DOES NOT EXIST ANYMORE
    SELECT  order_id
    FROM    users
    WHERE   user_id = 1
);

-- Words cannot describe. ALL ORDER LINES FOR 'bar' HAVE BEEN DELETED FOR
ALL USERS!!
SELECT     *
FROM     order_lines
WHERE     value = 'bar';

---------------------------------------
The results. I would expect the second select to only return order_lines_id
of 2, and the third select to return order lines 5 and 8.

 PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
(Debian 4.4.5-8) 4.4.5, 64-bit
(1 row)
 order_lines_id | order_id | value
----------------+----------+-------
              4 |   100001 | foo
              7 |   100002 | foo
(2 rows)
 order_lines_id | order_id | value
----------------+----------+-------
              2 |   100000 | bar
              5 |   100001 | bar
              8 |   100002 | bar
(3 rows)
 order_lines_id | order_id | value
----------------+----------+-------
(0 rows)