FWIW, I can confirm that exactly the same thing happens here with the
prebuilt sqlite 3.3.4 (on Windows XP SP2). It's not quite the same with the
prebuilt 2.8.17 binary though - the "select id from three;" query doesn't
give an error there. What is odd is that if you turn the column headers on
(and ignore the name changes going from 2.8.17 to 3.3.4 - presumably that's
what the ticket refers to?), the first column in the view appears to have
the name you're asking for but sqlite says it can't find. ISTM that
inconsistency might be a bug.
+--------------------------------------------------------------------------------------------------+
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> create table one(id integer primary key);
sqlite> create table two(id integer primary key);
sqlite> select one.id,two.id from one join two where one.id=two.id;
sqlite> create view three as select one.id,two.id from one join two where
one.id=two.id;
sqlite>
sqlite> select * from three;
sqlite> select one.id from three;
SQL error: no such column: one.id
sqlite>
sqlite> -- fails in 3.3.4 but not in 2.8.17
sqlite> select id from three;
sqlite>
sqlite> create view four as select one.id as oneid, two.id as twoid from one
join two where one.id=two.id;
sqlite> select oneid from four;
sqlite>
sqlite> .sc
create table one(id integer primary key);
create table two(id integer primary key);
create view four as select one.id as oneid, two.id as twoid from one join
two where one.id=two.id;
create view three as select one.id,two.id from one join two where
one.id=two.id;
sqlite>
sqlite> .mo co
sqlite> .he on
sqlite>
sqlite> insert into one values(123);
sqlite> insert into two values(123);
sqlite>
sqlite> select * from three;
id id_1
---------- ----------
123 123
sqlite>
sqlite> select * from four;
oneid twoid
---------- ----------
123 123
sqlite>
+--------------------------------------------------------------------------------------------------+
SQLite version 3.3.4
Enter ".help" for instructions
sqlite> create table one(id integer primary key);
sqlite> create table two(id integer primary key);
sqlite> select one.id, two.id from one join two where one.id=two.id;
sqlite> create view three as select one.id, two.id from one join two where
one.id=two.id;
sqlite>
sqlite> select * from three;
sqlite> select one.id from three;
SQL error: no such column: one.id
sqlite>
sqlite> -- fails in 3.3.4 but not in 2.8.17
sqlite> select id from three;
SQL error: no such column: id
sqlite>
sqlite> create view four as select one.id as oneid, two.id as twoid from one
join two where one.id=two.id;
sqlite> select oneid from four;
sqlite>
sqlite> .sc
CREATE TABLE one(id integer primary key);
CREATE TABLE two(id integer primary key);
CREATE VIEW four as select one.id as oneid, two.id as twoid from one join
two where one.id=two.id;
CREATE VIEW three as select one.id, two.id from one join two where
one.id=two.id;
sqlite>
sqlite> .mo co
sqlite> .he on
sqlite>
sqlite> insert into one values(123);
sqlite> insert into two values(123);
sqlite> -- one.id couldn't be found earlier!
sqlite> select * from three;
one.id two.id
---------- ----------
123 123
sqlite>
sqlite> select * from four;
oneid twoid
---------- ----------
123 123
sqlite>
+--------------------------------------------------------------------------------------------------+
HTH
Martin
----- Original Message -----
From: "Boris Popov" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Wednesday, March 29, 2006 4:03 AM
Subject: RE: [sqlite] Error when matching column names in a view
To be honest I don't like re-sending messages myself, but it seems this
may
have slipped through the cracks and I would really like to hear whether
it's
SQLite's issue or mine.
Thanks!
-Boris
--
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5
[EMAIL PROTECTED]
CONFIDENTIALITY NOTICE
This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.
Thank you.
-----Original Message-----
From: Boris Popov [mailto:[EMAIL PROTECTED]
Sent: Friday, March 24, 2006 4:16 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Error when matching column names in a view
Any comments would be appreciated,
SQLite version 3.3.4
Enter ".help" for instructions
sqlite> create table one (id integer primary key);
sqlite> create table two (id integer primary key);
sqlite> select one.id, two.id from one join two where one.id=two.id;
sqlite> create view three as select one.id, two.id from one join two where
one.id=two.id;
sqlite> select * from three;
sqlite> select one.id from three;
SQL error: no such column: one.id
sqlite> select id from three;
SQL error: no such column: id
sqlite> create view four as select one.id as oneid, two.id as twoid from
one
join two where one.id=two.id;
sqlite> select oneid from four;
sqlite>
The last one is a workaround to show that you can reference columns by
their
alias, but not their name in a view.
Could this be related to http://www.sqlite.org/cvstrac/chngview?cn=3128 by
any chance?
Thanks!
-Boris
--
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5
[EMAIL PROTECTED]
CONFIDENTIALITY NOTICE
This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.
Thank you.
