PostgreSQL Error "SELECT DISTINCT, ORDER BY expressions must appear in select list"

[fuga]

web2py generate SQL that PostgreSQL doesn't accept.

For instance,

SELECT DISTINCT a.id, a.uid, a.name FROM a, b WHERE ((((a.deleted <>
'T') AND (a.id > 0)) AND (((b.deleted <> 'T') AND (b.id > 0)) AND
(b.id = 1))) AND (b.uid = a.uid)) ORDER BY b.id, a.id LIMIT 1 OFFSET
0;

It fails because select list is not contain "b.id".

I change gluon/dal.py(Version 1.94.5) as follows.
It is work well.
But I am not sure that it is a good practice.

=== modified file 'gluon/dal.py' Version 1.94.5
@@ -1039,2 +1039,6 @@
--------------------------------------------------------------
if not orderby and tablenames:
- sql_o += ['%s.%s'%(t,x) for t in tablenames for x in
((hasattr(self.db[t],'_primarykey') and self.db[t]._primarykey) or
[self.db[t]._id.name])]
+ orderby_fields = ['%s.%s'%(t,x) for t in tablenames for x in
((hasattr(self.db[t],'_primarykey') and self.db[t]._primarykey) or
[self.db[t]._id.name])]
+ sql_o += ' ORDER BY %s' % ', '.join(orderby_fields)
+ for orderby_field in orderby_fields:
+ if orderby_field not in self._colnames:
+ sql_f += ', %s' % orderby_field


Your opinion will be much appreciated,
Cheers,

[Massimo Di Pierro]

Can you show us the web2py dal expression that caused the invalid
query? Thanks!

[Ross Peoples]

Showing us the model would help too, especially if you are connecting to a legacy database.

[fuga]

I attach argument-variables that is passed to BaseAdapter.select() (by
using pydev).

Do you mean as such?

I'm working for sahana-eden project.
It will be used for the earthquake rescue of Japan.

http://eden.sahanafoundation.org/wiki

PostgreSQL errors are caused by some querys which succeeds on other DBs.
I want you to cooperate in the solution.

Thanks

2011/4/4 Massimo Di Pierro <massimo....@gmail.com>:

[Massimo Di Pierro]

We need to see the web2py expression that generates the problematic
query. From your comment it seems to me the problem is there.

On Apr 4, 9:24 am, fuga <fug...@gmail.com> wrote:
> I attach argument-variables that is passed to BaseAdapter.select() (by
> using pydev).
>
> Do you mean as such?
>
> I'm working for sahana-eden project.
> It will be used for the earthquake rescue of Japan.
>
> http://eden.sahanafoundation.org/wiki
>
> PostgreSQL errors are caused by some querys which succeeds on other DBs.
> I want you to cooperate in the solution.
>
> Thanks
>

> 2011/4/4 Massimo Di Pierro <massimo.dipie...@gmail.com>:

>  variables
> 2KViewDownload