RicardoSQLiteOddity: ricardo.py

import sqlite3.dbapi2 as sqlite

con = sqlite.connect(":memory:")
crsr = con.cursor()
crsr.executescript(open("dumpMenu.sql").read())

crsr.execute('select Name '
             'from menu '
             'inner join path '
                 'on path.Id = menu.MenuId '
             'group by path.Id '
             'having count(*) = 1')    # get the children of root
print [d[0] for d in crsr.description]

#<Output>  ({'Name': u'Hijo'}, {'Name': u'OtroHijo'}) </Output>
#<Comment> This is ok </Comment>

crsr.execute('select menu.Name '
              'from menu '
              'inner join path '
                  'on path.Id = menu.MenuId '
              'group by path.Id '
              'having count(*) = 1')    # get the children of root
print [d[0] for d in crsr.description]

#<Output>  ({'menu.Name': u'Hijo'}, {'menu.Name': u'OtroHijo'}) </Output>
#<Comment> This is ok (notice now it is "menu.Name"
#                      instead of "Name") </Comment>

crsr.execute('select distinct menu.Name '
             'from menu '
             'inner join path '
                'on path.Id = menu.MenuId '
             'group by path.Id '
             'having count(*) = 1')    # get the children of root
print [d[0] for d in crsr.description]

#<Output>  ({'menu.Name': u'Hijo'}, {'menu.Name': u'OtroHijo'}) </Output>
#<Comment> This is ok (with "distinct" I still get the
#          same result "menu.Name") (1) </Comment>

crsr.execute("select distinct menu.Name "
             "from menu "
             "inner join path "
                "on path.Id = menu.MenuId "
             "left join (select MenuId as 'pId' "
                        "from menu "
                        "where name = 'Hijo') Parent "
             "where Id in (select Id "
                          "from path "
                          "where AncestorId = Parent.pId) "
                                 "and Id not in "
                                      "(select Id "
                                       "from path "
                                       "where Id in(select Id "
                                                    "from path "
                                                   "where AncestorId = Parent.pId) "
                                 "and AncestorId not in "
                                      "(select AncestorId "
                                       "from path "
                                       "where Id = Parent.pId) "
                                 "and AncestorId != Parent.pId) ")
print [d[0] for d in crsr.description]

#<Output>  ({'Name': u'Hijo-1'}, {'Name': u'Hijo-2'}) </Output>
#<Comment> Ooops!!! Here I'm using "select distinct menu.name" in the
#          main select
#          as in the previous query (1) but I'm getting "Name" as key
#          instead of "menu.Name".
#          This is the inconsistency I was talking about.
#</Comment>