Re: [sqlite] SELECT issue with SQLite 3.6.10
You got it right Igor ! So I have my table GENERAL with a field ID. I create a view VIEW_GENERAL like this : CREATE VIEW VIEW_GENERAL AS SELECT GENERAL.ID FROM GENERAL Note that this is just an example, in my real case, I needed to select GENERAL.ID instead of ID only because I also do some INNER JOIN on this ID when I create my view. When I use SQLite Administrator I can see that my view has one column called "GENERAL.ID", and with SQLite 3.6.10 in command line I can see : .header ON; SELECT * FROM VIEW_GENERAL; ID test1 test2 test3 test4 So depending on the version of SQLite3 I use, the column is not named the same ... Well, I fixed the problem pretty easily with a SELECT AS statement, I'm just very surprised that such a big change in the behavior has been processed :) Thank you all, Anthony Igor Tandetnik wrote: > > KurDtE wrote: >> I'm getting pretty confused : >> When I execute the query SELECT "GENERAL.ID" FROM VIEW_GENERAL; on >> SQLite Administrator (which uses an older version of SQLite than >> 3.6.10), everything works fine, but when I try the same query with >> SQLite 3.6.10 on command line, I get : >> >> "GENERAL.ID" >> "GENERAL.ID" >> "GENERAL.ID" >> "GENERAL.ID" >> "GENERAL.ID" >> >> meaning that it processes "GENERAL.ID" as text and not as a column >> name ... > > VIEW_GENERAL was created something like this: > > create view VIEW_GENERAL as > select ID from GENERAL; > > Older SQLite versions named the column in the select statement as > "GENERAL.ID". Newer version just names it ID. If you need a specific > name for the column, assign it explicitly: > > create view VIEW_GENERAL as > select ID as "GENERAL.ID" from GENERAL; > > > As to interpreting "GENERAL.ID" as a string literal - this is done for > compatibility with MySQL, if I recall correctly. SQL standard says - > single quotes for string literals, double quotes for identifiers. SQLite > instead looks up a double-quoted string as an identifier, but if it > can't find a suitable one, then it's treated as a string literal. This > is somewhat unfortunate, but apparently a lot of code out there relies > on this feature, so it's difficult to remove. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/SELECT-issue-with-SQLite-3.6.10-tp21682817p21702256.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT issue with SQLite 3.6.10
KurDtE wrote: > I'm getting pretty confused : > When I execute the query SELECT "GENERAL.ID" FROM VIEW_GENERAL; on > SQLite Administrator (which uses an older version of SQLite than > 3.6.10), everything works fine, but when I try the same query with > SQLite 3.6.10 on command line, I get : > > "GENERAL.ID" > "GENERAL.ID" > "GENERAL.ID" > "GENERAL.ID" > "GENERAL.ID" > > meaning that it processes "GENERAL.ID" as text and not as a column > name ... VIEW_GENERAL was created something like this: create view VIEW_GENERAL as select ID from GENERAL; Older SQLite versions named the column in the select statement as "GENERAL.ID". Newer version just names it ID. If you need a specific name for the column, assign it explicitly: create view VIEW_GENERAL as select ID as "GENERAL.ID" from GENERAL; As to interpreting "GENERAL.ID" as a string literal - this is done for compatibility with MySQL, if I recall correctly. SQL standard says - single quotes for string literals, double quotes for identifiers. SQLite instead looks up a double-quoted string as an identifier, but if it can't find a suitable one, then it's treated as a string literal. This is somewhat unfortunate, but apparently a lot of code out there relies on this feature, so it's difficult to remove. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT issue with SQLite 3.6.10
Please supply the SQL TEXT for the TABLE and the VIEW; Noah -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of KurDtE Sent: Tuesday, January 27, 2009 8:41 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SELECT issue with SQLite 3.6.10 First, thank you both for your help ! I'm getting pretty confused : When I execute the query SELECT "GENERAL.ID" FROM VIEW_GENERAL; on SQLite Administrator (which uses an older version of SQLite than 3.6.10), everything works fine, but when I try the same query with SQLite 3.6.10 on command line, I get : "GENERAL.ID" "GENERAL.ID" "GENERAL.ID" "GENERAL.ID" "GENERAL.ID" meaning that it processes "GENERAL.ID" as text and not as a column name ... Weird isn't it ? D. Richard Hipp wrote: > > > On Jan 27, 2009, at 10:08 AM, Fred Williams wrote: > >> >> Should not the GENERAL.ID be enclosed in double quotes? Or did I >> misread >> the SQL Standard? > > You can use double-quotes to conform to the SQL standard. But SQLite > also allows some non-standard quoting mechanisms for compatibility > with other database engines. [...] is used by MS SQL Server. `...` > is used by MySQL. > > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/SELECT-issue-with-SQLite-3.6.10-tp21682817p2168908 2.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT issue with SQLite 3.6.10
First, thank you both for your help ! I'm getting pretty confused : When I execute the query SELECT "GENERAL.ID" FROM VIEW_GENERAL; on SQLite Administrator (which uses an older version of SQLite than 3.6.10), everything works fine, but when I try the same query with SQLite 3.6.10 on command line, I get : "GENERAL.ID" "GENERAL.ID" "GENERAL.ID" "GENERAL.ID" "GENERAL.ID" meaning that it processes "GENERAL.ID" as text and not as a column name ... Weird isn't it ? D. Richard Hipp wrote: > > > On Jan 27, 2009, at 10:08 AM, Fred Williams wrote: > >> >> Should not the GENERAL.ID be enclosed in double quotes? Or did I >> misread >> the SQL Standard? > > You can use double-quotes to conform to the SQL standard. But SQLite > also allows some non-standard quoting mechanisms for compatibility > with other database engines. [...] is used by MS SQL Server. `...` > is used by MySQL. > > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/SELECT-issue-with-SQLite-3.6.10-tp21682817p21689082.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT issue with SQLite 3.6.10
That's what I have always loved about standards! They are so flexible :-) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of D. Richard Hipp Sent: Tuesday, January 27, 2009 9:30 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SELECT issue with SQLite 3.6.10 On Jan 27, 2009, at 10:08 AM, Fred Williams wrote: > > Should not the GENERAL.ID be enclosed in double quotes? Or did I > misread > the SQL Standard? You can use double-quotes to conform to the SQL standard. But SQLite also allows some non-standard quoting mechanisms for compatibility with other database engines. [...] is used by MS SQL Server. `...` is used by MySQL. D. Richard Hipp d...@hwaci.com ___ 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] SELECT issue with SQLite 3.6.10
On Jan 27, 2009, at 10:08 AM, Fred Williams wrote: > > Should not the GENERAL.ID be enclosed in double quotes? Or did I > misread > the SQL Standard? You can use double-quotes to conform to the SQL standard. But SQLite also allows some non-standard quoting mechanisms for compatibility with other database engines. [...] is used by MS SQL Server. `...` is used by MySQL. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT issue with SQLite 3.6.10
Should not the GENERAL.ID be enclosed in double quotes? Or did I misread the SQL Standard? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of D. Richard Hipp Sent: Tuesday, January 27, 2009 8:06 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SELECT issue with SQLite 3.6.10 On Jan 27, 2009, at 5:24 AM, KurDtE wrote: > > Dear all, > > Yesterday I upgraded my SQLite version from version 3.5.9 to 3.6.10, > and I > now experience an issue while making a SELECT on one of my view : > > This query was previously working on version 3.5.9 : > SELECT [GENERAL.ID] FROM VIEW_GENERAL; > > where "GENERAL.ID" is the name of a column in the view. > > I now get this error : GENERAL.ID : no such column. > I think it gets confused with the dot, how to make the query > understand it > as column name, and not table name + column name ? > > Thanks for help, This is probably due to a change (many would say a "bug fix") in version 3.6.0: "The result column names generated for compound subqueries have been simplified to show only the name of the column of the original table and omit the table name. This makes SQLite operate more like other SQL database engines." D. Richard Hipp d...@hwaci.com ___ 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] SELECT issue with SQLite 3.6.10
On Jan 27, 2009, at 5:24 AM, KurDtE wrote: > > Dear all, > > Yesterday I upgraded my SQLite version from version 3.5.9 to 3.6.10, > and I > now experience an issue while making a SELECT on one of my view : > > This query was previously working on version 3.5.9 : > SELECT [GENERAL.ID] FROM VIEW_GENERAL; > > where "GENERAL.ID" is the name of a column in the view. > > I now get this error : GENERAL.ID : no such column. > I think it gets confused with the dot, how to make the query > understand it > as column name, and not table name + column name ? > > Thanks for help, This is probably due to a change (many would say a "bug fix") in version 3.6.0: "The result column names generated for compound subqueries have been simplified to show only the name of the column of the original table and omit the table name. This makes SQLite operate more like other SQL database engines." D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SELECT issue with SQLite 3.6.10
Dear all, Yesterday I upgraded my SQLite version from version 3.5.9 to 3.6.10, and I now experience an issue while making a SELECT on one of my view : This query was previously working on version 3.5.9 : SELECT [GENERAL.ID] FROM VIEW_GENERAL; where "GENERAL.ID" is the name of a column in the view. I now get this error : GENERAL.ID : no such column. I think it gets confused with the dot, how to make the query understand it as column name, and not table name + column name ? Thanks for help, Regards, Anthony -- View this message in context: http://www.nabble.com/SELECT-issue-with-SQLite-3.6.10-tp21682817p21682817.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users