[YSQL][Parallel Query] Sometimes Missing rows when filtering on a sub-plan during index scan

[archit-rastogi]

Jira Link: DB-14901

Description

Build: > 2.25.1.0-b115, ``2.25.1.0-b122, 2.25.1.0-b154`, `2.25.1.0-b172`, `2.25.1.0-b187`

Query:

select
	s_name,
	s_address
from
	supplier,
	nation
where
	s_suppkey in (
		select
			ps_suppkey
		from
			partsupp
		where
			ps_partkey in (
				select
					p_partkey
				from
					part
				where
					p_name like ':1%'
			)
			and ps_availqty > (
				select
					0.5 * sum(l_quantity)
				from
					lineitem
				where
					l_partkey = ps_partkey
					and l_suppkey = ps_suppkey
					and l_shipdate >= date ':2'
					and l_shipdate < date ':2' + interval '1' year
			)
	)
	and s_nationkey = n_nationkey
	and n_name = ':3'
order by
	s_name;

Below describes the query plan node dropping rows on multiple executions.

Returns 1985 rows (expected and correct):

->  Index Scan using partsupp_pkey on partsupp  (cost=180.00..2445289.98 rows=1398 width=8) (actual time=9.566..2751.375 rows=1985 loops=3)
	                          Index Cond: (ps_partkey = ANY (ARRAY[part.p_partkey, $1028, $1029, ..., $2050]))
	                          Filter: ((ps_availqty)::numeric > (SubPlan 1))
	                          Rows Removed by Filter: 966
	                          SubPlan 1
	                            ->  Aggregate  (cost=578.42..578.44 rows=1 width=32) (actual time=0.927..0.927 rows=1 loops=8852)
	                                  ->  Index Scan using l_sk_pk on lineitem  (cost=180.00..578.42 rows=1 width=5) (actual time=0.901..0.902 rows=1 loops=8852)
	                                        Index Cond: ((l_suppkey = partsupp.ps_suppkey) AND (l_partkey = partsupp.ps_partkey))
	                                        Storage Filter: ((l_shipdate >= '1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp without time zone))
	                                        Storage Table Read Requests: 0
	                                        Storage Table Read Execution Time: 0.000 ms
	                                        Storage Table Rows Scanned: 8
	                                        Storage Index Read Requests: 1
	                                        Storage Index Read Execution Time: 0.000 ms
	                                        Storage Index Rows Scanned: 7

Returned 1916 rows

->  Index Scan using partsupp_pkey on partsupp  (cost=180.00..2445289.98 rows=1398 width=8) (actual time=34.957..2685.885 rows=1916 loops=3)
	                          Index Cond: (ps_partkey = ANY (ARRAY[part.p_partkey, $1028, $1029, ..., $2050]))
	                          Filter: ((ps_availqty)::numeric > (SubPlan 1))
	                          Rows Removed by Filter: 937
	                          SubPlan 1
	                            ->  Aggregate  (cost=578.42..578.44 rows=1 width=32) (actual time=0.926..0.926 rows=1 loops=8560)
	                                  ->  Index Scan using l_sk_pk on lineitem  (cost=180.00..578.42 rows=1 width=5) (actual time=0.899..0.900 rows=1 loops=8560)
	                                        Index Cond: ((l_suppkey = partsupp.ps_suppkey) AND (l_partkey = partsupp.ps_partkey))
	                                        Storage Filter: ((l_shipdate >= '1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp without time zone))
	                                        Storage Table Read Requests: 0
	                                        Storage Table Read Execution Time: 0.000 ms
	                                        Storage Table Rows Scanned: 8
	                                        Storage Index Read Requests: 1
	                                        Storage Index Read Execution Time: 0.000 ms
	                                        Storage Index Rows Scanned: 7

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.