Select returns nothing. Looks like bug.
A poster reported a SELECT with CASE WHEN id IN (subselect) returning no rows instead of 3 on IDS 9.40.TC2E1 on WinXP, though an EXISTS rewrite worked. Jonathan Leffler asked for version and PMR, noted TC2 was old and advised getting a support contract, or posting a minimal reproduction to the newsgroup. No fix was given.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
This select returns nothing when in fact it should return 3 rows :
create table t1(id int);
insert into t1 (id) values(0);
insert into t1 (id) values(0);
insert into t1 (id) values(1);
select * from t1
where casewhen id in (select id from t1) then
1
else
0
end = 1;
-------------
It works OK if I modify it to
select * from t1 a1 where (case when exists (select id from t1 wheret1.id =
a1.id ) then 1 else 0 end) = 1
Looks like a bug to me.
On 23 Mar 2006 10:19:08 -0800, smitin@gmail.com <smitin@gmail.com> wrote:
> This select returns nothing when in fact it should return 3 rows :
>
> create table t1(id int);
> insert into t1 (id) values(0);
> insert into t1 (id) values(0);
> insert into t1 (id) values(1);>
> select * from t1
> where case> when id in (select id from t1) then
> 1
> else
> 0
> end = 1;
>
> -------------
> It works OK if I modify it to
>
> select * from t1 a1 where (case when exists (select id from t1 where> t1.id =
> a1.id ) then 1 else 0 end) = 1
>
> Looks like a bug to me.
Which version of IDS, on which platform?
Have you reported it to IBM Informix Tech Support?
Do you have a PMR number for it?
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
9.40.TC2E1, WinXP. No, it's not reported to support. Any idea how to report bugs to support without support contract ?
smitin@gmail.com wrote: > 9.40.TC2E1, WinXP. No, it's not reported to support. Any idea how to > report bugs to support without support contract? TC2 is getting a little old - TC8 is current, I believe. However, it is likely to be a problem in both unless someone else spotted the bug and reported it and we have fixed it. The easy way to get bugs reported is by getting a support contract. Why don't you have support? Is your software legitimate? I assume it is - but expect the question to pass through the minds of everybody when you state you don't have support. If you can't get support, you can try reporting bugs in comp.databases.informix. Include a reproduction of the problem if at all possible (but not if it requires a large attachment; then just say you have a reproduction available, and try to minimize it). Also include the version number and platform information up front, please (yes, I know you didn't know this time - but please remember for the future). You may as well say "I don't have support" too because the first question back will be "Have you reported the bug to IBM/Informix Tech Support". It saves everyone time,. Be aware that customer-reported problems (with cases logged via Tech Support and therefore with PMR numbers) get higher priority than most bugs reported by other backdoor channels - and this is definitely a backdoor channel. More significantly, how do you plan to get fixes to the bugs you report without support?