Icon View Incident Report

Serious Serious
Reported By: Oliver Bock
Reported On: 10/20/2004
For: Version 4.13 Build 1
# 1877 SQL Sub-Queries are Not Working Properly When Containing a WHERE CLause

Non-trivial subqueries are failing. The query below returns nothing, but it should return a row containing '1'.

I cannot characterise the problem completely, but if I remove the '+ 0' it works and if I remove the 'where i = 1' it works. (Obviously my real query is more complicated than this contrived example, but I figured you would prefer something simple.)

Workaround: Run the subquery separately and substitute the values into the query as a string.

create table "\Memory\t"(i integer);
create table "\Memory\u"(i integer);
insert into "\Memory\t" values(1);
insert into "\Memory\u" values(1);
select * from "\Memory\t"
 where i in (select i + 0 from "\Memory\u" 
              where i = 1)

Resolution Resolution
Fixed Problem on 10/21/2004 in version 4.14 build 1