sqlite3 how to select the value of a column that matches another column name-CodePudding

If you just create a blank file "touch foo.db" then run this script (test.sql) as "sqlite3 foo.db < test.sql" (half this code is just for formatting the output):

.timer off
.headers off
.mode column
DROP TABLE IF EXISTS Test;
CREATE TABLE Test ("Name" TEXT, "Value" INT);
INSERT INTO Test (Name, Value) VALUES ("A", 1);
INSERT INTO Test (Name, Value) VALUES ("B", 2);
INSERT INTO Test (Name, Value) VALUES ("Value", 3);
SELECT "Query 1 - EVERYTHING" FROM Test WHERE rowid = 1;
SELECT "=====" FROM Test WHERE rowid = 1;
SELECT * FROM Test;
SELECT "Query 2 - EQUALS" FROM Test WHERE rowid = 1;
SELECT "=====" FROM Test WHERE rowid = 1;
SELECT * FROM Test WHERE Name = "Value";
SELECT "Query 3 - LIKE" FROM Test WHERE rowid = 1;
SELECT "=====" FROM Test WHERE rowid = 1;
SELECT * FROM Test WHERE Name LIKE "Value%";
SELECT "Query 4 - Recursive" FROM Test WHERE rowid = 1;
SELECT "=====" FROM Test WHERE rowid = 1;
SELECT * FROM Test WHERE Name IN (SELECT Name FROM Test WHERE Value = 3);

Gives the following results:

Query 1 - EVERYTHING  
=====     
A           1         
B           2         
Value       3         
Query 2 - EQUALS  
=====     
Query 3 - LIKE  
=====     
Value       3         
Query 4 - Recursive  
=====     
Value       3

This seems to indicate sqlite is changing the value of "Value" upon insert since the LIKE and recursion work. Is this a bug in sqlite or is there's something I can do to make the = work?

CodePudding user response：

From the SQLite Keywords doc

'keyword' A keyword in single quotes is a string literal.

"keyword" A keyword in double-quotes is an identifier.

Try this SELECT * FROM Test WHERE Name = 'Value'; instead because Value is an identifier, ie column name.

CodePudding user response：

SQLite does not change the values of the columns.

The problem is the use of "Value" in your queries.

From SQLite Keywords:

If a keyword in double quotes (ex: "key" or "glob") is used in a context where it cannot be resolved to an identifier but where a string literal is allowed, then the token is understood to be a string literal instead of an identifier.

In this statement:

SELECT * FROM Test WHERE Name = "Value";

"Value" can be resolved to an identifier (the column "Value" of the table) and so it is resolved to that identifier and the query is equivalent to:

SELECT * FROM Test WHERE Name = Value;

which does not return any rows.

This is why you should always use single quotes for string literals:

SELECT * FROM Test WHERE Name = 'Value';