Re: Too many duplicated condition query return wrong value

On Thu, Sep 3, 2015 at 5:14 AM, Atsushi Yoshida <rudeboyjet(at)gmail(dot)com>
wrote:

> Hi.
>
> I cought a strange result.
> I execute such query.
>
> > SELECT "attend"."lid", "attend"."status" FROM "attend" WHERE
> "attend"."sid" = 325 AND "attend"."lid" IN ('ABF0010', 'ABF0010',
> 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
> 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
> 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',

...

> 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
> 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
> 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060');
>

>
> it return
>
> > lid | status
> > ---------+--------
> > ABF0050 | 9
> > ABF0040 | 9
> > ABF0020 | 9
> > ABF0010 | 9
> > ABF0060 | 9
> > (5 rows)
>
> This IN condition to be unique and execute it like this.
>
> > arcvideo=> SELECT "attend"."lid", "attend"."status" FROM "attend" WHERE
> "attend"."sid" = 325 AND "attend"."lid" IN ('ABF0010', 'ABF0020',
> 'ABF0030', 'ABF0040', 'ABF0050', 'ABF0060’);
>
> It return
>
> > lid | status
> > ---------+--------
> > ABF0010 | 9
> > ABF0020 | 9
> > ABF0030 | 9
> > ABF0040 | 9
> > ABF0050 | 9
> > ABF0060 | 9
> > (6 rows)
>
> First query and second query are same meaning I think, but the result is
> different.
>

Can you give an "explain (analyze, buffers)" for each query? Maybe you
have a corrupted index, and one query uses the index and the other does not.

Cheers,

Jeff