current_date, etc are functions?

(1) By Tom B (tb13) on 2020-06-12 18:43:22 [link] [source]

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] [source]

What you show, and I confirm for v3.32.2, is a clear departure from the documented expression syntax 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 [source]

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] [source]

Ah, yes! They show up in that syntax diagram 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] [source]

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 [link] [source]

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] [source]

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:

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] [source]

In SQLite, you can use functions in default values, but you have to surround them with parenthesis :

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] [source]

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] [source]

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.