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" <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 [email protected].
> > > > To unsubscribe from this group, send email to
> > > > [email protected].
> > > > 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
