Thread

1. Re: [HACKERS] Bug on complex subselect (was: Bug on complex join)

   Oleg Broytmann <phd@sun.med.ru> — 1999-03-10T09:33:34Z

   Hello!
   
      I rewrote my 4-tables join to use subselects:
   
   SELECT DISTINCT subsec_id FROM positions
      WHERE pos_id IN
         (SELECT DISTINCT pos_id
            FROM central
               WHERE shop_id IN
                  (SELECT shop_id FROM shops
                     WHERE distr_id IN
                        (SELECT distr_id FROM districts
                           WHERE city_id = 2)
                  )
         )
   ;
   
      This does not work, either - postgres loops forever, until I cancel
   psql.
   
      I splitted it - I ran
   
         (SELECT DISTINCT pos_id
            FROM central
               WHERE shop_id IN
                  (SELECT shop_id FROM shops
                     WHERE distr_id IN
                        (SELECT distr_id FROM districts
                           WHERE city_id = 2)
                  )
         )
   
      and stored result in a file. Then I substituted the subselect with the
   file:
   
   SELECT DISTINCT subsec_id FROM positions
      WHERE pos_id IN
   (1, 2, 3, 6, 22, 25, 26, 27, 28, 29, 31, 33, 34, 35, 38, 41, 42, 44, 45,
   46, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 60, 61, 62, 63, 64)
   
      and got desired result within a second.
   
      This finally solves my problem, but I need to pass a long way to find
   that postgres cannot handle such not too complex joins and subselects.
   
   Oleg.
   ---- 
       Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net
              Programmers don't die, they just GOSUB without RETURN.
   
   
   

2. Re: [HACKERS] Bug on complex join

   Oleg Broytmann <phd@sun.med.ru> — 1999-03-15T13:36:06Z

   Hello!
   
      Now for those who said he cannot beleive there is a bug in join - the
   following query produced two different results on two different systems
   with identical data loaded:
   
   SELECT DISTINCT p.subsec_id
      FROM central cn, shops sh, districts d, positions p
         WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id
         AND   d.city_id = 2 AND cn.pos_id = p.pos_id
   ;
   subsec_id
   ---------
           1
           2
          10
          11
          12
          13
          14
          15
   (8 rows)
   
      on one system and
   
   subsec_id
   ---------
   (0 rows)
   
      on the other.
   
      First system is sparc-solrais and second is intel-linux (Debian 2.0,
   glibc2). Another glibc2-realted bug?
   
   Oleg.
   ---- 
       Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net
              Programmers don't die, they just GOSUB without RETURN.