Use of CTE's is broken
(1.1) By Balaji Ramanathan (balaji) on 2023-01-23 21:55:00 edited from 1.0 [link] [source]
This kind of query used to work all the way until 3.40 was released. Now, I get an error message.
create table A (RecordID int, RecordName text);
insert into A values (65,"A"), (66,"B"), (67,"C"), (68,"D"),(69,"E"), (70,"F"),(71,"G"), (72,"H"),(73,"I"), (74,"J");
with IDfromA as ( select distinct RecordID from A),
NameFromA as ( select distinct RecordName from A)
select RecordID, RecordName from IDfromA, NamefromA;
I am referencing the previously created CTE's in the final query, and for some reason, this produces an error saying "Error while executing SQL query on database 'DatabaseName': near ")": syntax error .
(2) By Keith Medcalf (kmedcalf) on 2023-01-23 22:08:02 in reply to 1.1 [link] [source]
Write on the chalkboard 1000 times:
double-quotes are for identifiers, single-quotes are for strings.
Once I fix your erroneous use of quotes, the execution is flawless (I cannot test your fubar version because I have prohibited the use of double-quote to indicate a string).
sqlite> create table A
...> (
(x1...> RecordID int,
(x1...> RecordName text
(x1...> );
sqlite> insert into A values (65,'A'), (66,'B'), (67,'C'), (68,'D'),(69,'E'), (70,'F'),(71,'G'), (72,'H'),(73,'I'), (74,'J');
sqlite>
sqlite> with IDfromA as
...> (
(x1...> select distinct RecordID
(x1...> from A
(x1...> ),
...> NameFromA as
...> (
(x1...> select distinct RecordName
(x1...> from A
(x1...> )
...> select RecordID, RecordName
...> from IDfromA, NamefromA;
┌──────────┬────────────┐
│ RecordID │ RecordName │
├──────────┼────────────┤
│ 65 │ 'A' │
│ 65 │ 'B' │
│ 65 │ 'C' │
│ 65 │ 'D' │
│ 65 │ 'E' │
│ 65 │ 'F' │
│ 65 │ 'G' │
│ 65 │ 'H' │
│ 65 │ 'I' │
│ 65 │ 'J' │
│ 66 │ 'A' │
│ 66 │ 'B' │
│ 66 │ 'C' │
│ 66 │ 'D' │
│ 66 │ 'E' │
│ 66 │ 'F' │
│ 66 │ 'G' │
│ 66 │ 'H' │
│ 66 │ 'I' │
│ 66 │ 'J' │
│ 67 │ 'A' │
│ 67 │ 'B' │
│ 67 │ 'C' │
│ 67 │ 'D' │
│ 67 │ 'E' │
│ 67 │ 'F' │
│ 67 │ 'G' │
│ 67 │ 'H' │
│ 67 │ 'I' │
│ 67 │ 'J' │
│ 68 │ 'A' │
│ 68 │ 'B' │
│ 68 │ 'C' │
│ 68 │ 'D' │
│ 68 │ 'E' │
│ 68 │ 'F' │
│ 68 │ 'G' │
│ 68 │ 'H' │
│ 68 │ 'I' │
│ 68 │ 'J' │
│ 69 │ 'A' │
│ 69 │ 'B' │
│ 69 │ 'C' │
│ 69 │ 'D' │
│ 69 │ 'E' │
│ 69 │ 'F' │
│ 69 │ 'G' │
│ 69 │ 'H' │
│ 69 │ 'I' │
│ 69 │ 'J' │
│ 70 │ 'A' │
│ 70 │ 'B' │
│ 70 │ 'C' │
│ 70 │ 'D' │
│ 70 │ 'E' │
│ 70 │ 'F' │
│ 70 │ 'G' │
│ 70 │ 'H' │
│ 70 │ 'I' │
│ 70 │ 'J' │
│ 71 │ 'A' │
│ 71 │ 'B' │
│ 71 │ 'C' │
│ 71 │ 'D' │
│ 71 │ 'E' │
│ 71 │ 'F' │
│ 71 │ 'G' │
│ 71 │ 'H' │
│ 71 │ 'I' │
│ 71 │ 'J' │
│ 72 │ 'A' │
│ 72 │ 'B' │
│ 72 │ 'C' │
│ 72 │ 'D' │
│ 72 │ 'E' │
│ 72 │ 'F' │
│ 72 │ 'G' │
│ 72 │ 'H' │
│ 72 │ 'I' │
│ 72 │ 'J' │
│ 73 │ 'A' │
│ 73 │ 'B' │
│ 73 │ 'C' │
│ 73 │ 'D' │
│ 73 │ 'E' │
│ 73 │ 'F' │
│ 73 │ 'G' │
│ 73 │ 'H' │
│ 73 │ 'I' │
│ 73 │ 'J' │
│ 74 │ 'A' │
│ 74 │ 'B' │
│ 74 │ 'C' │
│ 74 │ 'D' │
│ 74 │ 'E' │
│ 74 │ 'F' │
│ 74 │ 'G' │
│ 74 │ 'H' │
│ 74 │ 'I' │
│ 74 │ 'J' │
└──────────┴────────────┘
sqlite>
(3) By Balaji Ramanathan (balaji) on 2023-01-23 22:18:47 in reply to 2 [link] [source]
Sorry about the wrong quotes. So, I went through my query and yours, and I am not sure where the difference lies between the two. Is there a difference at all? Thank you.
(4) By Keith Medcalf (kmedcalf) on 2023-01-23 22:21:36 in reply to 3 [link] [source]
Only the quotes and some minor reformatting.
(5) By Stephan Beal (stephan) on 2023-01-23 22:23:18 in reply to 3 [link] [source]
Is there a difference at all?
The ability to use double-quotes for strings is a mysql-ism and violates standard SQL, where single-quotes are used for strings. SQLite can be compiled to permit double quotes for strings and it sounds like something in your build process changed to disallow that.
From sqlite3.c:
/* The SQLITE_DQS compile-time option determines the default settings
** for SQLITE_DBCONFIG_DQS_DDL and SQLITE_DBCONFIG_DQS_DML.
**
** SQLITE_DQS SQLITE_DBCONFIG_DQS_DDL SQLITE_DBCONFIG_DQS_DML
** ---------- ----------------------- -----------------------
** undefined on on
** 3 on on
** 2 on off
** 1 off on
** 0 off off
**
** Legacy behavior is 3 (double-quoted string literals are allowed anywhere)
** and so that is the default. But developers are encouranged to use
** -DSQLITE_DQS=0 (best) or -DSQLITE_DQS=1 (second choice) if possible.
*/
(6) By Keith Medcalf (kmedcalf) on 2023-01-23 22:23:42 in reply to 3 [link] [source]
There are three queries.
One is DDL, a create table.
Two is DML, an insert statement.
Three is DML, the SELECT query.
Are you sure about which one of the three queries is producing the error message. You seem to believe there is only one query, but there are three.
(8) By Balaji Ramanathan (balaji) on 2023-01-23 22:29:10 in reply to 6 [source]
The DDL and the insert DML succeed without any issues. I am able to do a select * from A without any problem. It was the final select query that was creating the issue.
But I think the problem is not with SQLite. I was using a front end called SQLiteStudio, and I think the problem lies in that software. When I use the SQLite command line tool, everything works fine whether or not I use single or double quotes for the RecordName entries.
Sorry about the false alarm. I will check in the command line in the future before assuming that there is an issue in SQLite. Thank you for all your quick responses.
(7) By Larry Brasfield (larrybr) on 2023-01-23 22:23:49 in reply to 1.1 [link] [source]
I'll second Keith's guidance and add this:
Your SQL runs fine without anything in the schema beyond what you have shown. This is true of the version you say was used, and its successors.
Further, you must have lost something in producing your post 1 because, despite the poor practice of using identifier syntax where literal syntax is intended, there is no bona fide syntax error. I suspect what was lost is that, in your actual use of that SQL, one of those identifiers must refer to something that is visible as an object name.
The lesson here is clear: Do as Keith says should be written 1000 times.