The boolean issue (1==1) is due to this https://github.com/web2py/web2py/blob/master/gluon/dal/adapters/base.py#L912 We can block it raising a syntax error but it will brake existing apps otherwise I propose this https://github.com/web2py/web2py/pull/580/files
Paolo On Saturday, January 3, 2015 2:03:13 PM UTC+1, Massimo Di Pierro wrote: > > These are not valid DAL expressions: > > db((db.mytable.field1>'C') & 1).select() > db((db.mytable.field1>'C') & (1==1)).select() > db((db.mytable.field1>'C') & 'True').select() > db((db.mytable.field1>'C') & 'TRUE').select() > > the argument of db(....) must be a query or a logical expression comprised > of queries. 1, True, 'True', 'TRUE' are not queries. The fact that > occasionally you do not get an error is purely accidental. You should not > use these expressions. Nowhere in the manual we say this is allowed. Future > versions of web2py may check (and should) and will block them. > > In the other case the problem is that the query should be: > > db(db.mytable.id>0).select(db.mytable.field2, orderby=db.mytable.field2, > groupby=db.mytable.field2) > > and it should work in both cases. > > If you do not specify that you are selecting only field2, they you are > selecting all fields and the query becomes ambiguous (which id should be > used for the grouped records?). Sqlite does not care and returns a random > one. postgresql checks and complains. Apparently this was solved in SQL3 > but most engines do not follow SQL3. > > > http://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function > > The standard SQL distinct syntax (in DAL: > db(...).select(...,distinct=True)) does not solve this problem. So the > postgresql folks introduced DISTINCT ON (in DAL: db(...).select(..., > distinct=field)) but this is a postgresql specific API. > > > http://www.postgresqlforbeginners.com/2010/11/sql-distinct-distinct-on-and-all.html > > Massimo > > > > On Saturday, 3 January 2015 04:42:14 UTC-6, mweissen wrote: >> >> In the last hour I have written a small application which tests (1) the >> boolean expressions and (2) distinct/groupby. You can find this app as >> attach file. >> >> (1) sqlite allows True or 1 or '1' as boolean expression, postgres works >> only with "TRUE" or 'TRUE' >> >> (2) sqlite does not understand distinct and postgres need some complex >> orderby. >> >> >> Testtables Datebase Command Result sqlitedb((db.mytable.field1>'C') & >> (1==1)).select()mytable.idmytable.field1mytable.field24Dy5Ey >> sqlitedb((db.mytable.field1>'C') >> & True).select()mytable.idmytable.field1mytable.field24Dy5Ey >> sqlitedb((db.mytable.field1>'C') >> & 1).select()mytable.idmytable.field1mytable.field24Dy5Ey >> sqlitedb((db.mytable.field1>'C') >> & '1').select()mytable.idmytable.field1mytable.field24Dy5Ey >> sqlitedb((db.mytable.field1>'C') >> & 'TRUE').select()no such column: TRUE sqlitedb((db.mytable.field1>'C') >> & "TRUE").select()no such column: TRUE postgresdb((db.mytable.field1>'C') >> & (1==1)).select()argument of AND must be type boolean, not type integer >> LINE 1: ...le.field2 FROM mytable WHERE ((mytable.field1 > 'C') AND 1); ^ >> postgresdb((db.mytable.field1>'C') & True).select()argument of AND must >> be type boolean, not type integer LINE 1: ...le.field2 FROM mytable WHERE >> ((mytable.field1 > 'C') AND 1); ^ postgresdb((db.mytable.field1>'C') & >> 1).select()argument of AND must be type boolean, not type integer LINE >> 1: ...le.field2 FROM mytable WHERE ((mytable.field1 > 'C') AND 1); ^ >> postgresdb((db.mytable.field1>'C') & '1').select()argument of AND must >> be type boolean, not type integer LINE 1: ...le.field2 FROM mytable WHERE >> ((mytable.field1 > 'C') AND 1); ^ postgresdb((db.mytable.field1>'C') & >> 'TRUE').select()mytable.idmytable.field1mytable.field24Dy5Ey >> postgresdb((db.mytable.field1>'C') >> & "TRUE").select()mytable.idmytable.field1mytable.field24Dy5Ey sqlitedb( >> db.mytable.id>0).select(orderby=db.mytable.field2, >> groupby=db.mytable.field2)mytable.idmytable.field1mytable.field22Bx5Ey >> sqlitedb(db.mytable.id>0).select(orderby=db.mytable.field2, >> distinct=db.mytable.field2)near "ON": syntax error postgresdb( >> db.mytable.id>0).select(orderby=db.mytable.field2, >> groupby=db.mytable.field2)column "mytable.id" must appear in the GROUP >> BY clause or be used in an aggregate function LINE 1: SELECT mytable.id, >> mytable.field1, mytable.field2 FROM myta... ^ >> postgresdb(db.mytable.id>0).select(orderby=db.mytable.field2, >> distinct=db.mytable.field2)mytable.idmytable.field1mytable.field21Ax3Cy >> >> >> 2015-01-03 10:47 GMT+01:00 Marin Pranjić <[email protected] >> <javascript:>>: >> >>> The latter one works only with postgres: >>> https://groups.google.com/forum/#!topic/web2py/1_DHUrrg6O8 >>> >>> What's the error message? >>> >>> On Sat, Jan 3, 2015 at 5:30 AM, Massimo Di Pierro <[email protected] >>> <javascript:>> wrote: >>> >>>> >>>> >>>> On Friday, 2 January 2015 16:03:16 UTC-6, mweissen wrote: >>>>> >>>>> I have tried to convert an application from SQLite to Postgresql and I >>>>> have found two differences: >>>>> >>>>> (1): >>>>> q = some query like db.table.field<3 >>>>> b = some boolean like a<3 >>>>> db(q & b) >>>>> >>>>> SQLite needs to convert b to '0' or '1', Postgresql to 'FALSE' or >>>>> 'TRUE' >>>>> >>>> >>>> yes but web2py does it for you. you should not need to change your code. >>>> >>>> >>>>> >>>>> (2): orderby generates some difficult error messages. I have found the >>>>> following solution >>>>> SQLite >>>>> db(db.table.field==x).select(orderby=db.table.field2, >>>>> groupby=db.table.field2) >>>>> >>>>> Postgresql: >>>>> db(db.table.field==x).select(orderby=db.table.field2, >>>>> distinct=db.table.field2) >>>>> >>>> >>>> Are you sure? I think they both work on both databases. The problems >>>> arise when you groupby some fields and records are not sorted by those >>>> fields. Some engines allow it and some don't. >>>> >>>> >>>>> Does somebody has similar experiences? >>>>> >>>>> Regards, Martin >>>>> >>>>> >>>>> 2015-01-02 16:29 GMT+01:00 Michele Comitini <[email protected] >>>>> <javascript:>>: >>>>> >>>>>> SQLite is by design an excellent embedded database, better suited >>>>>> for single user implementation and targeted at small datasets. >>>>>> For larger datasets there is no reason not to use PostgreSQL, it's >>>>>> free, well supported by web2py, >>>>>> handles very large datasets and it's easy to setup. >>>>>> >>>>>> Migration from SQLite to any other db supported by web2py is very >>>>>> easy and explained on the web2py book >>>>>> >>>>>> 2015-01-02 6:49 GMT+01:00 Eric Taw <[email protected] <javascript:>>: >>>>>> >>>>>>> From reading a lot of web2py tutorials and guides, they always say >>>>>>> SQLite is good for small implementations of databases, whereas other >>>>>>> SQL >>>>>>> flavors are better suited for larger uses. Is there any reason why? Can >>>>>>> I >>>>>>> still use SQLite if I have a lot of information to store? >>>>>>> >>>>>>> -- >>>>>>> Resources: >>>>>>> - http://web2py.com >>>>>>> - http://web2py.com/book (Documentation) >>>>>>> - http://github.com/web2py/web2py (Source code) >>>>>>> - https://code.google.com/p/web2py/issues/list (Report Issues) >>>>>>> --- >>>>>>> You received this message because you are subscribed to the Google >>>>>>> Groups "web2py-users" group. >>>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>>> send an email to [email protected] <javascript:>. >>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>> >>>>>> >>>>>> -- >>>>>> Resources: >>>>>> - http://web2py.com >>>>>> - http://web2py.com/book (Documentation) >>>>>> - http://github.com/web2py/web2py (Source code) >>>>>> - https://code.google.com/p/web2py/issues/list (Report Issues) >>>>>> --- >>>>>> You received this message because you are subscribed to the Google >>>>>> Groups "web2py-users" group. >>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>> send an email to [email protected] <javascript:>. >>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>> >>>>> >>>>> >>>>> -- >>>> Resources: >>>> - http://web2py.com >>>> - http://web2py.com/book (Documentation) >>>> - http://github.com/web2py/web2py (Source code) >>>> - https://code.google.com/p/web2py/issues/list (Report Issues) >>>> --- >>>> You received this message because you are subscribed to the Google >>>> Groups "web2py-users" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected] <javascript:>. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> >> >> >> -- >> Mit freundlichen Grüßen / With kind regards >> Martin Weissenböck >> Gregor-Mendel-Str. 37, 1190 Wien >> Austria / European Union >> >> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
