Thread
-
bug: UNION
Nabil Sayegh <nsmail@sayegh.de> — 2000-12-05T18:11:42Z
Hi, There seems to be a bug in postgresql concerning UNION. I use 7.0.3 (BLCKSIZE=32) I have the following tables: Table "best_ez" Attribute | Type | Modifier ------------+--------------+-------------------------------------------------- id | integer | not null default nextval('best_ez_id_seq'::text) ins_id | char(16) | hotel_id | varchar(200) | not null default '' datum | date | not null corporate | float8 | default '0.0000' preis | float8 | not null default '0.0000' menge | integer | not null default '0' reserviert | integer | default '0' gebucht | integer | default '0' Indices: best_ez_hotel_id_key, best_ez_pkey tcon=> SELECT * from best_ez where hotel_id='ADMIN' order by datum; id | ins_id | hotel_id | datum |preis | menge | reserviert | gebucht ------+------------------+----------+------------+------+-------+------------+--------- ... 476 | 2000010120000201 | ADMIN | 2000-01-30 | 4 | 5 | 0 | 0 477 | 2000010120000201 | ADMIN | 2000-01-31 | 4 | 5 | 0 | 0 478 | 2000010120000201 | ADMIN | 2000-02-01 | 4 | 5 | 0 | 0 2955 | 2001050120010510 | ADMIN | 2001-05-01 | 20 | 10 | 0 | 0 2956 | 2001050120010510 | ADMIN | 2001-05-02 | 20 | 10 | 0 | 0 ... Table "ints" Attribute | Type | Modifier -----------+---------+---------------------------------------------- n | integer | not null default nextval('ints_n_seq'::text) Index: ints_pkey It contains the numbers from 1 to 31 The Query: => SELECT n, menge, preis from ints, BEST_DZ where n <= 28 and hotel_id='ADMIN' AND DATUM between '2001-02-01'::date AND '2001-02-01'::date + 28 - 1 and n=date_part('day', datum) union all select n, 0, 0 from ints where n<28 AND n not in (select date_part('day', datum) from BEST_DZ where hotel_id='ADMIN' AND DATUM between '2001-02-01'::date AND '2001-02-01'::date + 28 - 1) order by n; n | menge | preis ----+-------+------- 1 | 0 | 0 2 | 0 | 0 3 | 0 | 0 4 | 0 | 0 5 | 0 | 0 6 | 0 | 0 7 | 0 | 0 8 | 0 | 0 9 | 11 | 3.7 10 | 0 | 0 11 | 0 | 0 ... The Query (same as above, but now with ins_id and '0'): => SELECT n, ins_id, menge, preis from ints, BEST_DZ where n <= 28 and hotel_id='ADMIN' AND DATUM between '2001-02-01'::date AND '2001-02-01'::date + 28 - 1 and n=date_part('day', datum) union all select n, '0', 0, 0 from ints where n<28 AND n not in (select date_part('day', datum) from BEST_DZ where hotel_id='ADMIN' AND DATUM between '2001-02-01'::date AND '2001-02-01'::date + 28 - 1) order by n; n | ins_id | menge | preis ----+------------------+-----------+---------------------- 1 | 0 | 0 | 0 2 | 0 | 0 | 0 3 | 0 | 0 | 0 4 | 0 | 0 | 0 5 | 0 | 0 | 0 6 | 0 | 0 | 0 7 | 0 | 0 | 0 8 | 0 | 0 | 0 9 | 2001020920010209 | 959459888 | 3.11924535422124e-33 10 | 0 | 0 | 0 11 | 0 | 0 | 0 The same query as above but without order by: => SELECT n, ins_id, menge, preis from ints, BEST_DZ where n <= 28 and hotel_id='ADMIN' AND DATUM between '2001-02-01'::date AND '2001-02-01'::date + 28 - 1 and n=date_part('day', datum) union all select n, '0', 0, 0 from ints where n<28 AND n not in (select date_part('day', datum) from BEST_DZ where hotel_id='ADMIN' AND DATUM between '2001-02-01'::date AND '2001-02-01'::date + 28 - 1); n | ins_id | menge | preis ----+------------------+-------+----------------------- 9 | 2001020920010209 | 11 | 3.7 1 | 0 | 16 | 2.0467033885567e-269 2 | 0 | 16 | 2.04646077043258e-269 3 | 0 | 16 | 2.0467033885567e-269 4 | 0 | 16 | 2.04646077043258e-269 5 | 0 | 16 | 2.0467033885567e-269 6 | 0 | 16 | 2.04646077043258e-269 7 | 0 | 16 | 2.0467033885567e-269 8 | 0 | 16 | 2.04646077043258e-269 10 | 0 | 16 | 2.0467033885567e-269 11 | 0 | 16 | 2.04646077043258e-269 (even the integer row "menge" is wrong) !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! The last query (e.g.) works exactly 3 times after a fresh connection ANY further executions of this query lead to wrong results. Other queries (of similar structure (UNION)) never work right !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! How can this be ? It's the same query on the same data. Apparantly the "preis" column is a rounding-error, but "menge" is of type integer. Why does it work if I skip "ins_id" ? Is it related to the BLCKSIZE ? TIA P.S.: Is there hope for OUTER JOIN in 7.1 ? -- Nabil Sayegh -
Re: bug: UNION
Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-05T20:17:35Z
Nabil Sayegh <nsmail@sayegh.de> writes: > There seems to be a bug in postgresql concerning UNION. Cross-datatype UNION doesn't work very well at all in existing releases. I recommend making sure the constants in the second SELECT exactly match the types of the variables in the first select, using explicit casts if necessary. For example, since preis is float8, the 0 that's being unioned with it should be written 0.0 or 0::float8. I believe that mess is all fixed for 7.1. > P.S.: Is there hope for OUTER JOIN in 7.1 ? Done and working. regards, tom lane