Re: [sqlite] Improper error message
Not quite as original as yours, but SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> attach database 'distinct.sqlite' as "distinct"; sqlite> create table "distinct"."distinct" ("distinct"); sqlite> select distinct "distinct" from "distinct"."distinct" order by "distinct"; --EQP-- 0,0,0,SCAN TABLE distinct --EQP-- 0,0,0,USE TEMP B-TREE FOR DISTINCT sqlite> create table "desc" ("asc", "desc"); sqlite> select "desc", "asc" from "desc" order by "asc" desc, "desc" asc; --EQP-- 0,0,0,SCAN TABLE desc --EQP-- 0,0,0,USE TEMP B-TREE FOR ORDER BY sqlite> Now we just need an "obfuscated SQL" competition... -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of petern Sent: Tuesday, February 20, 2018 7:43 PM To: SQLite mailing list Subject: Re: [sqlite] Improper error message Further to the earlier replies, here is a funny SQLite demo of function, keyword, and column names that is informative about the possibilities. sqlite> .load distinct.so sqlite> SELECT DISTINCT "distinct"() "distinct" WHERE [distinct] NOT NULL; distinct "fn distinct was called" distinct.c extension function source #include "sqlite3ext.h" SQLITE_EXTENSION_INIT1 static void distinct(sqlite3_context *context, int argc, sqlite3_value **argv) { sqlite3_result_text(context, "fn distinct was called", 22, SQLITE_TRANSIENT); } int sqlite3_distinct_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi); int rc = sqlite3_create_function(db, "distinct", -1, SQLITE_UTF8, 0, distinct, 0, 0); return rc; } On Tue, Feb 20, 2018 at 2:59 PM, Etienne Sanchez wrote: > Hi, > > Some other DBMS such as Postgres and SQL-Server implement the functions > "left" and "right". SQLite does not. But why do I get confusing error > messages when I (mistakenly) attempt to use them? > > To illustrate my point: > > > select foo('abc', 2) > Error: no such function: foo > > select left('abc', 2) > Error: near "(": syntax error > > select right('abc', 2) > Error: near "(": syntax error > > > In the 2nd and 3rd cases I would expect a "no such function" error. The > "syntax error" messages are slightly misleading, they lead the user to > think that he has forgotten a parenthesis or a comma somewhere in the > query. > > As for "left", it's maybe due to the ambiguity with "left join", but then > what about "right"? (There is no ambiguity with "right join" since it is > not supported.) > > Thanks, > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improper error message
Further to the earlier replies, here is a funny SQLite demo of function, keyword, and column names that is informative about the possibilities. sqlite> .load distinct.so sqlite> SELECT DISTINCT "distinct"() "distinct" WHERE [distinct] NOT NULL; distinct "fn distinct was called" distinct.c extension function source #include "sqlite3ext.h" SQLITE_EXTENSION_INIT1 static void distinct(sqlite3_context *context, int argc, sqlite3_value **argv) { sqlite3_result_text(context, "fn distinct was called", 22, SQLITE_TRANSIENT); } int sqlite3_distinct_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi); int rc = sqlite3_create_function(db, "distinct", -1, SQLITE_UTF8, 0, distinct, 0, 0); return rc; } On Tue, Feb 20, 2018 at 2:59 PM, Etienne Sanchez wrote: > Hi, > > Some other DBMS such as Postgres and SQL-Server implement the functions > "left" and "right". SQLite does not. But why do I get confusing error > messages when I (mistakenly) attempt to use them? > > To illustrate my point: > > > select foo('abc', 2) > Error: no such function: foo > > select left('abc', 2) > Error: near "(": syntax error > > select right('abc', 2) > Error: near "(": syntax error > > > In the 2nd and 3rd cases I would expect a "no such function" error. The > "syntax error" messages are slightly misleading, they lead the user to > think that he has forgotten a parenthesis or a comma somewhere in the > query. > > As for "left", it's maybe due to the ambiguity with "left join", but then > what about "right"? (There is no ambiguity with "right join" since it is > not supported.) > > Thanks, > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improper error message
Hello, On 2018-02-20 23:59, Etienne Sanchez wrote: Some other DBMS such as Postgres and SQL-Server implement the functions "left" and "right". SQLite does not. But why do I get confusing error messages when I (mistakenly) attempt to use them? To illustrate my point: select foo('abc', 2) Error: no such function: foo select left('abc', 2) Error: near "(": syntax error select right('abc', 2) Error: near "(": syntax error In the 2nd and 3rd cases I would expect a "no such function" error. The "syntax error" messages are slightly misleading, they lead the user to think that he has forgotten a parenthesis or a comma somewhere in the query. As for "left", it's maybe due to the ambiguity with "left join", but then what about "right"? (There is no ambiguity with "right join" since it is not supported.) Indeed, RIGHT is considered as JOIN_KW (look at ``select.c:sqlite3JoinType()'') even if not supported. Furthermore, JOIN_KW is treated as an identifier, when in an expression: ``NATURAL'', ``LEFT'', ``OUTER'', ``RIGHT'', ``FULL'', ``INNER'', and ``CROSS'' -- all are treated as identifiers. So if you hava a column named ``left'' or ``right'', it will be treated accordingly. ``Treated'' does not mean that they are ``becoming'' identifiers, so you cannot use them as function names: id ``('' [``DISTINCT''|``ALL''] expr [, expr [...]] ``)''. If you want to achieve a desired effect (i.e. ``no such function'' message), then copy block ``expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP. {...}'' in ``parse.y'' replacing ``id'' with ``JOIN_KW'', otherwise the parser is expecting that an expression contains the sole ``left''/``right'' or a syntax error occurs. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improper error message
On 2/20/18, Etienne Sanchez wrote: > > As for "left", it's maybe due to the ambiguity with "left join", but then > what about "right"? (There is no ambiguity with "right join" since it is > not supported.) "left" and "right" are keywords in SQL. SQLite understands the RIGHT keyword, even though it does not (yet) implement a RIGHT JOIN. You can force any word to be an identifier by enclosing it in double quotes: SELECT "left"('abc', 2); And it that case, it gives the error message you were expecting. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users