[#MDEV-20368] Wrong Results with Dependent Subquery

create table t0 (a int);

insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (a int, b int);

insert into t1 select a,a from t0 where a <5; # 5 rows

create table t2 as select * from t1 ; # 5 rows

create table t3(a int, b int, c int);

insert into t3 select A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B; # 100 rows

create table t4(a int, b int, c int);

insert into t4 select A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B; # 100 rows

SELECT * FROM t1,t2,t3

  WHERE t1.b=t2.b and

   EXISTS (select 1 from t4 where t4.b=t1.b group by t4.c having t3.b=max(t4.a))

ORDER BY t2.a desc,t1.a desc;

MariaDB [test]> SELECT * FROM t1,t2,t3

    -> WHERE t1.b=t2.b and

    -> EXISTS (select 1 from t4 where t4.b=t1.b group by t4.c having t3.b=max(t4.a))

    -> ORDER BY t2.a desc,t1.a desc;

Empty set (0.009 sec)

mysql> SELECT * FROM t1,t2,t3

    -> WHERE t1.b=t2.b and

    -> EXISTS (select 1 from t4 where t4.b=t1.b group by t4.c having t3.b=max(t4.a))

    -> ORDER BY t2.a desc,t1.a desc;

+------+------+------+------+------+------+------+

| a    | b    | a    | b    | a    | b    | c    |

+------+------+------+------+------+------+------+

|    4 |    4 |    4 |    4 |    4 |    4 |    4 |

|    3 |    3 |    3 |    3 |    3 |    3 |    3 |

|    2 |    2 |    2 |    2 |    2 |    2 |    2 |

|    1 |    1 |    1 |    1 |    1 |    1 |    1 |

|    0 |    0 |    0 |    0 |    0 |    0 |    0 |

+------+------+------+------+------+------+------+

5 rows in set (0.08 sec)

10.3.37-6216a2dfa2faabf8abfd3099a6cd46b00cef4115
$ podman exec -ti m3 mysql -u bob -pbob MDEV-20368 -e 'analyze format=JSON SELECT * FROM t1,t2,t3

WHERE t1.b=t2.b and

EXISTS (select 1 from t4 where t4.b=t1.b group by t4.c having t3.b=max(t4.a))

ORDER BY t2.a desc,t1.a desc\G'
************************* 1. row *************************
ANALYZE: {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.2292,
"filesort": {
"sort_key": "t2.a desc, t1.a desc",
"r_loops": 1,
"r_total_time_ms": 3.4e-4,
"r_used_priority_queue": false,
"r_output_rows": 0,
"r_buffer_size": "390",
"temporary_table": {
"table": {
"table_name": "t1",
"access_type": "ALL",
"r_loops": 1,
"rows": 5,
"r_rows": 5,
"r_total_time_ms": 0.0121,
"filtered": 100,
"r_filtered": 100
},
"block-nl-join": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"r_loops": 1,
"rows": 5,
"r_rows": 5,
"r_total_time_ms": 0.0021,
"filtered": 100,
"r_filtered": 100
},
"buffer_type": "flat",
"buffer_size": "256Kb",
"join_type": "BNL",
"attached_condition": "t2.b = t1.b",
"r_filtered": 20
},
"block-nl-join": {
"table": {
"table_name": "t3",
"access_type": "ALL",
"r_loops": 1,
"rows": 21,
"r_rows": 100,
"r_total_time_ms": 0.0198,
"filtered": 100,
"r_filtered": 100
},
"buffer_type": "incremental",
"buffer_size": "256Kb",
"join_type": "BNL",
"attached_condition": "<in_optimizer>(1,exists(subquery#2))",
"r_filtered": 0
},
"subqueries": [
{
"expression_cache": {
"r_loops": 500,
"r_hit_ratio": 99,
"query_block": {
"select_id": 2,
"r_loops": 5,
"r_total_time_ms": 0.1044,
"having_condition": "t3.b = max(t4.a)",
"filesort": {
"sort_key": "t4.c",
"r_loops": 5,
"r_total_time_ms": 0.0039,
"r_used_priority_queue": false,
"r_output_rows": 0,
"r_buffer_size": "(varied across executions)",
"temporary_table": {
"table": {
"table_name": "t4",
"access_type": "ALL",
"r_loops": 5,
"rows": 1,
"r_rows": 100,
"r_total_time_ms": 0.0799,
"filtered": 100,
"r_filtered": 1,
"attached_condition": "t4.b = t1.b"
}
}
}
}
}
}
]
}
}
}
}

10.4.27
ANALYZE: {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 9.4341,
"filesort": {
"sort_key": "t2.a desc, t1.a desc",
"r_loops": 1,
"r_total_time_ms": 0.0038,
"r_used_priority_queue": false,
"r_output_rows": 5,
"r_buffer_size": "390",
"temporary_table": {
"table": {
"table_name": "t1",
"access_type": "ALL",
"r_loops": 1,
"rows": 5,
"r_rows": 5,
"r_total_time_ms": 0.0128,
"filtered": 100,
"r_filtered": 100
},
"block-nl-join": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"r_loops": 1,
"rows": 5,
"r_rows": 5,
"r_total_time_ms": 0.002,
"filtered": 100,
"r_filtered": 100
},
"buffer_type": "flat",
"buffer_size": "119",
"join_type": "BNL",
"attached_condition": "t2.b = t1.b",
"r_filtered": 20
},
"block-nl-join": {
"table": {
"table_name": "t3",
"access_type": "ALL",
"r_loops": 1,
"rows": 21,
"r_rows": 100,
"r_total_time_ms": 0.0213,
"filtered": 100,
"r_filtered": 100
},
"buffer_type": "incremental",
"buffer_size": "388",
"join_type": "BNL",
"attached_condition": "<in_optimizer>(1,exists(subquery#2))",
"r_filtered": 1
},
"subqueries": [
{
"expression_cache": {
"state": "disabled",
"r_loops": 200,
"r_hit_ratio": 0,
"query_block": {
"select_id": 2,
"r_loops": 500,
"r_total_time_ms": 9.1701,
"having_condition": "t3.b = max(t4.a)",
"temporary_table": {
"table": {
"table_name": "t4",
"access_type": "ALL",
"r_loops": 500,
"rows": 1,
"r_rows": 100,
"r_total_time_ms": 7.9882,
"filtered": 100,
"r_filtered": 1,
"attached_condition": "t4.b = t1.b"
}
}
}
}
}
]
}
}
}
}

10.3 180b2bcd5389082e200
MariaDB [test]> create table t1 (id int);
Query OK, 0 rows affected (0,048 sec)

MariaDB [test]> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0,010 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [test]> SELECT t1.id FROM t1,t1 a WHERE EXISTS (select 1 from t1 having t1.id=1) ORDER BY t1.id;
Empty set (0,011 sec)

MariaDB [test]> SELECT t1.id FROM t1,t1 a WHERE EXISTS (select 1 from t1 having t1.id=1);
+------+
\| id \|
+------+
\| 1 \|
\| 1 \|
\| 1 \|
+------+
3 rows in set (0,004 sec)

[MDEV-20368] Wrong Results with Dependent Subquery Created: 2019-08-17 Updated: 2022-12-07
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Data Manipulation - Subquery, Optimizer
Affects Version/s:	5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s:	10.3

[MDEV-20368] Wrong Results with Dependent Subquery Created: 2019-08-17 Updated: 2022-12-07