search

yugabyte / yugabyte-db

YugabyteDB - the cloud native distributed SQL database for mission-critical applications.
https://www.yugabyte.com
Other
8.65k stars 1.04k forks source link

[YSQL] Incorrect rows returned during sql query #21529

Open archit-rastogi opened 3 months ago

archit-rastogi commented 3 months ago

Jira Link: DB-10411

Description

Stress run: http://stress.dev.yugabyte.com/stress_test/43b89ebe-0c5c-498e-954c-96066b959631

Issue Type

kind/bug

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

andrei-mart commented 3 months ago

Query: https://github.com/yugabyte/yb-stress-test/blob/pq_perf_lst/src/apps/tpcc/yb/queries/2.sql Query plan: http://stress.dev.yugabyte.com/files/get?name=18a4499e-64e7-480d-92b0-8e56a12aca8e-analyze_without_pq_workers_2.sql.out

BNL there is incorrectly planned. There are two nested BNLs, outer one sets partkey, and inner one sets suppkey. The Index Scan using partsupp_pkey on partsupp correctly updates the index filter, however it also has a regular filter with a SubPlan which also depends on the partkey, but the reference in the subplan is not batched.

It seems that dependency of that kind should prevent BNL.

rthallamko3 commented 3 months ago

Can we check if 2.20 backport is needed as well. cc @tanujnay112

tanujnay112 commented 3 months ago

I think this was fixed by db8eb2596daa8150b03d67b7e3595cd300d169d9 @archit-rastogi Can you confirm this on a more recent release?