Thread: Invalid SQL not rejected?

Hmmm.... the subselect is invalid, but not rejected.  The outer select returns every row in the customer_order_matches table.  This seems pretty wrong. This is PG 9.2.1 running on Ubuntu.


db=> select count(1) from customer_order_matches where customer_order_item_id in (select customer_order_item_id from customer_order where customer_order_id = 105862140);
 count
-------
 36180
(1 row)

db=> select customer_order_item_id from customer_order where customer_order_id = 105862140;
ERROR:  column "customer_order_item_id" does not exist
LINE 1: select customer_order_item_id from customer_order where cust...
               ^

Craig

Craig James <cjames@emolecules.com> writes:
> Hmmm.... the subselect is invalid, but not rejected.  The outer select
> returns every row in the customer_order_matches table.  This seems pretty
> wrong. This is PG 9.2.1 running on Ubuntu.

> db=> select count(1) from customer_order_matches where
> customer_order_item_id in (select customer_order_item_id from
> customer_order where customer_order_id = 105862140);
>  count
> -------
>  36180
> (1 row)

Unfortunately, if customer_order_item_id doesn't exist in
customer_order, that's still a perfectly valid outer reference
to customer_order_matches.customer_order_item_id.  People get
confused by that kind of thing regularly, but it's required by
spec to work like this.

            regards, tom lane

On 12/04/13 10:17, Craig James wrote:
> Hmmm.... the subselect is invalid, but not rejected.  The outer select
> returns every row in the customer_order_matches table.  This seems
> pretty wrong. This is PG 9.2.1 running on Ubuntu.
>
>
> db=> select count(1) from customer_order_matches where
> customer_order_item_id in (select customer_order_item_id from
> customer_order where customer_order_id = 105862140);
>  count
> -------
>  36180
> (1 row)
>
> db=> select customer_order_item_id from customer_order where
> customer_order_id = 105862140;
> ERROR:  column "customer_order_item_id" does not exist
> LINE 1: select customer_order_item_id from customer_order where cust...
>                ^
>
> Craig
The "customer_order_item_id" in the subselect is referencing the
column and therefore the value of the outer query "customer_order_item_id"
in the query result.
i.e:
SELECT generate_series FROM generate_series(1,100) WHERE generate_series
IN (SELECT generate_series);
Which is always going to be true. (x == x).

You might want to explicitly reference tables (and alias). Such as:

SELECT count(*) FROM customer_order_matches com
    WHERE com.customer_order_item_id IN
        (SELECT co.customer_order_item_id
            FROM customer_order co
            WHERE co.customer_order_id = 105862140);

Although I'm making asumptions with the "customer_order_id".
Jules.

Julian Glass <tempura@internode.on.net> wrote:

> You might want to explicitly reference tables (and alias).

I agree.  I find that the best defense against several classes of
silent misbehavior is to alias all table references and qualify all
column references with the alias -- or use a tool which does this
for you.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company