From: | Julian Glass <tempura(at)internode(dot)on(dot)net> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Invalid SQL not rejected? |
Date: | 2013-04-12 01:28:06 |
Message-ID: | 51676326.9040807@internode.on.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-04-12 01:32:07 | Re: [HACKERS] after 9.2.4 patch vacuumdb -avz not analyzing all tables |
Previous Message | Jeff Janes | 2013-04-12 01:15:49 | Re: [ADMIN] after 9.2.4 patch vacuumdb -avz not analyzing all tables |