current_date, etc are functions?
(1) By Tom B (tb13) on 2020-06-12 18:43:22 [link]
Hi All, Can someone explain why current_date, current_time, and current_timestamp show up in the list of available functions, even though they do not have function syntax? CLI output below. SQLite version 3.30.0 2019-10-04 15:03:17 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select name from pragma_function_list where name like 'current%'; current_timestamp current_date current_time sqlite> select current_date(); Error: near "(": syntax error sqlite> select current_date; 2020-06-12 Thanks Tom
(2) By Larry Brasfield (LarryBrasfield) on 2020-06-12 19:04:30 in reply to 1 [link]
What you show, and I confirm for v3.32.2, is a clear departure from the [documented expression syntax](https://sqlite.org/lang_expr.html) using a \<function_name\> token. I cannot explain why.
(3) By Keith Medcalf (kmedcalf) on 2020-06-12 20:59:38 in reply to 1 [link]
Yes, current_date, current_time, and current_timestamp are functions that take no arguments. They are also *special tokens* in the SQL standard in that they are "magical constants" representing the current date, time, and datetime respectively.
(4) By Larry Brasfield (LarryBrasfield) on 2020-06-12 21:17:55 in reply to 3 [link]
Ah, yes! They show up in that [syntax diagram](https://sqlite.org/lang_expr.html) in the button-activated expansion for token **literal\_value**.
(5) By Tom B (tb13) on 2020-06-13 00:45:05 in reply to 4 [link]
Ok I guess. Yet the sqlite_version() function also takes no arguments but the parentheses are required. Regardless it's no biggie. SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select current_date; 2020-06-13 sqlite> select current_date(); Error: near "(": syntax error sqlite> select sqlite_version; Error: no such column: sqlite_version sqlite> select sqlite_version(); 3.31.1 sqlite> Thanks guys Tom
(6) By Larry Brasfield (LarryBrasfield) on 2020-06-13 02:16:18 in reply to 5
As Keith said, those 3 current\_* tokens are special. The scanner (lexical analyzer) recognizes them along with all the keywords (very efficiently by using a specialized hash table) and finally reduces them to a value, TK\_CTIME\_KW, which is all the parser directly sees. They become distinguished when the original (maybe case-folded) text is looked up in the same "table" that pragma\_function\_list shows. The grammar (in parse.y) requires that there be no parenthesis, just as Keith says the SQL standard specifies and the syntax diagram I linked documents. Because of this special processing for just those functions, there is no reason to be surprised that sqlite\_version() needs its parentheses. I think it was a mistake for the SQL standard to require this. Those 3 tokens act like literals in the language, but they are not constants like all other literals; they have more complex behavior such as we associate with functions. It might be considered syntactical sugar, but I think it's mainly an anomalous source of confusion such as you (and I) experienced.
(7) By Stephan Beal (stephan) on 2020-06-13 02:30:16 in reply to 6 [link]
> I think it was a mistake for the SQL standard to require this. Those 3 tokens act like literals in the language, but they are not constants like all other literals; There is actually a reason for it: those constants can be used as default values in column definitions, whereas normal function calls cannot: ```sql sqlite> create table t1(a, b default current_date); sqlite> create table t2(a, b default strftime('%Y','now')); Error: near "(": syntax error sqlite> select strftime('%Y','now'); 2020 sqlite> insert into t1(a) values(1); select * from t1; 1|2020-06-13 ``` Why *those* specific constants are singled out probably boils down to historical behaviour on ancient platforms.
(8) By Kees Nuyt (knu) on 2020-06-13 13:31:57 in reply to 7 [link]
In SQLite, you can use functions in default values, but you have to surround them with parenthesis : ```sql sqlite> create table t1(a, b default current_date); sqlite> create table t2(a, b default strftime('%Y','now')); Error: near "(": syntax error sqlite> create table t2(a, b default (strftime('%Y','now'))); sqlite> insert into t2(a) values(1); select * from t2; 1|2020 ``` ~~~ -- Regards, Kees Nuyt ~~~
(9) By Tom B (tb13) on 2020-06-13 16:45:10 in reply to 6 [link]
Interesting. Seems like a better name for those entities would be "global variables" as opposed to "functions". I'm all for backwards compatibility so if they are considered functions in the 40+ years of SQL nomenclature then ok. Thank you Tom
(10.1) By Keith Medcalf (kmedcalf) on 2020-06-13 21:35:02 edited from 10.0 in reply to 9 [link]
LIKE, REGEXP, and GLOB are also functions and "language elements". That is, `select * from somewhere where a like 'twat%' escape '\';` is equivalent to `select * from somewhere where like('twat%', a, '\');`. The same also applies to the GLOB and REGEXP keywords, which are also functions. While `current_time`, `current_date`, and `current_timestamp` are "language elements", the use of those names as functions is not implemented.