Re: [sqlite] SELECT issue with SQLite 3.6.10

[KurDtE]

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

[Igor Tandetnik]

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

[Noah Hart]

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

[KurDtE]

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

[Fred Williams]

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

[D. Richard Hipp]

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

[Fred Williams]

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

[D. Richard Hipp]

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