1 Reply Latest reply on Nov 6, 2009 6:25 AM by timo888

    SQLite library bug involving UNION and inline views

    timo888 Level 1

      I just spent over 30 minutes typing in a detailed account of how to reproduce the bug.  Took too long, I guess, because the forum wouldn't let me post and told me to log in.  Everything I typed is gone.

       

      I tried to add the detail to the bug tracking system but there's a bug in the bug-tracking system. The logon dialog is displayed with the password field cut off, so I couldn't log in there.

        • 1. Re: SQLite library bug involving UNION and inline views
          timo888 Level 1

          OK, I've managed to add the following to the bug-tracking system.

           

          -------

           

          I believe I've determined the cause of the problem.

          1. Under certain circumstances (not sure exactly which combinations yet), columns of relations instantiated by inline views must be aliased in order for the outermost statement to reference them.

          2. The outermost statement must also use an explicit column list, not "*", otherwise JOINS of two inline views will returns the columns of only one of the inline views.



          create table CARS("id" int, "year" int, "make" varchar, "model" varchar)
          insert into cars(1, 2009,'Honda','Civic')
          insert into cars(2, 2009,'Honda','Accord')
          insert into cars(3, 2009,'Toyota','Prius')
          insert into cars(4, 2009,'Toyota','Camry')

           

          The following query may look dumb; it's just to show the problem:

          select *

          -- uncomment the next line and comment out the select *above  to see the difference
          --select a.id, b.id, a.make, b.make, a.model, b.model, a.year, b.year
            from 
          (
          select * from cars where make='Honda' 
          union select * from cars where make='Toyota'
          ) as A

          join 
          (
          select * from cars where make='Honda'
          union
          select * from cars where make = 'Toyota') 
          as B


          Only four columns are returned by the query.  

          But do this:

          select a.id, b.id, a.make, b.make, a.model, b.model, a.year, b.year  as BYEAR

          and you can get BYEAR.

           

           

          WORKAROUND
          When an outer statement tries to reference a column or columns returned by the inline view, the inline view column(s) must be aliased and the outer statement cannot be "select *" , it must be "select explicit-column-list".