Thread

1. Is this BUG or FEATURE?

   Vladimir Litovka <pgsqll@barnet.kharkov.ua> — 1998-10-17T13:19:15Z

   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>
   
   
   

2. RE: [SQL] Is this BUG or FEATURE?

   Taral <taral@mail.utexas.edu> — 1998-10-17T17:44:33Z

   > 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
   
   
   

3. RE: [SQL] Is this BUG or FEATURE?

   Vladimir Litovka <doka@root.webest.com> — 1998-10-17T17:48:31Z

   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>