Re: [sqlite] Improper error message

[David Raymond]

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 <etiennesanch...@gmail.com>
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

[petern]

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

[Cezary H. Noweta]

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

[Richard Hipp]

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

[sqlite] Improper error message

[Etienne Sanchez]

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