Why is this possible? -- Left outer join does not work well?

Generically speaking Why is this possible: -- Counting is 7 select count(*) from d left outer join a on d.f1=a.f1 abd d.f2=a.f2 where d.f3 = _constant_ and d.f4 = _constant_; (count(*)) 7 -- Selecting all records with same previous filter: select * from d left outer join a on d.f1=a.f1 abd d.f2=a.f2 where d.f3 = _constant_ and d.f4 = _constant_; f1 f2 f3 f4 --- --- --- --- 1.............. 2.............. 3.............. 4.............. 5.............. 6.............. 6 row(s) retrieved. Why the countig gives a different number than the number of record returned from the same query/filter? Never happened before. Maybe it is a problem involved with one of the tables? in the previous example table a is almost static and has hundred of records but table d is one of the busiest tables with millions of records... Maybe something is wrong with this table? Any hist would be appreciated. J. PD: What are the proper checks for a table in IDS, somewhere I read they are something like oncheck -Cxxx ... Surely our DBA knows them. Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com

"Jean Sagi" <jeansagi@myrealbox.com> wrote on 04/14/2005 10:02:19 AM: > Generically speaking Why is this possible: > > -- Counting is 7 > select count(*) > from d left outer join a on d.f1=a.f1 abd d.f2=a.f2 > where d.f3 = _constant_ > and d.f4 = _constant_> ; > (count(*)) > 7 Generically speaking: -201: syntax error :-) (AND has an N in the middle) > -- Selecting all records with same previous filter: > select * > from d left outer join a on d.f1=a.f1 abd d.f2=a.f2 > where d.f3 = _constant_ > and d.f4 = _constant_> ; > > f1 f2 f3 f4 > --- --- --- --- > 1.............. > 2.............. > 3.............. > 4.............. > 5.............. > 6.............. > > 6 row(s) retrieved. > > Why the countig gives a different number than the number of record > returned from the same query/filter? > > Never happened before. > > Maybe it is a problem involved with one of the tables? in the > previous example table a is almost static and has hundred of records > but table d is one of the busiest tables with millions of records... > Maybe something is wrong with this table? > > Any hist would be appreciated. > > J. > > PD: What are the proper checks for a table in IDS, somewhere I read > they are something like oncheck -Cxxx ... Surely our DBA knows them. Version? Platform? The normal reason is that someone else changed one of the tables between the two runs - I assume that is not the case here. Where the data doesn't change (REPEATABLE READ isolation, for example), it begins to look like a bug. You'd need to provide the reproduction tables (schema - including indexes) and data, and details of the platform and version. How many of the rows of data in this outer join result have nulls for the values from a? (Your sample data suggests that this could be handled by an inner join, but only if the numbers at the start of the output are from a.f1 rather than being an artefact of your typing.) If you dropped the LEFT OUTER (just an inner join), do you still get mismatches? Does running UPDATE STATISTICS make any difference (it shouldn't). -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Information Management Division 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!"

It solved itself... really ! Maybe my fingers betrayed me? because after some minutes the same query and count(*) version get consistent. Our DBA run cheks on tables involved: oncheck -cDI a oncheck -cDI d And all was rigth and we update statistics every day at night. Some users reported -243 Block errors regarding one of the fields in result set of the above query. That was precisely the missig record. So I assume it was a temporary problem. Thanks for answering and to Julio Gerardo too. J. -----Original Message----- From: Jonathan Leffler <jleffler@us.ibm.com> To: "Jean Sagi" <jeansagi@myrealbox.com> Date: Thu, 14 Apr 2005 12:39:55 -0700 Subject: Re: Why is this possible? -- Left outer join does not work well? [4711] "Jean Sagi" <jeansagi@myrealbox.com> wrote on 04/14/2005 10:02:19 AM: > Generically speaking Why is this possible: > > -- Counting is 7 > select count(*) > from d left outer join a on d.f1=a.f1 abd d.f2=a.f2 > where d.f3 = _constant_ > and d.f4 = _constant_> ; > (count(*)) > 7 Generically speaking: -201: syntax error :-) (AND has an N in the middle) > -- Selecting all records with same previous filter: > select * > from d left outer join a on d.f1=a.f1 abd d.f2=a.f2 > where d.f3 = _constant_ > and d.f4 = _constant_> ; > > f1 f2 f3 f4 > --- --- --- --- > 1.............. > 2.............. > 3.............. > 4.............. > 5.............. > 6.............. > > 6 row(s) retrieved. > > Why the countig gives a different number than the number of record > returned from the same query/filter? > > Never happened before. > > Maybe it is a problem involved with one of the tables? in the > previous example table a is almost static and has hundred of records > but table d is one of the busiest tables with millions of records... > Maybe something is wrong with this table? > > Any hist would be appreciated. > > J. > > PD: What are the proper checks for a table in IDS, somewhere I read > they are something like oncheck -Cxxx ... Surely our DBA knows them. Version? Platform? The normal reason is that someone else changed one of the tables between the two runs - I assume that is not the case here. Where the data doesn't change (REPEATABLE READ isolation, for example), it begins to look like a bug. You'd need to provide the reproduction tables (schema - including indexes) and data, and details of the platform and version. How many of the rows of data in this outer join result have nulls for the values from a? (Your sample data suggests that this could be handled by an inner join, but only if the numbers at the start of the output are from a.f1 rather than being an artefact of your typing.) If you dropped the LEFT OUTER (just an inner join), do you still get mismatches? Does running UPDATE STATISTICS make any difference (it shouldn't). -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Information Management Division 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com