Description:
Without subquery_to_derived:
mysql> SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1);
Empty set, 2 warnings (0.00 sec)
With subquery_to_derived:
mysql> SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1);
+---------+------+
| BIN(c1) | c3 |
+---------+------+
| 1010 | 1 |
| 1111 | 3 |
+---------+------+
2 rows in set, 7 warnings (0.00 sec)
mysql> EXPLAIN SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1);
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | PRIMARY | type1 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (hash join) |
| 2 | DERIVED | type2 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using temporary |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)
Without subquery_to_derived, use Arg_comparator::compare_json to for Item_func_eq::val_int.
The m_compare_type is REAL_RESULT. After unnesting the subquery, HASH JOIN use extract_value_for_hash_join to get the value to REAL_RESULT.
It looks conflict from the m_compare_type(REAL_RESULT) and the comparator func (Arg_comparator::compare_json)
On 8.0.13 without hash join, the unnesting subquery result is also empty.
mysql> /* select#1 */ select conv(`test`.`type1`.`c1`,10,2) AS `BIN(c1)`,`test`.`type1`.`c3` AS `c3` from `test`.`type1` join (/* select#2 */ select variance(`test`.`type2`.`c2`) AS `var_pop(c2)`,`test`.`type2`.`c1` AS `c1` from `test`.`type2` group by `test`.`type2`.`c1`) `derived_1_2` where ((`derived_1_2`.`c1` = `test`.`type1`.`c1`) and (`test`.`type1`.`c2` = `derived_1_2`.`var_pop(c2)`));
Empty set, 4 warnings (0.01 sec)
mysql> explain /* select#1 */ select conv(`test`.`type1`.`c1`,10,2) AS `BIN(c1)`,`test`.`type1`.`c3` AS `c3` from `test`.`type1` join (/* select#2 */ select variance(`test`.`type2`.`c2`) AS `var_pop(c2)`,`test`.`type2`.`c1` AS `c1` from `test`.`type2` group by `test`.`type2`.`c1`) `derived_1_2` where ((`derived_1_2`.`c1` = `test`.`type1`.`c1`) and (`test`.`type1`.`c2` = `derived_1_2`.`var_pop(c2)`));
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | PRIMARY | type1 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where; Using join buffer (Block Nested Loop) |
| 2 | DERIVED | type2 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
How to repeat:
```
CREATE TABLE type1(c1 bit(8), c2 json, c3 int, c4 json);
INSERT INTO type1 VALUES(b'1010', '{"key1": "value1", "key2": "value2"}', 1, '["key1"]');
INSERT INTO type1 VALUES(b'1011', '{"key1": "value1", "key2": "value2"}', 2, '["key2", "key22"]');
INSERT INTO type1 VALUES(b'1111', '{"key3": "value3", "key3": "value3"}', 3, '{"key3": 3}');
INSERT INTO type1 VALUES(null, null, null, '["key4"]');
CREATE TABLE type2(c1 bit(8), c2 json, c3 int, c4 varchar(20));
INSERT INTO type2 VALUES(b'1010', '{"key1": "value1", "key2": "value2"}', 1, "key1");
INSERT INTO type2 VALUES(b'1100', '{"key2": "value2", "key2": "value2"}', 2, "key2");
INSERT INTO type2 VALUES(b'1100', '{"key2": "value2", "key2": "value2"}', 2, "key22");
INSERT INTO type2 VALUES(b'1111', '{"key3": "value3", "key3": "value3"}', 3, "key3");
INSERT INTO type2 VALUES(null, null, null, "key4");
SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1);
set @@optimizer_switch='subquery_to_derived=on';
SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1);
```
Without subquery_to_derived:
mysql> SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1);
Empty set, 2 warnings (0.00 sec)
With subquery_to_derived:
mysql> SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1);
+---------+------+
| BIN(c1) | c3 |
+---------+------+
| 1010 | 1 |
| 1111 | 3 |
+---------+------+
2 rows in set, 7 warnings (0.00 sec)
Description: Without subquery_to_derived: mysql> SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1); Empty set, 2 warnings (0.00 sec) With subquery_to_derived: mysql> SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1); +---------+------+ | BIN(c1) | c3 | +---------+------+ | 1010 | 1 | | 1111 | 3 | +---------+------+ 2 rows in set, 7 warnings (0.00 sec) mysql> EXPLAIN SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1); +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | PRIMARY | type1 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL | | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (hash join) | | 2 | DERIVED | type2 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using temporary | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ 3 rows in set, 2 warnings (0.00 sec) Without subquery_to_derived, use Arg_comparator::compare_json to for Item_func_eq::val_int. The m_compare_type is REAL_RESULT. After unnesting the subquery, HASH JOIN use extract_value_for_hash_join to get the value to REAL_RESULT. It looks conflict from the m_compare_type(REAL_RESULT) and the comparator func (Arg_comparator::compare_json) On 8.0.13 without hash join, the unnesting subquery result is also empty. mysql> /* select#1 */ select conv(`test`.`type1`.`c1`,10,2) AS `BIN(c1)`,`test`.`type1`.`c3` AS `c3` from `test`.`type1` join (/* select#2 */ select variance(`test`.`type2`.`c2`) AS `var_pop(c2)`,`test`.`type2`.`c1` AS `c1` from `test`.`type2` group by `test`.`type2`.`c1`) `derived_1_2` where ((`derived_1_2`.`c1` = `test`.`type1`.`c1`) and (`test`.`type1`.`c2` = `derived_1_2`.`var_pop(c2)`)); Empty set, 4 warnings (0.01 sec) mysql> explain /* select#1 */ select conv(`test`.`type1`.`c1`,10,2) AS `BIN(c1)`,`test`.`type1`.`c3` AS `c3` from `test`.`type1` join (/* select#2 */ select variance(`test`.`type2`.`c2`) AS `var_pop(c2)`,`test`.`type2`.`c1` AS `c1` from `test`.`type2` group by `test`.`type2`.`c1`) `derived_1_2` where ((`derived_1_2`.`c1` = `test`.`type1`.`c1`) and (`test`.`type1`.`c2` = `derived_1_2`.`var_pop(c2)`)); +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | 1 | PRIMARY | type1 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL | | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where; Using join buffer (Block Nested Loop) | | 2 | DERIVED | type2 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ How to repeat: ``` CREATE TABLE type1(c1 bit(8), c2 json, c3 int, c4 json); INSERT INTO type1 VALUES(b'1010', '{"key1": "value1", "key2": "value2"}', 1, '["key1"]'); INSERT INTO type1 VALUES(b'1011', '{"key1": "value1", "key2": "value2"}', 2, '["key2", "key22"]'); INSERT INTO type1 VALUES(b'1111', '{"key3": "value3", "key3": "value3"}', 3, '{"key3": 3}'); INSERT INTO type1 VALUES(null, null, null, '["key4"]'); CREATE TABLE type2(c1 bit(8), c2 json, c3 int, c4 varchar(20)); INSERT INTO type2 VALUES(b'1010', '{"key1": "value1", "key2": "value2"}', 1, "key1"); INSERT INTO type2 VALUES(b'1100', '{"key2": "value2", "key2": "value2"}', 2, "key2"); INSERT INTO type2 VALUES(b'1100', '{"key2": "value2", "key2": "value2"}', 2, "key22"); INSERT INTO type2 VALUES(b'1111', '{"key3": "value3", "key3": "value3"}', 3, "key3"); INSERT INTO type2 VALUES(null, null, null, "key4"); SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1); set @@optimizer_switch='subquery_to_derived=on'; SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1); ``` Without subquery_to_derived: mysql> SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1); Empty set, 2 warnings (0.00 sec) With subquery_to_derived: mysql> SELECT BIN(c1), c3 FROM type1 where c2 = (select var_pop(c2) from type2 where type2.c1 = type1.c1); +---------+------+ | BIN(c1) | c3 | +---------+------+ | 1010 | 1 | | 1111 | 3 | +---------+------+ 2 rows in set, 7 warnings (0.00 sec)