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);