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. <[email protected]> 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. <[email protected]> > To: "[email protected]" <[email protected]> > 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
