Bug #121823 LEFT JOIN drops unmatched rows with `(IN (SELECT ...) IS TRUE) = 0`
Description:
MySQL can simplify a `LEFT JOIN` to an inner join when the `WHERE` clause compares `(right_column IN (correlated subquery)) IS TRUE` with zero. The filter accepts null-extended rows, so eliminating the outer join silently omits valid rows.
`Item_in_optimizer::fix_fields()` retains the left operand's `not_null_tables` bits for `BOOL_IS_TRUE`. In this expression, the enclosing `= 0` condition can be true when the operand comes from a null-extended row. The trace shows that the join is simplified as if the complete condition rejected that row. [MySQL 9.7.0 source](https://github.com/mysql/mysql-server/blob/mysql-9.7.0/sql/item_cmpfunc.cc#L2471-L2488)
Maybe related to [WL#4245 commit](https://github.com/mysql/mysql-server/commit/dc80b26d9097bea487e58efa90a1a859ef99f2fc)
How to repeat:
Run the following in an empty schema.
```sql
CREATE TABLE oj_test (id INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO oj_test VALUES (1), (2);
SET optimizer_trace = 'enabled=on';
SELECT l.id FROM oj_test AS l
LEFT JOIN oj_test AS r ON r.id = l.id AND r.id = 1
WHERE ((r.id IN (SELECT s.id + 1 FROM oj_test AS s WHERE s.id = l.id)) IS TRUE) = 0
ORDER BY l.id;
```
## Expected result
```text
id
1
2
```
For `l.id = 1`, the subquery returns `2` and `1 IN (2)` is false. For `l.id = 2`, the right side has no match, so `r.id` is `NULL`. The subquery returns `3` and `NULL IN (3)` is unknown. In both cases, `IS TRUE` returns `0` and the outer comparison succeeds.
## Actual result
```text
id
1
```
The same query returns both rows when the `WHERE` clause is changed to `(r.id IN (SELECT s.id + 1 FROM oj_test AS s WHERE s.id = l.id)) IS NOT TRUE`.
Immediately after running the original `SELECT`, inspect the join-preparation trace.
```sql
SELECT JSON_PRETTY(JSON_EXTRACT(TRACE,
'$.steps[*].join_preparation.steps[*].transformations_to_nested_joins')) AS join_rewrite
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
```
The trace includes these transformations on both MySQL 8.4.9 and 9.7.0.
```json
{
"transformations": [
"outer_join_to_inner_join",
"JOIN_condition_to_WHERE",
"parenthesis_removal"
]
}
```
In the same trace entry, `expanded_query` contains `` from `oj_test` `l` join `oj_test` `r` ``. Both original `ON` predicates, `r.id = l.id` and `r.id = 1`, have moved into `WHERE`.
Suggested fix:
```diff
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index c9d56e4283c..b4103d78da0 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -2721,6 +2721,18 @@ void Item_in_optimizer::update_used_tables() {
}
}
+table_map Item_in_optimizer::not_null_tables() const {
+ const auto *subqpred = down_cast<const Item_in_subselect *>(args[0]);
+ // A NULL-complemented left operand can yield FALSE after IS TRUE.
+ // An enclosing comparison can accept that value, for example
+ // (left_expr IN (SELECT ...) IS TRUE) = 0.
+ if (subqpred->subquery_type() == Item_subselect::IN_SUBQUERY &&
+ subqpred->value_transform == BOOL_IS_TRUE &&
+ !subqpred->m_is_truth_condition)
+ return 0;
+ return not_null_tables_cache;
+}
+
bool Item_func_eq::clean_up_after_removal(uchar *arg) {
Cleanup_after_removal_context *const ctx =
pointer_cast<Cleanup_after_removal_context *>(arg);
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 2d4b8123cd7..b2c2bd239ea 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -541,6 +541,7 @@ class Item_in_optimizer final : public Item_bool_func {
const char *func_name() const override { return "<in_optimizer>"; }
Item_cache **get_cache() { return &cache; }
void update_used_tables() override;
+ table_map not_null_tables() const override;
};
/// Abstract factory interface for creating comparison predicates.
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 1eaad2dc248..fa750705b31 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -621,6 +621,11 @@ class Item_in_subselect : public Item_exists_subselect {
void cleanup() override;
Subquery_type subquery_type() const override { return IN_SUBQUERY; }
+ void apply_is_true() override {
+ m_is_truth_condition = true;
+ super::apply_is_true();
+ }
+
void reset() override {
m_value = false;
null_value = false;
@@ -726,6 +731,9 @@ class Item_in_subselect : public Item_exists_subselect {
bool need_expr_cache{true};
private:
+ // The caller tests this IN predicate for truth.
+ bool m_is_truth_condition{false};
+
bool mark_as_outer(Item *left_row, size_t col);
/**
```
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.058 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.