#46 Error with Correlated Subquery in select-list

open
nobody
None
5
2006-11-15
2006-11-15
Anonymous
No

I believe this problem may be similiar to the issue submitted by crazyjul (Request ID 1206456), but doesn't appear to have been resolved. There seems to be a problem with correlated subqueries in the SQLite Data Provider.

1) Use the following SQL to generate the tables and test data:

create table table1
( symbol varchar(5) );

create table table2
( symbol varchar(5), price numeric );

insert into table1 values ('A');
insert into table2 values ('A',1);
insert into table2 values ('A',2);

2) Then run the following SQL statement:

select table1.symbol, (select max(price) from table2 where table2.symbol = table1.symbol) from table1;

This runs fine from the SQLite command line. However, when running this SQL statement from my c# app using the SQLite Data Provider, an error is thrown that "no such column: table1.symbol".

Let me know if any other information is needed to determine if this is a bug or not.

Thanks!

Discussion

  • Logged In: NO

    Oh, here is snipit of the c# code:

    sSQL = "select table1.symbol, (select max(price) from table2 where table2.symbol = table1.symbol) from table1;";
    DataTable dataTable = new DataTable();
    SQLiteDataAdapter da = new SQLiteDataAdapter (sSQL,oCn);
    da.Fill(dataTable);

    oCn is an open SQLiteConnection.