Informix returning inconsistent resultset

The following simple query I have , is returning 38 rows , although I know that it should return me 40 rows. select * from ledgdet where lindex in (2279561,2245322,2279562,2245323, 2242315, 2242319,2244911,2244915, 2256408 ,2256412 ,2279565, 2242316,2242320,2244912, 2244916, 2256409 ,2256413 ,2279566, 2279563,2279564,2245324,2245325, 2242317,2242318,2242321,2242322,2244913,2244914, 2244917,2244918,2256410,2256411,2256414,2256415, 2256417,2256418,2279563,2279564,2279567,2279568) It is not returning me the rows associated with lindexes 2279563,2279564 if I execute the following query select * from ledgdet where lindex in (2279563,2279564)I am getting those two rows. I am executing this query using dbaccess utility. In addition to that, I already run oncheck on this table. It didn't report any errors. Does anyone have idea other than dropping and recreating indexes? Any help will be greatly appreciated. Thanks in advance

On Wed, 18 Aug 2004 09:57:03 -0400, Hakan Bardavid wrote: > The following simple query I have , is returning 38 rows , although I know > that it should return me 40 rows. > > select * from ledgdet where lindex in > (2279561,2245322,2279562,2245323, > 2242315, > 2242319,2244911,2244915, > 2256408 ,2256412 ,2279565, > 2242316,2242320,2244912, > 2244916, 2256409 ,2256413 ,2279566, > 2279563,2279564,2245324,2245325, > 2242317,2242318,2242321,2242322,2244913,2244914, > 2244917,2244918,2256410,2256411,2256414,2256415, > 2256417,2256418,2279563,2279564,2279567,2279568) Is it possible that you built the query above outside dbaccess in a script and that the query is one or two VERY long lines? Perhaps you just blew dbaccess's line buffer length? If so try breaking it up as it appears in the email into one to four lindex values per line and see what happens. Art S. Kagel > It is not returning me the rows associated with lindexes 2279563,2279564 > > if I execute the following query > select * from ledgdet where lindex in (2279563,2279564) I am getting those> two rows. > > > I am executing this query using dbaccess utility. In addition to that, I > already run oncheck on this table. It didn't report any errors. > > Does anyone have idea other than dropping and recreating indexes? > > Any help will be greatly appreciated. > Thanks in advance

Hakan Bardavid wrote: > The following simple query I have , is returning 38 rows , although > I know that it should return me 40 rows. > > select * from ledgdet where lindex in > (2279561,2245322,2279562,2245323, > 2242315, > 2242319,2244911,2244915, > 2256408 ,2256412 ,2279565, > 2242316,2242320,2244912, > 2244916, 2256409 ,2256413 ,2279566, > 2279563,2279564,2245324,2245325, > 2242317,2242318,2242321,2242322,2244913,2244914, > 2244917,2244918,2256410,2256411,2256414,2256415, > 2256417,2256418,2279563,2279564,2279567,2279568)> > It is not returning me the rows associated with lindexes > 2279563,2279564 > > if I execute the following query > select * from ledgdet where lindex in (2279563,2279564)> I am getting those two rows. > > > I am executing this query using dbaccess utility. In addition to that, > I already run oncheck on this table. It didn't report any errors. Which oncheck options? > Does anyone have idea other than dropping and recreating indexes? What size is the table? What is the query plan (SET EXPLAIN ON)? Which version (complete version) are you using? Which platform? What is the table schema? How many rows of data in the table? **Don't hurry to run UPDATE STATISTICS** When did you last run UPDATE STATISTICS on the table? What options did you use? What are the distributions? Please capture this information *before* runnig UPDATE STATISTICS! Then consider whether to run UPDATE STATISTICS. Wrong results are always alarming. Report the issue to IBM Informix Tech Support. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/