IDS Forum

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

Posted By: Jonathan Leffler
Date: Thursday, 14 April 2005, at 3:45 p.m.

In Response To: Why is this possible? -- Left outer join does not work well? (Jean Sagi)

"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!"

Messages In This Thread

• Why is this possible? -- Left outer join does not work well?
  Jean Sagi -- Thursday, 14 April 2005, at 1:02 p.m.
  • Re: Why is this possible? -- Left outer join does not work well?
    Jonathan Leffler -- Thursday, 14 April 2005, at 3:45 p.m.
  • Re: Re: Why is this possible? -- Left outer join does not work well? - Solved itself
    Jean Sagi -- Thursday, 14 April 2005, at 4:21 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.