sqlite3_column_name() contains quotes for views

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3_column_name() contains quotes for views

NSRT Mail account.
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_name() contains quotes for views

NSRT Mail account.
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. <[hidden email]>
To: "[hidden email]" <[hidden email]>
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_name() contains quotes for views

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