From: Julian Glass <tempura@internode.on.net>
To: pgsql-admin@postgresql.org
Subject: Re: Invalid SQL not rejected?
Date: Fri, 12 Apr 2013 11:28:06 +1000
Message-ID: <51676326.9040807@internode.on.net> (raw)
In-Reply-To: <CAFwQ8reYH0cuHhFnnv_USHrXvjmwY9+Fw_KEuqhQjecU_-bAtQ@mail.gmail.com>
References: <CAFwQ8reYH0cuHhFnnv_USHrXvjmwY9+Fw_KEuqhQjecU_-bAtQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tempura@internode.on.net
Subject: Re: Invalid SQL not rejected?
In-Reply-To: <51676326.9040807@internode.on.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox