#214 orderBy='..' conflicts with distinct=True in select()

open
Oleg Broytman
General (126)
5
2007-09-26
2007-02-27
No

I tried to execute select() with parameters orderBy
and distinct for inheritable tables.
Any of the two parameters can be used alone, but if
they are used together, an error occurs.

The following example is printed in Python shell:

from datetime import datetime
from sqlobject import *
from sqlobject.sqlbuilder import *
from sqlobject.inheritance import InheritableSQLObject
sqlhub.processConnection = connectionForURI('postgres:///test')
class Base(InheritableSQLObject):
created = DateTimeCol(default=DateTimeCol.now())
class BaseSample(Base):
name = StringCol(length=40, alternateID=True)

BaseSample.createTable(ifNotExists=True)
Base.createTable(ifNotExists=True)
sample = BaseSample(name='Sample1')
order=BaseSample.q.created

Two working calls of select().

print list(BaseSample.select(orderBy=order))
[<BaseSample 1L name='Sample1' created='datetime.datetime...)'>]

print list(BaseSample.select(distinct=True))
[<BaseSample 1L name='Sample1' created='datetime.datetime...)'>]

Now the error.

print list(BaseSample.select(orderBy=order, distinct=True))
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1860-py2.4.egg/sqlobject/sresults.py", line 155, in __iter__
return iter(list(self.lazyIter()))
File "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1860-py2.4.egg/sqlobject/sresults.py", line 163, in lazyIter
return conn.iterSelect(self)
File "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1860-py2.4.egg/sqlobject/dbconnection.py", line 364, in iterSelect
select, keepConnection=False)
File "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1860-py2.4.egg/sqlobject/inheritance/iteration.py", line 10, in __init__
super(InheritableIteration, self).__init__(dbconn, rawconn, select, keepConnection)
File "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1860-py2.4.egg/sqlobject/dbconnection.py", line 704, in __init__
self.dbconn._executeRetry(self.rawconn, self.cursor, self.query)
File "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1860-py2.4.egg/sqlobject/dbconnection.py", line 298, in _executeRetry
return cursor.execute(query)
psycopg.ProgrammingError: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

SELECT DISTINCT base_sample.id, base_sample.child_name, base_sample.name FROM base, base_sample WHERE ((1 = 1) AND ((base_sample.id) = (base.id))) ORDER BY base.created

Additional information.

SQLObject-0.7.1dev_r1860
TurboGears-1.0b1
Ubuntu
$ uname -a
Linux svetlana-desktop 2.6.15-28-amd64-generic #1 SMP PREEMPT Thu Feb 1 15:53:41 UTC 2007 x86_64 GNU/Linux

Discussion

  • Oleg Broytman
    Oleg Broytman
    2007-02-28

    • assigned_to: nobody --> phd
    • status: open --> closed-works-for-me
     
  • Oleg Broytman
    Oleg Broytman
    2007-02-28

    Logged In: YES
    user_id=4799
    Originator: NO

    Works in SQLObject 0.8:

    from datetime import datetime
    from sqlobject import *
    from sqlobject.inheritance import InheritableSQLObject
    from sqlobject.sqlbuilder import *

    __connection__ = "postgres:/test?debug=1"

    class Base(InheritableSQLObject):
    created = DateTimeCol(default=DateTimeCol.now())

    class BaseSample(Base):
    name = StringCol(length=40, alternateID=True)

    Base.createTable(ifNotExists=True)
    BaseSample.createTable(ifNotExists=True)

    sample = BaseSample(name='Sample1')
    order=BaseSample.q.created

    print list(BaseSample.select(orderBy=order))
    print list(BaseSample.select(distinct=True))
    print list(BaseSample.select(orderBy=order, distinct=True))

    Debugging output; please note base.created is in the select list in the last SELECT DISTINCT query:

    1/QueryOne: SELECT COUNT(relname) FROM pg_class WHERE relname = 'base'
    1/QueryR : SELECT COUNT(relname) FROM pg_class WHERE relname = 'base'
    1/COMMIT : auto
    1/Query : CREATE TABLE base (
    id SERIAL PRIMARY KEY,
    created TIMESTAMP,
    child_name VARCHAR(255)
    )
    1/QueryR : CREATE TABLE base (
    id SERIAL PRIMARY KEY,
    created TIMESTAMP,
    child_name VARCHAR(255)
    )
    1/COMMIT : auto
    1/QueryOne: SELECT COUNT(relname) FROM pg_class WHERE relname = 'base_sample'
    1/QueryR : SELECT COUNT(relname) FROM pg_class WHERE relname = 'base_sample'
    1/COMMIT : auto
    1/Query : CREATE TABLE base_sample (
    id SERIAL PRIMARY KEY,
    name VARCHAR(40) NOT NULL UNIQUE,
    child_name VARCHAR(255)
    )
    1/QueryR : CREATE TABLE base_sample (
    id SERIAL PRIMARY KEY,
    name VARCHAR(40) NOT NULL UNIQUE,
    child_name VARCHAR(255)
    )
    1/COMMIT : auto
    1/QueryIns: INSERT INTO base (id, child_name, created) VALUES (1, 'BaseSample', '2007-02-28 14:24:22')
    1/COMMIT : auto
    1/QueryOne: SELECT created, child_name FROM base WHERE id = (1)
    1/QueryR : SELECT created, child_name FROM base WHERE id = (1)
    1/COMMIT : auto
    1/QueryIns: INSERT INTO base_sample (id, name, child_name) VALUES (1, 'Sample1', NULL)
    1/COMMIT : auto
    1/QueryOne: SELECT name, child_name FROM base_sample WHERE id = (1)
    1/QueryR : SELECT name, child_name FROM base_sample WHERE id = (1)
    1/COMMIT : auto
    1/Select : SELECT base.id, base.created, base.child_name FROM base WHERE ((base.child_name) = ('BaseSample')) ORDER BY base.created
    1/QueryR : SELECT base.id, base.created, base.child_name FROM base WHERE ((base.child_name) = ('BaseSample')) ORDER BY base.created
    1/Select children of the class BaseSample: SELECT base_sample.id, base_sample.name, base_sample.child_name FROM base_sample WHERE ((base_sample.id) = (1))
    1/QueryR : SELECT base_sample.id, base_sample.name, base_sample.child_name FROM base_sample WHERE ((base_sample.id) = (1))
    1/COMMIT : auto
    [<BaseSample 1L name='Sample1' created='datetime.datetime...)'>]
    1/Select : SELECT DISTINCT base.id, base.created, base.child_name FROM base WHERE ((base.child_name) = ('BaseSample'))
    1/QueryR : SELECT DISTINCT base.id, base.created, base.child_name FROM base WHERE ((base.child_name) = ('BaseSample'))
    1/Select children of the class BaseSample: SELECT base_sample.id, base_sample.name, base_sample.child_name FROM base_sample WHERE ((base_sample.id) = (1))
    1/QueryR : SELECT base_sample.id, base_sample.name, base_sample.child_name FROM base_sample WHERE ((base_sample.id) = (1))
    1/COMMIT : auto
    [<BaseSample 1L name='Sample1' created='datetime.datetime...)'>]
    1/Select : SELECT DISTINCT base.id, base.created, base.child_name FROM base WHERE ((base.child_name) = ('BaseSample')) ORDER BY base.created
    1/QueryR : SELECT DISTINCT base.id, base.created, base.child_name FROM base WHERE ((base.child_name) = ('BaseSample')) ORDER BY base.created
    1/Select children of the class BaseSample: SELECT base_sample.id, base_sample.name, base_sample.child_name FROM base_sample WHERE ((base_sample.id) = (1))
    1/QueryR : SELECT base_sample.id, base_sample.name, base_sample.child_name FROM base_sample WHERE ((base_sample.id) = (1))
    1/COMMIT : auto
    [<BaseSample 1L name='Sample1' created='datetime.datetime...)'>]

    PS. I hope you understand the difference between
    created = DateTimeCol(default=DateTimeCol.now())
    and
    created = DateTimeCol(default=DateTimeCol.now)

     
  • Logged In: YES
    user_id=607106
    Originator: YES

    Thanks a lot for your followup. We have upgraded our system for the use of SO 0.8 (BTW, some changes to our code were
    needed with the names of foreign key fields, perhaps something about possible incompatibility of the versions is due
    for the release notes).

    Indeed, the previous example now works. But the same conflict remains, just the table changed from Base to BaseSample
    for our test.

    As a continuation of your commands:

    print list(BaseSample.select(orderBy=BaseSample.q.name, distinct=True))
    1/Select : SELECT DISTINCT base.id, base.created, base.child_name FROM base, base_sample WHERE (((base.child_name) = ('BaseSample')) AND ((base_sample.id) = (base.id))) ORDER BY base_sample.name
    1/QueryR : SELECT DISTINCT base.id, base.created, base.child_name FROM base, base_sample WHERE (((base.child_name) = ('BaseSample')) AND ((base_sample.id) = (base.id))) ORDER BY base_sample.name
    Traceback (most recent call last):
    File "<stdin>", line 1, in ?
    File "/usr/local/lib/python2.4/site-packages/SQLObject-0.8.0-py2.4.egg/sqlobject/sresults.py", line 160, in __iter__
    return iter(list(self.lazyIter()))
    File "/usr/local/lib/python2.4/site-packages/SQLObject-0.8.0-py2.4.egg/sqlobject/sresults.py", line 168, in lazyIter
    return conn.iterSelect(self)
    File "/usr/local/lib/python2.4/site-packages/SQLObject-0.8.0-py2.4.egg/sqlobject/dbconnection.py", line 374, in iterSelect
    select, keepConnection=False)
    File "/usr/local/lib/python2.4/site-packages/SQLObject-0.8.0-py2.4.egg/sqlobject/inheritance/iteration.py", line 10, in __init__
    super(InheritableIteration, self).__init__(dbconn, rawconn, select, keepConnection)
    File "/usr/local/lib/python2.4/site-packages/SQLObject-0.8.0-py2.4.egg/sqlobject/dbconnection.py", line 760, in __init__
    self.dbconn._executeRetry(self.rawconn, self.cursor, self.query)
    File "/usr/local/lib/python2.4/site-packages/SQLObject-0.8.0-py2.4.egg/sqlobject/dbconnection.py", line 308, in _executeRetry
    return cursor.execute(query)
    psycopg.ProgrammingError: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

    SELECT DISTINCT base.id, base.created, base.child_name FROM base, base_sample WHERE (((base.child_name) = ('BaseSample')) AND ((base_sample.id) = (base.id))) ORDER BY base_sample.name

    Thanks again,
    V. Chukharev

    PS. As a side note, is lack of distinct parameter from Select() intentional and can that be fixed?

    PPS. Thanks for spotting now() insead of now. It was forgotten after some experiments with both variants.

     
    • status: closed-works-for-me --> open-works-for-me
     
  • Oleg Broytman
    Oleg Broytman
    2007-03-19

    Logged In: YES
    user_id=4799
    Originator: NO

    Seems your database is stricter than mine as my Postgres 7.4 allows this:

    9/Select : SELECT DISTINCT base.id, base.created, base.child_name FROM base, base_sample WHERE (((base.child_name) = ('BaseSample')) AND ((base_sample.id) = (base.id))) ORDER BY base_sample.name
    9/QueryR : SELECT DISTINCT base.id, base.created, base.child_name FROM base, base_sample WHERE (((base.child_name) = ('BaseSample')) AND ((base_sample.id) = (base.id))) ORDER BY base_sample.name
    9/Select children of the class BaseSample: SELECT base_sample.id, base_sample.name, base_sample.child_name FROM base_sample WHERE ((base_sample.id) = (1))
    9/QueryR : SELECT base_sample.id, base_sample.name, base_sample.child_name FROM base_sample WHERE ((base_sample.id) = (1))
    [<BaseSample 1 name='Sample1' created='datetime.datetime...)'>]

    Well, that's a problem...

    The lack of 'distinct' parameter from Select() is not intentional - just nobody has implemented it. There is an open bug report about that. It can be fixed, and it's being fixed in the sqlbuilder-views branch by Luke Opperman: http://svn.colorstudy.com/SQLObject/branches/sqlbuilder-views/ . I will ask Luke when and how he is going to merge the branch into the trunk.

     
  • Oleg Broytman
    Oleg Broytman
    2007-09-26

    • status: open-works-for-me --> open
     
  • Oleg Broytman
    Oleg Broytman
    2007-09-26

    Logged In: YES
    user_id=4799
    Originator: NO

    I have upgraded to Postgres 8.1 and now see the problem. The root of the problem is that inheritance works in two stages - first, it draws IDs form the base table and then it draws rows from the appropriate children tables. In your case the first stage fails because inheritance generates SELECT for the base table with ORDER BY a column from the child table. I am still thinking but really I don't see any reasonable fix for that.