Folks,
Last week one of my students confronted me with a nice little SQL statement which made me call gdb ...
Consider the following scenario:
[EMAIL PROTECTED] bug]$ cat q1.sql create temporary sequence seq_ab;
select * from (Select nextval('seq_ab') as nv,
* from ( select t_product.id,t_text.value,t_price.price
from t_product,t_price,t_text
where t_product.id = t_price.product_id
and t_product.name = t_text.id
and t_text.lang='de'
and t_price.typ = 'default'
order by price desc ) as t ) as u
-- WHERE nv <= 1
;
[EMAIL PROTECTED] bug]$ psql test < q1.sql
CREATE SEQUENCE
nv | id | value | price
----+----+---------+-------
1 | 3 | Banane | 12
2 | 1 | T-Shirt | 10
3 | 2 | Apfel | 7
(3 rows)
this query returns the right result. however, when uncommenting the WHERE clause things look different:
[EMAIL PROTECTED] bug]$ cat q2.sql create temporary sequence seq_ab;
select * from (Select nextval('seq_ab') as nv,
* from ( select t_product.id,t_text.value,t_price.price
from t_product,t_price,t_text
where t_product.id = t_price.product_id
and t_product.name = t_text.id
and t_text.lang='de'
and t_price.typ = 'default'
order by price desc ) as t ) as u
WHERE nv <= 1
;
[EMAIL PROTECTED] bug]$ psql test < q2.sql
CREATE SEQUENCE
nv | id | value | price
----+----+---------+-------
4 | 1 | T-Shirt | 10
(1 row)
Obviously nv = 4 is wrong ...
Looking at the execution plan of the second query the problem seems quite obvious:
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Subquery Scan t (cost=69.24..69.26 rows=1 width=68)
-> Sort (cost=69.24..69.25 rows=1 width=68)
Sort Key: t_price.price
-> Hash Join (cost=22.51..69.23 rows=1 width=68)
Hash Cond: ("outer".name = "inner".id)
Join Filter: (nextval('seq_ab'::text) <= 1)
-> Nested Loop (cost=0.00..46.68 rows=5 width=40)
-> Seq Scan on t_price (cost=0.00..22.50 rows=5 width=36)
Filter: (typ = 'default'::text)
-> Index Scan using t_product_pkey on t_product (cost=0.00..4.82 rows=1 width=8)
Index Cond: (t_product.id = "outer".product_id)
-> Hash (cost=22.50..22.50 rows=5 width=36)
-> Seq Scan on t_text (cost=0.00..22.50 rows=5 width=36)
Filter: (lang = 'de'::text)
(14 rows)
nextval() is called again when processing the WHERE clause.
this was fine if nextval() would return the same thing again and again (which is not the job of nextval).
if the planner materialized the subquery things would materialize the subquery in case of unstable functions things would work in this case.
I know I temp table would easily fix this query and it is certainly not the best query I have ever seen but still it seems like a bug and I just wanted to know whether it is a know issue or not.
Looking at the code I did not quite know whether this is something which should / can be fixed or not.
here is the data: ------------------------------------------------------ CREATE TABLE t_text ( id int4, lang text, value text );
CREATE TABLE t_group ( id int4, name int4, -- mehrsprachig in t_text valid boolean );
INSERT INTO t_group VALUES (1, 1, 't'); INSERT INTO t_text VALUES (1, 'de', 'Obst'); INSERT INTO t_text VALUES (1, 'en', 'Fruits');
INSERT INTO t_group VALUES (2, 2, 't'); INSERT INTO t_text VALUES (2, 'de', 'Kleidung'); INSERT INTO t_text VALUES (2, 'en', 'Clothes');
CREATE UNIQUE INDEX idx_group_id ON t_group (id);
CREATE TABLE t_product ( id int4, name int4, -- mehrsprachig in t_text active boolean, PRIMARY KEY (id) );
INSERT INTO t_product VALUES (1, 3, 't'); INSERT INTO t_text VALUES (3, 'de', 'T-Shirt'); INSERT INTO t_text VALUES (3, 'en', 'T-Shirt');
INSERT INTO t_product VALUES (2, 4, 't'); INSERT INTO t_text VALUES (4, 'de', 'Apfel'); INSERT INTO t_text VALUES (4, 'en', 'Apple');
INSERT INTO t_product VALUES (3, 5, 't'); INSERT INTO t_text VALUES (5, 'de', 'Banane'); INSERT INTO t_text VALUES (5, 'en', 'Banana');
CREATE TABLE t_product_group ( product_id int4 REFERENCES t_product(id) ON UPDATE CASCADE ON DELETE CASCADE, group_id int4 REFERENCES t_group(id) ON UPDATE CASCADE ON DELETE CASCADE );
INSERT INTO t_product_group VALUES (2, 1); INSERT INTO t_product_group VALUES (3, 1); INSERT INTO t_product_group VALUES (1, 2);
CREATE TABLE t_price ( id int4, typ text, price numeric, product_id int4 REFERENCES t_product(id) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY (id) );
INSERT INTO t_price VALUES (1, 'default', '10', 1); INSERT INTO t_price VALUES (2, 'sonder', '20', 1); INSERT INTO t_price VALUES (3, 'spezial', '30', 1); INSERT INTO t_price VALUES (4, 'default', '7', 2); INSERT INTO t_price VALUES (5, 'default', '12', 3);
Best regards,
Hans
-- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/720/10 1234567 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster