One of my colleagues pointed out, that they query returns a different
result, if I cast the head_table_id condition to float8 and then back to
int8.
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::float8::int8 = 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
where c.id in (26437091668, 26643094740)
Returns:
id |code|regno|col_3|
-----------+----+-----+-----+
26437091668|TA-T| 2632| 1|
26643094740|PEG | 2905| 0|
Although all identifier columns are defined as int8.
Les <nagy...@gmail.com> ezt írta (időpont: 2022. okt. 21., P, 19:50):
> 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);
>
