bug: UNION - Mailing list pgsql-bugs

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