sql: internal error: lookup join with no lookup columns #87306
Comments
yuzefovich
added
C-bug
|
Reduced reproduction: |
|
This occurs on v22.1.6, so this is not a release blocker. |
mgartner
removed
the
release-blocker
|
The problem is that we incorrectly determine that an index used for a lookup join is non-covering when a column in the ON filter is not in the index and conditions on that column form a contradiction that was not eliminated by normalization rules in the canonical expression. This causes us to unnecessarily plan a paired joiner to fetch the column from the primary index. As far as I can tell, there is no correctness issue here, but it does create inefficient plans with paired joiners that could be planned with just a single lookup join. As an example, consider the reproduction above. The secondary index, |
|
Is there some easy way to adjust |
|
This is easier to fix than I originally thought. Will have a PR up momentarily. |
This commit fixes an issue where the optimizer would plan a paired semi or anti lookup join in cases when a single lookup join would suffice. This only occurred in rare cases when the join filter contained a tautology or contradiction that could not be normalized to true or false in the canonical query plan, but could be eliminated from the filters when building a lookup join. If the tautology or contradiction referenced a column not covered by the lookup index, the optimizer mistakenly assumed that the index was not covering and planned a paired join. Now the optimizer can recognize that the index is actually covering, because the referenced column is not needed to evaluate the filters, and a single lookup join is planned. Fixes cockroachdb#87306 Release note (performance improvement): The optimizer now explores a plans with a single lookup join expressions in rare cases where it previously planned two lookup join expressions.
88076: admission: Split stats for WorkQueueMetrics r=irfansharif a=andrewbaptist This commit splits the WorkQueueMetric stats into priority. Because there are a lot of new stats that might be generated as a result of this splitting, only the "important" priorities are collected and considered for each queue. Release note: None 88289: cdc: avoid deadlock on error in pubsub sink r=HonoreDB a=HonoreDB #88130 introduced a deadlock when an attempt to create a topic fails -- the goroutine tries to acquire a lock in order to record the error, but it already has it in order to write to the map. This PR releases the lock while creating the topic, which should also help with performance a bit on startup. Fixes #85374 Release note (bug fix): Fixed a bug preventing pubsub changefeeds from retrying. 88491: opt: do not plan unnecessary paired semi- and anti- lookup joins r=mgartner a=mgartner This commit fixes an issue where the optimizer would plan a paired semi or anti lookup join in cases when a single lookup join would suffice. This only occurred in rare cases when the join filter contained a tautology or contradiction that could not be normalized to true or false in the canonical query plan, but could be eliminated from the filters when building a lookup join. If the tautology or contradiction referenced a column not covered by the lookup index, the optimizer mistakenly assumed that the index was not covering and planned a paired join. Now the optimizer can recognize that the index is actually covering, because the referenced column is not needed to evaluate the filters, and a single lookup join is planned. Fixes #87306 Release note (performance improvement): The optimizer now explores plans with a single lookup join expressions in rare cases where it previously planned two lookup join expressions. 88714: ttl: test multiple TTL storage param changes r=rafiss,ajwerner a=ecwall fixes #88560 Add tests to cover multiple changes to TTL storage params. Release note: None 88756: sql: only record index reads on non-internal sessions r=THardy98 a=THardy98 Resolves: #77598 Previously, explicit `CREATE INDEX` queries were causing unintended index reads in our `index_usage_statistics` subsystem. The unintended index reads were caused by an index validation query from an internal executor that did not use an internal planner. However, the internal executor does set its session data as internal. This change adds a check to only record index reads when the planner is not internal AND the session is not internal. Release note (bug fix): Fixed unintended recordings of index reads caused by internal executor/queries. 88773: sql: support `SHOW GRANTS` for functions r=chengxiong-ruan a=chengxiong-ruan Backport resolves #88495 Release note (sql change): Previously `SHOW GRANTS` only supports db, schema, table and types. This commit add supports for UDFs, so that `SHOW GRANTS` returns UDFs privileges infos, and statements like `SHOW GRANTS ON FUNCTION <udf name/signatures>` are now supported Full function signature must be provided if the function name is not unique. Release justification: low risk GA blocker. 88831: bench: add a benchmark for INSERT RETURNING r=yuzefovich a=yuzefovich Informs #88525. Release note: None Co-authored-by: Andrew Baptist <[email protected]> Co-authored-by: Aaron Zinger <[email protected]> Co-authored-by: Marcus Gartner <[email protected]> Co-authored-by: Evan Wall <[email protected]> Co-authored-by: Thomas Hardy <[email protected]> Co-authored-by: Chengxiong Ruan <[email protected]> Co-authored-by: Yahor Yuzefovich <[email protected]>
This commit fixes an issue where the optimizer would plan a paired semi or anti lookup join in cases when a single lookup join would suffice. This only occurred in rare cases when the join filter contained a tautology or contradiction that could not be normalized to true or false in the canonical query plan, but could be eliminated from the filters when building a lookup join. If the tautology or contradiction referenced a column not covered by the lookup index, the optimizer mistakenly assumed that the index was not covering and planned a paired join. Now the optimizer can recognize that the index is actually covering, because the referenced column is not needed to evaluate the filters, and a single lookup join is planned. Fixes #87306 Release note (performance improvement): The optimizer now explores plans with a single lookup join expressions in rare cases where it previously planned two lookup join expressions.
When running SQLite logic tests with
--crdb_testflag (which is currently not added in CI due to #58089) withseems likely it's related to recent work by @mgartner, so I'm adding a release blocker label.
Jira issue: CRDB-19263
The text was updated successfully, but these errors were encountered: