|
From: SourceForge.net <no...@so...> - 2007-02-28 11:29:15
|
Bugs item #1670239, was opened at 2007-02-27 21:37 Message generated for change (Comment added) made by phd You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1670239&group_id=74338 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: General Group: None >Status: Closed >Resolution: Works For Me Priority: 5 Private: No Submitted By: Vladimir Chukharev (chukharevv) >Assigned to: Oleg Broytmann (phd) Summary: orderBy='..' conflicts with distinct=True in select() Initial Comment: 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 ---------------------------------------------------------------------- >Comment By: Oleg Broytmann (phd) Date: 2007-02-28 14:29 Message: 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) ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1670239&group_id=74338 |
