Hello!

 There is problem - I don't know where is it.

 I'm using PostgreSQL 6.3.2 under Linux-2.0 with official patches:

  . linux_elf.patch-980421.gz
  . gram.c.patch-980428
  . configure-980430.gz
  . btree_adj-980730.gz
 
 There are two tables in my test database:

create table AAA (anum int2, aname char(16), ata int4 default 0);
insert into AAA values (0, '0');
insert into AAA values (1, '1');
-- Note: ata hasn't initialized!

create table BBB (bnum int2, bname char(16));
insert into BBB values (0, '0');
insert into BBB values (1, '1');

 Now try some queries:

test=> select * from aaa, bbb where bnum = anum;
anum|           aname|ata|bnum|           bname
----+----------------+---+----+----------------
   0|0               |  0|   0|0               
   1|1               |  0|   1|1               
(2 rows)

 It's OK, in both AAA and BBB all fields as expected.

test=> select * from aaa, bbb where bnum = ata;
NOTICE:  ExecInitMergeJoin: left and right sortop's are unequal!
anum|           aname|ata|bnum|           bname
----+----------------+---+----+----------------
   0|0               |  0|   0|0               
   1|1               |  0|   0|0               
(2 rows)

 Why bnum and bname are 0 and '0' respectively? I'm agree - ata and bnum
has different types, but why illegal comparison resets BBB's fields? In the
last query I'm expect for one row in result, where bnum == ata == 0!

 Who is wrong - I'm or PostgreSQL? And why?

---
Vladimir Litovka <doka@webest.com>


Sat, 17 Oct 1998 16:19:15 +0300 (EEST)
Vladimir Litovka <pgsqll@barnet.kharkov.ua>
Is this BUG or FEATURE?
> create table AAA (anum int2, aname char(16), ata int4 default 0);
> insert into AAA values (0, '0');
> insert into AAA values (1, '1');
> -- Note: ata hasn't initialized!

Yes it has. ata = 0 in both because you set a default.

> test=> select * from aaa, bbb where bnum = ata;
> NOTICE:  ExecInitMergeJoin: left and right sortop's are unequal!
> anum|           aname|ata|bnum|           bname
> ----+----------------+---+----+----------------
>    0|0               |  0|   0|0
>    1|1               |  0|   0|0
> (2 rows)

You asked for all cases where bnum = ata. I assume select * from aaa,bbb;
would have returned:

anum|           aname|ata|bnum|           bname
----+----------------+---+----+----------------
   0|0               |  0|   0|0
   0|0               |  0|   1|1
   1|1               |  0|   0|0
   1|1               |  0|   1|1

Now filter for bnum=ata and you get two rows.

Am I wrong here?

Taral


Sat, 17 Oct 1998 12:44:33 -0500
"Taral" <taral@mail.utexas.edu>
RE: [SQL] Is this BUG or FEATURE?
Hi!

On Sat, 17 Oct 1998, Taral wrote:

> anum|           aname|ata|bnum|           bname
> ----+----------------+---+----+----------------
>    0|0               |  0|   0|0
>    0|0               |  0|   1|1
>    1|1               |  0|   0|0
>    1|1               |  0|   1|1
> 
> Now filter for bnum=ata and you get two rows.
> 
> Am I wrong here?

 Egghhhhh....... X-/

 Thank you :)

-- 
Vladimir Litovka <doka@webest.com>


Sat, 17 Oct 1998 20:48:31 +0300 (EEST)
Vladimir Litovka <doka@root.webest.com>
RE: [SQL] Is this BUG or FEATURE?