Re: Too many duplicated condition query return wrong value - Mailing list pgsql-hackers

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


> explain (analyze, buffers) 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’…. 
>
>  Index Scan using idx_attend_00 on attend  (cost=0.29..627.20 rows=172 width=12) (actual time=5.158..10.179 rows=5
loops=1)
>    Index Cond: (sid = 325)
>    Filter: (lid = ANY ('{ABF0010,ABF0010,ABF0010,ABF0010,ABF0010 ... ABF0060,ABF0060,ABF0060,ABF0060}'::text[]))
>    Rows Removed by Filter: 414
>    Buffers: shared hit=331
>  Total runtime: 10.196 ms
> (6 rows)

> explain (analyze, buffers) SELECT "attend"."lid", "attend"."status" FROM "attend" WHERE "attend"."sid" = 325 AND
"attend"."lid"IN ('ABF0010', 'ABF0020', 'ABF0030', 'ABF0040', 'ABF0050', 'ABF0060') 
>
>  Index Scan using index_attend_on_sid_and_lid on attend  (cost=0.42..36.32 rows=3 width=12) (actual time=0.011..0.034
rows=6loops=1) 
>    Index Cond: ((sid = 325) AND (lid = ANY ('{ABF0010,ABF0020,ABF0030,ABF0040,ABF0050,ABF0060}'::text[])))
>    Buffers: shared hit=24
>  Total runtime: 0.078 ms
> (4 rows)
>


Is this result aims idx_attend_00 corrupted?
How to fix it?
What countermeasure do I it?
---
http://github.com/yalab

Atsushi YOSHIDA <rudeboyjet@gmail.com>
http://twitter.com/yalab inject your heart

> 2015/09/04 0:58、Jeff Janes <jeff.janes@gmail.com> のメール：
>
>
>
> On Thu, Sep 3, 2015 at 5:14 AM, Atsushi Yoshida <rudeboyjet@gmail.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
theindex and the other does not. 
>
> Cheers,
>
> Jeff