Description:
MySQL returns incorrect results when using VALUES statement as a dependent subquery. This is because any conditions pushed to the subquery as part of query transformation is being ignored.
The issue is that JOIN::create_root_access_path_for_join is not generating any FilterIterator operators based any WHERE conditions, if query_block->is_table_value_constructor is true. HAVING/LIMIT clause is handled by JOIN::attach_access_paths_for_having_and_limit, but WHERE conditions are not handled.
An easy way to figure out which query patterns hit this issue is to add this assert and run through the test suite to see which queries are being executed incorrectly.
diff --git a/sql/sql_executor.cc b/sql/sql_executor.cc
index 2497b38b834..7a2df43213d 100644
--- a/sql/sql_executor.cc
+++ b/sql/sql_executor.cc
@@ -2884,6 +2884,7 @@ AccessPath *JOIN::create_root_access_path_for_join() {
// OK, so we're good. Go through the tables and make the join access paths.
AccessPath *path = nullptr;
if (query_block->is_table_value_constructor) {
+ assert(!query_block->where_cond());
best_rowcount = query_block->row_value_list->size();
path = NewTableValueConstructorAccessPath(thd);
path->num_output_rows = query_block->row_value_list->size();
How to repeat:
mysql> create table t (i int primary key);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t values (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t where i in (VALUES ROW(1), ROW(2));
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)
mysql> select * from t where i in (SELECT 1 UNION SELECT 2);
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
Suggested fix:
JOIN::create_root_access_path_for_join should check for where conditions on query_block and create filter operators.
In my specific example, we should just do semijoin transformation here which avoids this issue, but that's a separate feature request...
Description: MySQL returns incorrect results when using VALUES statement as a dependent subquery. This is because any conditions pushed to the subquery as part of query transformation is being ignored. The issue is that JOIN::create_root_access_path_for_join is not generating any FilterIterator operators based any WHERE conditions, if query_block->is_table_value_constructor is true. HAVING/LIMIT clause is handled by JOIN::attach_access_paths_for_having_and_limit, but WHERE conditions are not handled. An easy way to figure out which query patterns hit this issue is to add this assert and run through the test suite to see which queries are being executed incorrectly. diff --git a/sql/sql_executor.cc b/sql/sql_executor.cc index 2497b38b834..7a2df43213d 100644 --- a/sql/sql_executor.cc +++ b/sql/sql_executor.cc @@ -2884,6 +2884,7 @@ AccessPath *JOIN::create_root_access_path_for_join() { // OK, so we're good. Go through the tables and make the join access paths. AccessPath *path = nullptr; if (query_block->is_table_value_constructor) { + assert(!query_block->where_cond()); best_rowcount = query_block->row_value_list->size(); path = NewTableValueConstructorAccessPath(thd); path->num_output_rows = query_block->row_value_list->size(); How to repeat: mysql> create table t (i int primary key); Query OK, 0 rows affected (0.04 sec) mysql> insert into t values (1), (2), (3); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t where i in (VALUES ROW(1), ROW(2)); +---+ | i | +---+ | 1 | | 2 | | 3 | +---+ 3 rows in set (0.00 sec) mysql> select * from t where i in (SELECT 1 UNION SELECT 2); +---+ | i | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec) Suggested fix: JOIN::create_root_access_path_for_join should check for where conditions on query_block and create filter operators. In my specific example, we should just do semijoin transformation here which avoids this issue, but that's a separate feature request...