RicardoSQLiteOddity: ErrorSQLite.txt

ci = dabo.db.dConnectInfo(DbType='SQLite')
ci.Database = 'MultiMenu.db'
conn = dabo.db.dConnection(ci)
crsr = conn.getDaboCursor()
menu = 'Hijo'

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
crsr.getDataSet()

<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
crsr.getDataSet()

<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
crsr.getDataSet()

<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 = '" + menu + "') 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) ")
crsr.getDataSet()

<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>