| Bug #94721 | Wrong result with correlated LATERAL JOIN | ||
|---|---|---|---|
| Submitted: | 20 Mar 2019 14:55 | Modified: | 20 Apr 2019 16:59 |
| Reporter: | Dag Wanvik | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.17 | OS: | Any |
| Assigned to: | Steinar Gunderson | CPU Architecture: | Any |
[20 Mar 2019 14:58]
Dag Wanvik
Posted by developer:
This patch gives the correct result (disables iterators):
index 8356e2f5800..6741fa46dbc 100644
--- a/sql/sql_executor.cc
+++ b/sql/sql_executor.cc
@@ -2196,7 +2196,7 @@ static unique_ptr_destroy_only<RowIterator> ConnectJoins(
void JOIN::create_iterators() {
DBUG_ASSERT(m_root_iterator == nullptr);
-
+ return;
if (select_lex->parent_lex->m_sql_cmd != nullptr &&
select_lex->parent_lex->m_sql_cmd->using_secondary_storage_engine()) {
return;
[20 Mar 2019 16:55]
Steinar Gunderson
The basic issue seems to be that for the case of zero input rows, the aggregated value isn't materialized properly. Here's a case where it's slightly clearer what's going on:
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
INSERT INTO t1 VALUES (10), (20), (30);
INSERT INTO t2 VALUES (20), (20);
SELECT t1.id AS id, derived0.cnt AS c FROM t1 JOIN LATERAL (SELECT GROUP_CONCAT(t.id) AS cnt FROM t2 t WHERE (t.id = t1.id)) derived0 ON(1) WHERE 1;
id c
10 NULL
20 20,20
30 20,20
-> Nested loop inner join (cost=9.37 rows=6)
-> Invalidate materialized tables (row from t1)
-> Table scan on t1
-> Table scan on derived0
-> Materialize (invalidate on row from t1)
-> Aggregate: group_concat(t.id separator ',')
-> Filter: (t.id = t1.id)
-> Table scan on t
Normally, the Aggregate node here would be the one responsible for copying the value into derived0, but it doesn't do it properly here.
[20 Apr 2019 16:59]
Jon Stephens
Documented fix in the MySQL 8.0.17 changelog as follows:
When working with derived tables with an aggregation which had
zero input rows, the results of the aggregate functions were not
properly copied into the temporary table. This caused incorrect
results in cases where the derived table was evaluated multiple
times, such as when performing a lateral join.
Closed.
[20 May 2019 8:43]
Erlend Dahl
Bug#95411 LATERAL produces wrong results (values insted of NULLs) on 8.0.16 was marked as a duplicate.
Description: CREATE TABLE t1 (id INT); CREATE TABLE t2 (id INT); INSERT INTO t1 VALUES (1), (2); INSERT INTO t2 VALUES (1); SELECT t1.id AS id, derived0.cnt AS c FROM t1 LEFT JOIN LATERAL (SELECT COUNT(t.id) AS cnt FROM t2 t WHERE (t.id = t1.id)) derived0 ON(1) WHERE 1; +------+------+ | id | c | +------+------+ | 1 | 1 | | 2 | 1 | <--- should be 0 +------+------+ This seems to be a regression after the new iterator execution model was introduced. How to repeat: see description