Improper error message

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Improper error message

Richard Hipp-3
On 2/20/18, Etienne Sanchez <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Improper error message

Cezary H. Noweta
In reply to this post by Etienne Sanchez
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Improper error message

petern
In reply to this post by Etienne Sanchez
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 <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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:[hidden email]] 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 <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users