Hello Dr Hipp, First of all, I apologize for this rather off-topic suggestion knowing that you may have already implemented the syntax you describe, but there is an IMHO good reason for it, read ahead.
On Tue, Sep 10, 2013 at 10:26 PM, Richard Hipp <d...@sqlite.org> wrote: > SELECT DISTINCT aname > FROM album, composer, track > WHERE unlikely(cname LIKE '%bach%') > AND composer.cid=track.cid > AND album.aid=track.aid; > I would prefer that the planner hint is not interleaved inside normal SQL syntax. Instead I propose a special comment-like syntax instead, as Oracle's /*+ */ or --+, but replacing "+" with another symbol, e.g. ">": SELECT DISTINCT aname > FROM album, composer, track > WHERE cname LIKE '%bach%' > /*> unlikely */ > AND composer.cid=track.cid AND album.aid=track.aid; > or: SELECT DISTINCT aname > FROM album, composer, track > WHERE cname LIKE '%bach%' > --> unlikely > AND composer.cid=track.cid > AND album.aid=track.aid; If the hint is to be applied to an expression that combines many column predicates with AND (I am not sure if this actually makes sense): SELECT DISTINCT aname > FROM album, composer, track > WHERE unlikely(cname LIKE '%bach%' > AND composer.cid=track.cid) > AND album.aid=track.aid; > then a -normally redundant- pair of parentheses can be used to specify the scope of the hint: SELECT DISTINCT aname > FROM album, composer, track > WHERE (cname LIKE '%bach%' AND composer.cid=track.cid) /*> unlikely */ > AND album.aid=track.aid; > The SQLite SQL parser will have to look for exactly "/*>" or "-->" without whitespace between the characters, so it can easily tell a planner hint from a plain comment with a single character read-ahead. Also, the fact that hints are "transparent" to the SQL syntax will allow the query parser to handle them in an "orthogonal" way (e.g. a small separate parser for hints) to normal SQL parsing, IMO making handling of any future hints easier to add. The main reason for this proposal is that the planner hint will be ignored by default by other SQL parsers without the need to modify them, which in some cases may not even be possible. For instance it will allow someone to write SQL that is valid in databases of alternative DB vendors and still provide planner hints when the DB vendor is SQLite (that is why I replaced "+" with ">", to avoid conflicts with a hypothetical alternate Oracle query optimizer) without having to modify the SQL in the application code to remove the hints. This is a property of the Oracle optimizer hint syntax I have always appreciated when writing SQL that is to be executed in databases of alternative DB vendors with the same schema, for applications where the user chooses the database vendor from a list of supported ones. For more on Oracle optimizer hints see http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm. As for the name of the hint itself I would propose: --> PROBABLY(True) -- the current default --> PROBABLY(False) --> PROBABLY(False, 0.7) --> PROBABLY(False, 0.6, 0.3) --re "pedantic detail", the second value if for True, the remainder for NULL. Kind regards, Constantine Yannakopoulos _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
