Обсуждение: Too many duplicated condition query return wrong value

Поиск
Список
Период
Сортировка

Too many duplicated condition query return wrong value

От
Atsushi Yoshida
Дата:
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', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
'ABF0010','ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020', 'ABF0020',
'ABF0020','ABF0020', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030',
'ABF0030','ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0030', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0040',
'ABF0040','ABF0040', 'ABF0040', 'ABF0040', 'ABF0040', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050', 'ABF0050',
'ABF0050','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060',
'ABF0060','ABF0060', 'ABF0060', '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.

Why did the result?
Which log or action do I analyze it?

> arcvideo=> SELECT VERSION();
>                                                version
> ------------------------------------------------------------------------------------------------------
>  PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
> (1 row)

Thanks.
---
http://github.com/yalab

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

Re: Too many duplicated condition query return wrong value

От
Jeff Janes
Дата:


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 the index and the other does not.

Cheers,

Jeff

Re: Too many duplicated condition query return wrong value

От
Atsushi Yoshida
Дата:
>> 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

Re: Too many duplicated condition query return wrong value

От
Jeff Janes
Дата:
On Thu, Sep 3, 2015 at 10:55 PM, Atsushi Yoshida <rudeboyjet@gmail.com> wrote:
>> 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.


>
>  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
 
...
 
>
>  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=6 loops=1)
>    Index Cond: ((sid = 325) AND (lid = ANY ('{ABF0010,ABF0020,ABF0030,ABF0040,ABF0050,ABF0060}'::text[])))
>    Buffers: shared hit=24

 
Is this result aims idx_attend_00 corrupted?
How to fix it?
What countermeasure do I it?

Yes, almost certainly.  You can fix it by rebuilding the index ("REINDEX INDEX idx_attend_00").  Whether this will completely fix the problem depends on what caused it.  There could be a wider corruption issue of which this is only a symptom.

If you would like to preserve the ability to investigate the root cause, you should make a full file-level backup of the database *before* doing the re-index.

What type of index is it?  (I'm now guessing btree, but maybe not)?  Is there a defined time window during which you know the corruption occurred?  If so, do you still have the server logs from that time window?  The WAL logs?

Do you know if the sometimes-missing tuple actually belongs in the table or not?  It could be that the row was marked deleted from the table, scrubbed from the index, and then inappropriately got "revived" like a zombie in the table, so that the "corrupt" index is correct and it is the table that is wrong.

And of course, if you are running with fsync=off or full_page_writes=off, don't do that.

Cheers,

Jeff

Re: Too many duplicated condition query return wrong value

От
Atsushi Yoshida
Дата:
Thank you for your answer Jeff.

I have fixed it.

>> What type of index is it?  (I'm now guessing btree, but maybe not)?  Is there a defined time window during which you
knowthe corruption occurred?  If so, do you still have the server logs from that time window?  The WAL logs? 

Its only btree.
Please tell me which path can I see the logs?

> arcvideo=> \d idx_attend_00
>  Index "public.idx_attend_00"
>  Column |  Type   | Definition
> --------+---------+------------
>  sid    | integer | sid
> btree, for table "public.attend"
>
> arcvideo=> \d  index_attend_on_sid_and_lid
> Index "public.index_attend_on_sid_and_lid"
>  Column |  Type   | Definition
> --------+---------+------------
>  sid    | integer | sid
>  lid    | text    | lid
> unique, btree, for table "public.attend"

If I want to detect the same thing, should I execute REINDEX sometimes?
Do you have any good ideas?

Thanks.
---
http://github.com/yalab

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

> 2015/09/07 7:47、Jeff Janes <jeff.janes@gmail.com> のメール：
>
> On Thu, Sep 3, 2015 at 10:55 PM, Atsushi Yoshida <rudeboyjet@gmail.com> wrote:
> >> Can you give an "explain (analyze, buffers)"  for each query?  Maybe you have a corrupted index, and one query
usesthe index and the other does not. 
>
>
> >
> >  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
>
> ...
>
> >
> >  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.034rows=6 loops=1) 
> >    Index Cond: ((sid = 325) AND (lid = ANY ('{ABF0010,ABF0020,ABF0030,ABF0040,ABF0050,ABF0060}'::text[])))
> >    Buffers: shared hit=24
>
>
> Is this result aims idx_attend_00 corrupted?
> How to fix it?
> What countermeasure do I it?
>
> Yes, almost certainly.  You can fix it by rebuilding the index ("REINDEX INDEX idx_attend_00").  Whether this will
completelyfix the problem depends on what caused it.  There could be a wider corruption issue of which this is only a
symptom.
>
> If you would like to preserve the ability to investigate the root cause, you should make a full file-level backup of
thedatabase *before* doing the re-index. 
>
> What type of index is it?  (I'm now guessing btree, but maybe not)?  Is there a defined time window during which you
knowthe corruption occurred?  If so, do you still have the server logs from that time window?  The WAL logs? 
>
> Do you know if the sometimes-missing tuple actually belongs in the table or not?  It could be that the row was marked
deletedfrom the table, scrubbed from the index, and then inappropriately got "revived" like a zombie in the table, so
thatthe "corrupt" index is correct and it is the table that is wrong. 
>
> And of course, if you are running with fsync=off or full_page_writes=off, don't do that.
>
> Cheers,
>
> Jeff