Done. Thank you again for bringing this up!
Massimo
On Dec 16, 1:19 am, jonfroehlich <jonfroehl...@gmail.com> wrote:
> Hi Massimo,
>
> Thanks, as always, for your quick response. I just downloaded
> Mercurial so I could check out the trunk and see your fix. Indeed, the
> modified line "if distinct is True:" fixes my aforementioned problem.
> The code now correctly branches to the "elif distinct." However, all
> is not yet good. :) I had to update sql.py a bit to get it to work
> with PostgresSQL. I would suggest that this modification be added to
> the trunk as well.
>
> The line 2875 was:
> sql_s += ' DISTINCT ON %s' % distinct
>
> I made it:
> sql_s += ' DISTINCT ON (%s)' % distinct
>
> This correctly produces and executes the the PostgresSQL query:
> SELECT DISTINCT ON (sensor_health.sensor_name)
> sensor_health.sensor_name, sensor_health.sensor_started,
> sensor_health.last_heard_from, sensor_health.sampling_rate,
> sensor_health.sensor_event_count FROM sensor_health WHERE
> sensor_health.deployment_site='Jon''s Apartment' ORDER BY
> sensor_health.sensor_name, sensor_health.sensor_started DESC;
>
> Without the parens, PostgresSQL complains with:
> ERROR: syntax error at or near "sensor_health"
> LINE 1: SELECT DISTINCT ON sensor_health.sensor_name
> sensor_health.s...
> ^
>
> With my modification, I have successfully run these two queries (the
> first, distinct set to one field name; the second, distinct set to two
> field names):
>
> sensorHealthRows = db(db.sensor_health.deployment_site ==
> deploymentSite).select(
> db.sensor_health.sensor_name, db.sensor_health.sensor_started,
> db.sensor_health.last_heard_from,
> db.sensor_health.sampling_rate,
> db.sensor_health.sensor_event_count,
> orderby=(db.sensor_health.sensor_name|
> ~db.sensor_health.sensor_started),
> distinct=db.sensor_health.sensor_name)
>
> sensorHealthRows = db(db.sensor_health.deployment_site ==
> deploymentSite).select(
> db.sensor_health.sensor_name, db.sensor_health.sensor_started,
> db.sensor_health.last_heard_from,
> db.sensor_health.sampling_rate,
> db.sensor_health.sensor_event_count,
> orderby=(db.sensor_health.sensor_name|
> ~db.sensor_health.sensor_started),
> distinct=db.sensor_health.sensor_name|
> db.sensor_health.sensor_started)
>
> Jon
>
> On Dec 15, 10:22 pm, "mr.freeze" <nat...@freezable.com> wrote:
>
> > Is DISTINCT ON supported by all RDBS? I am getting OperationalError:
> > near "ON": syntax error with sqlite now.
>
> > On Dec 16, 12:11 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > > The code contains:
>
> > > if distinct == True:
>
> > > This code was designed so that distinct is True or False (that is what
> > > the docs say) and it works. Jon tried to put a db.table.field in it.
> > > Is happens that db.table.field==True is a Query object in web2py and
> > > query objects and not None therefore they evaluates always to true.
> > > Changing this to
>
> > > if distinct is True:
>
> > > fixes the problem. It is very subtle.
>
> > > massimo
>
> > > On Dec 15, 11:38 pm, Jonathan Lundell <jlund...@pobox.com> wrote:
>
> > > > On Dec 15, 2009, at 9:30 PM, mdipierro wrote:
>
> > > > > Good catch! Uploading fix in trunk.
>
> > > > Would you explain the fix, please, to those of us who don't understand
> > > > the subtlety of it? Thanks.
>
> > > > > Massimo
>
> > > > > On Dec 15, 11:13 pm, jonfroehlich <jonfroehl...@gmail.com> wrote:
> > > > >> Although the web2py documentation (page 165) appears to imply that
> > > > >> the
> > > > >> argument distinct can only be set to True or False, the gluon/sql.py
> > > > >> file reveals otherwise. From line 2871 of sql.py:
>
> > > > >> distinct = attributes.get('distinct', False)
> > > > >> if distinct == True:
> > > > >> sql_s += ' DISTINCT'
> > > > >> elif distinct:
> > > > >> sql_s += ' DISTINCT ON %s' % distinct
>
> > > > >> So, it appears as though the code is trying to support providing
> > > > >> specific field names with which to execute the DISTINCT ON expression
> > > > >> upon (the "elif distinct" part). I found a post about this from June
> > > > >> when it looks like the above code was first
> > > > >> proposed:http://groups.google.com/group/web2py/browse_thread/thread/cc6180d335....
> > > > >> The code, however, does not appear to be executing properly. Whether
> > > > >> distinct is set to True or, more importantly, whether it is set to a
> > > > >> field name, the "distinct == True" branch is still taken and the
> > > > >> "elif
> > > > >> distinct" branch is never executed (unfortunately in my case). I
> > > > >> could
> > > > >> be doing something wrong, so let me try to include the relevant
> > > > >> information.
>
> > > > >> I have the following table:
> > > > >> db.define_table('sensor_health',
> > > > >> Field('deployment_site', 'string'),
> > > > >> Field('sensor_name', 'string'),
> > > > >> Field('sensor_started', 'datetime'),
> > > > >> Field('last_heard_from', 'datetime'),
> > > > >> Field('sampling_rate', 'double'),
> > > > >> Field('sensor_event_count', 'integer')
> > > > >> )
>
> > > > >> When executing this query:
> > > > >> sensorHealthRows= db(db.sensor_health.deployment_site ==
> > > > >> deploymentSite).select(
> > > > >> db.sensor_health.sensor_name,
> > > > >> db.sensor_health.sensor_started,
> > > > >> db.sensor_health.last_heard_from,
> > > > >> db.sensor_health.sampling_rate,
> > > > >> db.sensor_health.sensor_event_count,
> > > > >> orderby=(db.sensor_health.sensor_name|
> > > > >> ~db.sensor_health.sensor_started),
> > > > >> distinct=db.sensor_health.sensor_name)
>
> > > > >> Which should be equivalent to this in PostgresSQL:
> > > > >> SELECT DISTINCT ON (sensor_health.sensor_name)
> > > > >> sensor_health.sensor_name, sensor_health.sensor_started,
> > > > >> sensor_health.last_heard_from, sensor_health.sampling_rate,
> > > > >> sensor_health.sensor_event_count
> > > > >> FROM sensor_health
> > > > >> ORDER BY sensor_health.sensor_name,
> > > > >> sensor_health.sensor_started
> > > > >> DESC;
>
> > > > >> I set a breakpoint in sql.py at line 2872 in Wing IDE and the code
> > > > >> does indeed follow the "distinct == True" branch even though I
> > > > >> clearly
> > > > >> set "distinct = db.sensor_health.sensor_name" in my query. Here's
> > > > >> the
> > > > >> even stranger part: using Wing IDE's Debug Probe, I could actually
> > > > >> play around with this a bit (a direct copy/paste from the output is
> > > > >> below). Notice that the type(distinct) is class 'gluon.sql.Field'
> > > > >> and,
> > > > >> perhaps even more interesting, it appears as though distinct
> > > > >> evaluates
> > > > >> to both True and False. What's going on here?
>
> > > > >>>>> print distinct
>
> > > > >> sensor_health.sensor_name>>> distinct == True
>
> > > > >> <gluon.sql.Query object at 0x0393E510>>>> if distinct == True: print
> > > > >> "distinct is True"
>
> > > > >> ...
> > > > >> distinct is True>>> if distinct == False: print "distinct is False"
>
> > > > >> ...
> > > > >> distinct is False>>> type(distinct)
>
> > > > >> <class 'gluon.sql.Field'>
>
> > > > >> Thanks for your help!
>
> > > > >> j
>
> > > > > --
>
> > > > > You received this message because you are subscribed to the Google
> > > > > Groups "web2py-users" group.
> > > > > To post to this group, send email to web...@googlegroups.com.
> > > > > To unsubscribe from this group, send email to
> > > > > web2py+unsubscr...@googlegroups.com.
> > > > > For more options, visit this group
> > > > > athttp://groups.google.com/group/web2py?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to web...@googlegroups.com.
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
