SQLite Forum

Question regarding dangling commas in CREATE VIRTUAL TABLE queries
Login

Question regarding dangling commas in CREATE VIRTUAL TABLE queries

(1) By Hansheng Zhao (copyright) on 2021-11-03 22:44:39 [link] [source]

Hello there!

It seems that when creating virtual tables, dangling commas are allowed, I'm wondering if there are some reasons to why? Thank you very much in advance!

Here are the testing queries that has been tested to be working on 3.7.17:

CREATE VIRTUAL TABLE test_fts4 USING FTS4(id, name, content='test',);
CREATE VIRTUAL TABLE test_rtree USING RTREE(id, name, content,);

The above and the following queries also tested to be working on 3.32.3:

CREATE VIRTUAL TABLE test_fts5 USING FTS5(id, name, content='test',);

(2) By Richard Hipp (drh) on 2021-11-03 23:56:38 in reply to 1 [link] [source]

It is not a dangling comma. The examples you show all merely have the last parameter equal to an empty string. For both FTS4, FTS5, and RTREE, this results in a column named by an empty string ("").

If you are writing your own virtual tables and want to disallow this, just check the parameters and raise an error if any of them are empty. Perhaps we should have done this when we created FTS4, FTS5, and RTREE. But the time for that decision has passed. Those virtual tables have accepted and allowed column names that are the empty string for many years, and so we cannot change it now without breaking compatibility.

SQLite also allows you to create tables and columns named by an empty string. For example:

CREATE TABLE ""(a INT, "" INT, c INT);
INSERT INTO "" VALUES(1,2,3);
SELECT "" FROM "" WHERE a=1;

You are allowed to do this, but you probably ought not.

(3) By Hansheng Zhao (copyright) on 2021-11-04 01:43:15 in reply to 2 [source]

Hello Richard!

Ah! Thank you very much for the detailed explanations!!

And thank you very much for this wonderful database!

Hope you have a wonderful day :-)

Regards, Hansheng

(4) By doug (doug9forester) on 2021-11-05 14:37:12 in reply to 2 [link] [source]

Perhaps, "Strict" mode should not allow null column names?