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
