Select returns nothing. Looks like bug.

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?