select(distinct=...) may produce faulty sql for sqlite

[cassiobotaro]

This one works:

In [45]: db().select(db.player.country, distinct = True)
Out[45]: <Rows (40)>

In [46]: db._lastsql
Out[46]: 'SELECT DISTINCT player.country FROM player;'

But the other option given in the book does not:

In [47]: db().select(db.player.country, distinct = db.player.country)
OperationalError: near "ON": syntax error

In [48]: db._lastsql
Out[48]: 'SELECT DISTINCT ON (player.country) player.country FROM player;'

I didn't test it with other DB engines since I don't currently have one installed.

Related to web2py/web2py#1129

[cassiobotaro]

Searching about this, I discover that sqlite don't has DISTINCT ON feature. How to proceed?

[gi0baro]

@niphlod @ilvalle @mdipierro any ideas?

[niphlod]

This - and others - is the fruit of allowing sintaxes on a per-backend basis.
IMHO lots of postgresql-related features creeped in (as always, without tests) . This landed in the official docs.
To change the behaviour we'd need to change how queries are built, but I don't see any way around:

• DAL allows it and it crashes at execution time, depending on the backend
• DAL removes all code that is not compatible with each and every backend
• DAL raises a syntaxerror depending on the URI

If specific-backend functionality has to be retained, DAL won't be the "universal adapter" that the docs point to (i.e. develop on postgresql, deploy on mysql, spun another instance on sqlite).

And if we retain specific functionality, there should be a clear matrix of backend/capabilities

[gi0baro]

@niphlod Ok, so we should schedule a discussion about this so that we include this in the refactoring of the adapters I've planned for the parsing topic..

[gi0baro]

@niphlod Damn, I ended the refactoring and forgotten this. We should re-start a discussion.
I vote for the SyntaxError on sqlite when using DISTINCT ON.

[niphlod]

start away, my previous semi-detailed comment pretty much lays every path that can be taken.