Bug Report: Bug in Query Planner #18987
Description
Overview of the Issue
We have the following sample query:
select foobar.* from foobar where foobar.id in (select id from (select foobar.id, foobar.updated_at from foobar where foobar.fizzbuzz_id = 12345678 and 1 = 0 and foobar.state = 'open' order by foobar.updated_at desc limit 0, 100) as subquery_for_limit) order by foobar.updated_at desc limit 100Given the sample VSchema below, we identified a regression from Variant: "None" in V20 to Variant: "Scatter" in V21 and above. This is also broken on main.
cc: @arthurschreiber @andyedison
Reproduction Steps
- Deploy the following vschema:
"foobar_ks": {
"sharded": true,
"vindexes": {
"foobar_id_ks_idx": {
"type": "lookup_unique",
"params": {
"autocommit": "true",
"from": "id",
"read_lock": "none",
"table": "foobar_id_ks_idx",
"to": "keyspace_id"
},
"owner": "foobar"
},
"hash": {
"type": "hash",
"params": {},
"owner": ""
}
},
"tables": {
"foobar": {
"type": "",
"column_vindexes": [
{
"column": "",
"name": "hash",
"columns": [
"fizzbuzz_id"
]
},
{
"column": "",
"name": "foobar_id_ks_idx",
"columns": [
"id"
]
}
],
"columns": [
{
"name": "id",
"type": "UINT64",
"invisible": false,
"default": "",
"collation_name": "",
"size": 0,
"scale": 0,
"values": []
},
{
"name": "state",
"type": "ENUM",
"invisible": false,
"default": "",
"collation_name": "",
"size": 0,
"scale": 0,
"values": []
},
{
"name": "updated_at",
"type": "DATETIME",
"invisible": false,
"default": "",
"collation_name": "",
"size": 0,
"scale": 0,
"values": []
},
{
"name": "fizzbuzz_id",
"type": "UINT64",
"invisible": false,
"default": "",
"collation_name": "",
"size": 0,
"scale": 0,
"values": []
}
],
"pinned": "",
"column_list_authoritative": true,
"source": ""
},
"foobar_id_ks_idx": {
"type": "",
"column_vindexes": [
{
"column": "",
"name": "hash",
"columns": [
"id"
]
}
],
"auto_increment": null,
"columns": [
{
"name": "id",
"type": "UINT64",
"invisible": false,
"default": "",
"collation_name": "",
"size": 0,
"scale": 0,
"values": []
},
{
"name": "keyspace_id",
"type": "VARBINARY",
"invisible": false,
"default": "",
"collation_name": "",
"size": 0,
"scale": 0,
"values": []
}
],
"pinned": "",
"column_list_authoritative": true,
"source": ""
}
},
"require_explicit_routing": false,
"foreign_key_mode": "unspecified",
"multi_tenant_spec": null- Deploy the following schema:
create table foobar(id bigint, state enum('closed','open'), updated_at datetime, fizzbuzz_id bigint, primary key(id));
Binary Version
v21 to v23Operating System and Environment details
N/ALog Fragments
[{
"Type": "Complex",
"QueryType": "SELECT",
"Original": "select foobar.* from foobar where foobar.id in (select id from (select foobar.id, foobar.updated_at from foobar where foobar.fizzbuzz_id = 12345678 and 1 = 0 and foobar.state = 'open' order by foobar.updated_at desc limit 0, 100) as subquery_for_limit) order by foobar.updated_at desc limit 100",
"Instructions": {
"OperatorType": "Limit",
"Count": "100",
"Inputs": [
{
"OperatorType": "UncorrelatedSubquery",
"Variant": "PulloutIn",
"PulloutVars": [
"__sq_has_values",
"__sq1"
],
"Inputs": [
{
"InputName": "SubQuery",
"OperatorType": "Limit",
"Count": "100",
"Offset": "0",
"Inputs": [
{
"OperatorType": "Route",
"Variant": "Scatter",
"Keyspace": {
"Name": "foobar_ks",
"Sharded": true
},
"FieldQuery": "select id, subquery_for_limit.updated_at from (select foobar.id, foobar.updated_at from foobar where 1 != 1) as subquery_for_limit where 1 != 1",
"OrderBy": "1 DESC",
"Query": "select id, subquery_for_limit.updated_at from (select foobar.id, foobar.updated_at from foobar where 0 and foobar.state = 'open') as subquery_for_limit order by subquery_for_limit.updated_at desc limit 100"
}
]
},
{
"InputName": "Outer",
"OperatorType": "VindexLookup",
"Variant": "IN",
"Keyspace": {
"Name": "foobar_ks",
"Sharded": true
},
"Values": [
"::__sq1"
],
"Vindex": "foobar_id_ks_idx",
"Inputs": [
{
"OperatorType": "Route",
"Variant": "IN",
"Keyspace": {
"Name": "foobar_ks",
"Sharded": true
},
"FieldQuery": "select id, keyspace_id from foobar_id_ks_idx where 1 != 1",
"Query": "select id, keyspace_id from foobar_id_ks_idx where id in ::__vals",
"Values": [
"::id"
],
"Vindex": "hash"
},
{
"OperatorType": "Route",
"Variant": "ByDestination",
"Keyspace": {
"Name": "foobar_ks",
"Sharded": true
},
"FieldQuery": "select id, state, updated_at, fizzbuzz_id from foobar where 1 != 1",
"OrderBy": "2 DESC",
"Query": "select id, state, updated_at, fizzbuzz_id from foobar where :__sq_has_values and foobar.id in ::__vals order by foobar.updated_at desc"
}
]
}
]
}
]
},
"TablesUsed": [
"foobar_ks.foobar"
]
}
]
FAIL
exit status 1
FAIL vitess.io/vitess/go/vt/vtgate/planbuilder 0.028s