Bug Report: Query planner breaks join with derived table containing a subquery with IN/NOT IN
#17867
Comments
arthurschreiber
added
Type: Bug
Needs Triage
|
hii , would like to work on this issue , kinfly assign |
|
Hello @kairveeehh, thanks for your interest in working on this issue! After internal discussions, we believe this might be a bit complex to tackle without more experience with Vitess. However, if you’re confident that you understand the fix and would like to take it on, you’re more than welcome to give it a try. We typically don’t assign issues to individuals, but feel free to open a PR once you have a solution. Just ensure it’s well-tested and includes the necessary unit and end-to-end tests! |
|
Joining @GuptaManan100's sentiment that this particular task is not a good fit for a first contribution. Also, we want to see whether @arthurschreiber has intentions of working on that himself. @arthurschreiber ? |
Sure , would understand it further and inform , also I look forward to contribute to the idea of vitess under GSoC'25 the chatbot based over RAG , had texted over slack but received no response , should I directly go to proposal? |
Overview of the Issue
We have run into a query that gets incorrectly broken up by the Query Planner instead of fully being pushed down to MySQL.
Here's a simplified version of that query (using the vschema located at
go/vt/vtgate/planbuilder/testdata/vschemas/schema.json):The derived table
m2has enough information to be routed to a specific shard (user_extraandmusichave conditions onuser_id, which is ahashvindex). The join betweenm1andm2happens on a shared lookup vindex.The above mentioned conditions should be enough to have the query fully merged by the query planner and pushed down completely to MySQL. Instead, the query is broken up. Here's the query plan (taken on
main):{ "QueryType": "SELECT", "Original": "SELECT m1.id FROM music as m1 JOIN (SELECT max(id) as id FROM music WHERE music.user_id = 1234 AND music.foobar NOT IN (SELECT foobar FROM user_extra WHERE user_extra.user_id = 1234)) as m2 ON m1.id = m2.id", "Instructions": { "OperatorType": "Join", "Variant": "Join", "JoinColumnIndexes": "R:0", "JoinVars": { "m2_id": 0 }, "TableName": "music_music", "Inputs": [ { "OperatorType": "Route", "Variant": "EqualUnique", "Keyspace": { "Name": "user", "Sharded": true }, "FieldQuery": "select m2.id from (select max(id) as id from music where 1 != 1) as m2 where 1 != 1", "Query": "select m2.id from (select max(id) as id from music where music.user_id = 1234 and music.foobar not in (select foobar from user_extra where user_extra.user_id = 1234)) as m2", "Table": "music", "Values": [ "1234" ], "Vindex": "user_index" }, { "OperatorType": "VindexLookup", "Variant": "EqualUnique", "Keyspace": { "Name": "user", "Sharded": true }, "Values": [ ":m2_id" ], "Vindex": "music_user_map", "Inputs": [ { "OperatorType": "Route", "Variant": "IN", "Keyspace": { "Name": "user", "Sharded": true }, "FieldQuery": "select `name`, keyspace_id from name_user_vdx where 1 != 1", "Query": "select `name`, keyspace_id from name_user_vdx where `name` in ::__vals", "Table": "name_user_vdx", "Values": [ "::name" ], "Vindex": "user_index" }, { "OperatorType": "Route", "Variant": "ByDestination", "Keyspace": { "Name": "user", "Sharded": true }, "FieldQuery": "select m1.id from music as m1 where 1 != 1", "Query": "select m1.id from music as m1 where m1.id = :m2_id", "Table": "music" } ] } ] }, "TablesUsed": [ "user.music", "user.user_extra" ] }Variations of this query get merged correctly. For example, a derived table with a nested join:
{ "QueryType": "SELECT", "Original": "SELECT m1.id FROM music as m1 JOIN (SELECT max(id) as id FROM music INNER JOIN user_extra ue ON music.user_id = ue.user_id AND music.foobar = ue.foobar WHERE music.user_id = 1234) as m2 ON m1.id = m2.id", "Instructions": { "OperatorType": "Route", "Variant": "EqualUnique", "Keyspace": { "Name": "user", "Sharded": true }, "FieldQuery": "select m1.id from (select max(id) as id from music, user_extra as ue where 1 != 1) as m2, music as m1 where 1 != 1", "Query": "select m1.id from (select max(id) as id from music, user_extra as ue where music.user_id = 1234 and music.user_id = ue.user_id and music.foobar = ue.foobar) as m2, music as m1 where m1.id = m2.id", "Table": "music, user_extra", "Values": [ "1234" ], "Vindex": "user_index" }, "TablesUsed": [ "user.music", "user.user_extra" ] }The query for the derived table gets merged correctly:
{ "QueryType": "SELECT", "Original": "SELECT max(id) as id FROM music WHERE music.user_id = 1234 AND music.foobar NOT IN (SELECT foobar FROM user_extra WHERE user_extra.user_id = 1234)", "Instructions": { "OperatorType": "Route", "Variant": "EqualUnique", "Keyspace": { "Name": "user", "Sharded": true }, "FieldQuery": "select max(id) as id from music where 1 != 1", "Query": "select max(id) as id from music where music.user_id = 1234 and music.foobar not in (select foobar from user_extra where user_extra.user_id = 1234)", "Table": "music", "Values": [ "1234" ], "Vindex": "user_index" }, "TablesUsed": [ "user.music", "user.user_extra" ] }And a join with a derived table without a subquery works fine too:
{ "QueryType": "SELECT", "Original": "SELECT m1.id FROM music as m1 JOIN (SELECT max(id) as id FROM music WHERE music.user_id = 1234) as m2 ON m1.id = m2.id", "Instructions": { "OperatorType": "Route", "Variant": "EqualUnique", "Keyspace": { "Name": "user", "Sharded": true }, "FieldQuery": "select m1.id from (select max(id) as id from music where 1 != 1) as m2, music as m1 where 1 != 1", "Query": "select m1.id from (select max(id) as id from music where music.user_id = 1234) as m2, music as m1 where m1.id = m2.id", "Table": "music", "Values": [ "1234" ], "Vindex": "user_index" }, "TablesUsed": [ "user.music" ] }Reproduction Steps
N/A
Binary Version
Operating System and Environment details
Log Fragments
No response
The text was updated successfully, but these errors were encountered: