Hello,

We had a support request today, and we have narrowed down the problem to a
query that behaves very strangely. The actual query was much more
complicated, but I came up with this minimal example.

This is what we have seen inside our application:

select * from test where id in (26643094740, 26437091668);

id         |code|regno|col_3|
-----------+----+-----+-----+
26437091668|TA-T| 2632|    1|
26643094740|PEG | 2905|    1|

select * from test where id = 26643094740;

id         |code|regno|col_3|
-----------+----+-----+-----+
26643094740|PEG | 2905|    0|

The problem: value of col_3 changes for id=26643094740 if I query two rows
vs. one row. This is without changing any data. The problem is 100%
repeatable, if I query two rows from the same view, then I get different
data for one of the rows.

I suspect that this is a bug. But I might be wrong. Please help me!

The actual test view looks like this:

create view test as
SELECT
    c.id,
    tt.code,
    c.regno,
    (
        select count(*)
        FROM kap.course_user cu
        JOIN wf.workflow w_1 ON w_1.rec_id = cu.id AND w_1.head_table_id =
25408438504
        where cu.is_active AND cu.course_id = c.id AND w_1.station_id =
25406740434
    ) AS col_3
FROM
    kap.course c
    INNER JOIN kap.training_type tt ON tt.id = c.training_type_id;

Below are some DDL s (simplified, the actual tables contain much more
fields).


Do you think that this might be a bug? If not, then can somebody please
explain how this can happen?

   Laszlo

explain analyze select * from test where id in (26643094740, 26437091668);

QUERY PLAN

|
-------------------------------------------------------------------------------------------------------------------------------------------------------+
Hash Join  (cost=16.08..124.99 rows=2 width=29) (actual time=0.067..0.107
rows=2 loops=1)
 |
  Hash Cond: (tt.id = c.training_type_id)
                                                                           |
  ->  Seq Scan on training_type tt  (cost=0.00..12.71 rows=71 width=13)
(actual time=0.004..0.033 rows=71 loops=1)
    |
  ->  Hash  (cost=16.05..16.05 rows=2 width=24) (actual time=0.024..0.025
rows=2 loops=1)
 |
        Buckets: 1024  Batches: 1  Memory Usage: 9kB

|
        ->  Bitmap Heap Scan on course c  (cost=8.58..16.05 rows=2
width=24) (actual time=0.018..0.021 rows=2 loops=1)
        |
              Recheck Cond: (id = ANY
('{26643094740,26437091668}'::bigint[]))
                                      |
              Heap Blocks: exact=2

|
              ->  Bitmap Index Scan on pk_course  (cost=0.00..8.58 rows=2
width=0) (actual time=0.012..0.012 rows=2 loops=1)
  |
                    Index Cond: (id = ANY
('{26643094740,26437091668}'::bigint[]))
                                  |
  SubPlan 1
                                                                           |
    ->  Aggregate  (cost=47.91..47.92 rows=1 width=8) (actual
time=0.014..0.014 rows=1 loops=2)
             |
          ->  Nested Loop  (cost=0.59..47.90 rows=1 width=0) (actual
time=0.010..0.012 rows=1 loops=2)
      |
                ->  Index Scan using workflow_idx_station on workflow w_1
 (cost=0.29..22.90 rows=3 width=8) (actual time=0.005..0.007 rows=1
loops=2) |
                      Index Cond: (station_id = '25406740434'::bigint)

|
                      Filter: (head_table_id = '25408438504'::bigint)
                                                                           |
                ->  Memoize  (cost=0.30..8.32 rows=1 width=8) (actual
time=0.004..0.004 rows=1 loops=2)
     |
                      Cache Key: w_1.rec_id
                                                                           |
                      Hits: 1  Misses: 1  Evictions: 0  Overflows: 0
 Memory Usage: 1kB
       |
                      ->  Index Scan using pk_course_user on course_user cu
 (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)|
                            Index Cond: (id = w_1.rec_id)
                                                                           |
                            Filter: (is_active AND (course_id = c.id))

|
Planning Time: 0.527 ms
                                                                           |
Execution Time: 0.175 ms

|

explain analyze select * from test where id in (26643094740)

QUERY PLAN

|
-------------------------------------------------------------------------------------------------------------------------------------------------------+
Nested Loop  (cost=0.42..64.60 rows=1 width=29) (actual time=0.033..0.035
rows=1 loops=1)
 |
  ->  Index Scan using pk_course on course c  (cost=0.28..8.30 rows=1
width=24) (actual time=0.007..0.008 rows=1 loops=1)
     |
        Index Cond: (id = '26643094740'::bigint)

|
  ->  Index Scan using pk_training_type on training_type tt
 (cost=0.14..8.16 rows=1 width=13) (actual time=0.002..0.002 rows=1
loops=1)               |
        Index Cond: (id = c.training_type_id)
                                                                           |
  SubPlan 1
                                                                           |
    ->  Aggregate  (cost=47.91..47.92 rows=1 width=8) (actual
time=0.018..0.019 rows=1 loops=1)
             |
          ->  Nested Loop  (cost=0.59..47.90 rows=1 width=0) (actual
time=0.017..0.017 rows=0 loops=1)
      |
                ->  Index Scan using workflow_idx_station on workflow w_1
 (cost=0.29..22.90 rows=3 width=8) (actual time=0.007..0.009 rows=1
loops=1) |
                      Index Cond: (station_id = '25406740434'::bigint)

|
                      Filter: (head_table_id = '25408438504'::bigint)
                                                                           |
                ->  Memoize  (cost=0.30..8.32 rows=1 width=8) (actual
time=0.007..0.007 rows=0 loops=1)
     |
                      Cache Key: w_1.rec_id
                                                                           |
                      Hits: 0  Misses: 1  Evictions: 0  Overflows: 0
 Memory Usage: 1kB
       |
                      ->  Index Scan using pk_course_user on course_user cu
 (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)|
                            Index Cond: (id = w_1.rec_id)
                                                                           |
                            Filter: (is_active AND (course_id = c.id))

|
                            Rows Removed by Filter: 1
                                                                           |
Planning Time: 0.528 ms
                                                                           |
Execution Time: 0.096 ms

|


CREATE TABLE kap.course (
  id int8 NOT NULL,
  training_type_id int8 NOT NULL,
  regno int8 NOT NULL,
  -- many more fields here...
  CONSTRAINT pk_course PRIMARY KEY (id),
  CONSTRAINT fk_course_training_type_id FOREIGN KEY (training_type_id)
REFERENCES kap.training_type(id) DEFERRABLE
  -- many more constraints here...
);


CREATE TABLE kap.training_type (
  id int8 NOT NULL,
  code text NOT NULL,
  -- many more fields here
 CONSTRAINT pk_training_type PRIMARY KEY (id)
  -- many more constraints here
);
CREATE UNIQUE INDEX training_type_uidx_code ON kap.training_type USING
btree (code);


CREATE TABLE kap.course_user (
  id int8 NOT NULL,
  is_active bool NOT NULL DEFAULT true,
  course_id int8 NOT NULL,
  sec_user_id int8 NOT NULL,
  CONSTRAINT pk_course_user PRIMARY KEY (id),
  CONSTRAINT fk_course_user_course_id FOREIGN KEY (course_id) REFERENCES
kap.course(id) DEFERRABLE,
  CONSTRAINT fk_course_user_sec_user_id FOREIGN KEY (sec_user_id)
REFERENCES sys.sec_user(id) DEFERRABLE
  -- many more constraints here...
);
CREATE UNIQUE INDEX course_user_uidx ON kap.course_user USING btree
(course_id, sec_user_id);


CREATE TABLE wf.workflow (
  id int8 NOT NULL,
wf_type_id int8 NOT NULL,
head_table_id int8 NOT NULL,
table_info_id int8 NOT NULL,
rec_id int8 NOT NULL,
station_id int8 NOT NULL,
-- many more fields here...
CONSTRAINT pk_workflow PRIMARY KEY (id),
CONSTRAINT fk_workflow_station_id FOREIGN KEY (station_id) REFERENCES
wf.station(id) DEFERRABLE,
CONSTRAINT fk_workflow_table_info_id FOREIGN KEY (table_info_id) REFERENCES
meta.table_info(id) DEFERRABLE,
CONSTRAINT fk_workflow_wf_type_id FOREIGN KEY (wf_type_id) REFERENCES
wf.wf_type(id) DEFERRABLE
-- many more constraints here
);
CREATE UNIQUE INDEX uidx_ht ON wf.workflow USING btree (head_table_id,
rec_id);
CREATE INDEX workflow_idx_station ON wf.workflow USING btree (station_id);
CREATE UNIQUE INDEX workflow_uidx_ht ON wf.workflow USING btree
(head_table_id, rec_id);

Reply via email to