Folks,

Last week one of my students confronted me with a nice little SQL 
statement which made me call gdb ...

Consider the following scenario:

[hs@fedora 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
                 ;
[hs@fedora 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:

[hs@fedora 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
                 ;
[hs@fedora 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



Mon, 04 Oct 2004 16:14:55 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at>
is it a known issue or just a bug?
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:
> Consider the following scenario:

> 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
>                  ;

I don't think there's any very clean way to fix this sort of problem in
general.  We could make this particular example work if

(1) we prevented a subquery containing volatile functions in its
targetlist from being flattened into the parent query, and

(2) we prevented outer WHERE clauses from being pushed down into a
subquery when they reference subquery outputs containing volatile
functions.

There has been some recent discussion about doing (1) but I think we
forgot about the necessity to also do (2); otherwise you'd end up with

select * from (Select nextval('seq_ab') as nv,
                       ...
                       WHERE nextval('seq_ab') <= 1
              ) as u
                 ;

which is hardly any better.

Now those things are both doable but where it really falls down is when
you join the subselect to some other table.  Short of materializing the
subselect there'd be no way to guarantee single evaluation of any one
row in the subselect.

I'd be willing to do (1) and (2) but not to force materialization; the
performance hit for that just seems unacceptable.

			regards, tom lane

Mon, 04 Oct 2004 12:26:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: is it a known issue or just a bug? 