IDS Forum

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

Posted By: Jean Sagi
Date: Thursday, 14 April 2005, at 4:21 p.m.

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

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

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.