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]

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:

```sql
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:

```sql
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]

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

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

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