[sqlite] sqlite3_column_name() contains quotes for views
I believe I ran into a bug with SQLite, and would like to ensure the problem is
not on my end.
I created a simple table along with a view of it:
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE namesReal (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE ON CONFLICT IGNORE
);
INSERT INTO namesReal VALUES(1,'Linus');
INSERT INTO namesReal VALUES(2,'Bill');
INSERT INTO namesReal VALUES(3,'Steve');
INSERT INTO namesReal VALUES(4,'Richard');
INSERT INTO namesReal VALUES(5,'Ninjas');
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('namesReal',10);
CREATE VIEW names AS SELECT * FROM namesReal;
COMMIT;
-
At this point selecting from names or namesReal should generate the same data:
sqlite .header on
sqlite SELECT id, name FROM namesReal;
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
The data above is good, the column names, as well as the row values. But look
what happens when selecting from the view:
sqlite SELECT id, name FROM names;
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
The quotes are being includes in the column names unlike the prior case.
However when selecting via wildcard, this happens:
sqlite SELECT * FROM names;
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
It appears from these examples, that SQLite mistakenly? is including the
decorations around column names as used by the query for views. Unless I'm
mistaken, column names are supposed to be quoted in SQL in order to prevent
conflict with reserved words.
When trying to query this database with the API, sqlite3_column_name() includes
the quotes around the column name in the second select statement, but not in
the first or third. So it seems the issue is with that function, and not some
quirk of the command line client.
Is this a bug? Or am I doing something wrong?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_name() contains quotes for views
In the example, I just realized something that makes matters worse.
sqlite SELECT id AS id, name AS name FROM names;
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
Despite using a view, using AS seems to remove the quotes.
From: NSRT Mail account. joecool2...@yahoo.com
To: sqlite-users@sqlite.org sqlite-users@sqlite.org
Sent: Monday, October 29, 2012 2:33 PM
Subject: [sqlite] sqlite3_column_name() contains quotes for views
I believe I ran into a bug with SQLite, and would like to ensure the problem is
not on my end.
I created a simple table along with a view of it:
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE namesReal (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE ON CONFLICT IGNORE
);
INSERT INTO namesReal VALUES(1,'Linus');
INSERT INTO namesReal VALUES(2,'Bill');
INSERT INTO namesReal VALUES(3,'Steve');
INSERT INTO namesReal VALUES(4,'Richard');
INSERT INTO namesReal VALUES(5,'Ninjas');
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('namesReal',10);
CREATE VIEW names AS SELECT * FROM namesReal;
COMMIT;
-
At this point selecting from names or namesReal should generate the same data:
sqlite .header on
sqlite SELECT id, name FROM namesReal;
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
The data above is good, the column names, as well as the row values. But look
what happens when selecting from the view:
sqlite SELECT id, name FROM names;
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
The quotes are being includes in the column names unlike the prior case.
However when selecting via wildcard, this happens:
sqlite SELECT * FROM names;
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
It appears from these examples, that SQLite mistakenly? is including the
decorations around column names as used by the query for views. Unless I'm
mistaken, column names are supposed to be quoted in SQL in order to prevent
conflict with reserved words.
When trying to query this database with the API, sqlite3_column_name() includes
the quotes around the column name in the second select statement, but not in
the first or third. So it seems the issue is with that function, and not some
quirk of the command line client.
Is this a bug? Or am I doing something wrong?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_name() contains quotes for views
This problem was fixed here http://www.sqlite.org/src/info/5526e0aa3c.
It will appear in the next version of SQLite.
Pavel
On Mon, Oct 29, 2012 at 5:41 AM, NSRT Mail account.
joecool2...@yahoo.com wrote:
In the example, I just realized something that makes matters worse.
sqlite SELECT id AS id, name AS name FROM names;
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
Despite using a view, using AS seems to remove the quotes.
From: NSRT Mail account. joecool2...@yahoo.com
To: sqlite-users@sqlite.org sqlite-users@sqlite.org
Sent: Monday, October 29, 2012 2:33 PM
Subject: [sqlite] sqlite3_column_name() contains quotes for views
I believe I ran into a bug with SQLite, and would like to ensure the problem
is not on my end.
I created a simple table along with a view of it:
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE namesReal (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE ON CONFLICT IGNORE
);
INSERT INTO namesReal VALUES(1,'Linus');
INSERT INTO namesReal VALUES(2,'Bill');
INSERT INTO namesReal VALUES(3,'Steve');
INSERT INTO namesReal VALUES(4,'Richard');
INSERT INTO namesReal VALUES(5,'Ninjas');
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('namesReal',10);
CREATE VIEW names AS SELECT * FROM namesReal;
COMMIT;
-
At this point selecting from names or namesReal should generate the same data:
sqlite .header on
sqlite SELECT id, name FROM namesReal;
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
The data above is good, the column names, as well as the row values. But look
what happens when selecting from the view:
sqlite SELECT id, name FROM names;
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
The quotes are being includes in the column names unlike the prior case.
However when selecting via wildcard, this happens:
sqlite SELECT * FROM names;
id|name
1|Linus
2|Bill
3|Steve
4|Richard
5|Ninjas
-
It appears from these examples, that SQLite mistakenly? is including the
decorations around column names as used by the query for views. Unless I'm
mistaken, column names are supposed to be quoted in SQL in order to prevent
conflict with reserved words.
When trying to query this database with the API, sqlite3_column_name()
includes the quotes around the column name in the second select statement,
but not in the first or third. So it seems the issue is with that function,
and not some quirk of the command line client.
Is this a bug? Or am I doing something wrong?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
