Bug #89367 Storing result in a variable(UDV) causes query on a view to use derived tables

• Submitted: 24 Jan 2018 2:52
• Modified: 24 Jan 2018 5:50
• Reporter: Jaime Sicam
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7.20, 5.7.21
• OS: Any
• CPU Architecture: Any

[24 Jan 2018 2:52] Jaime Sicam

Description:
If you run a query without a user defined variable in a view, derived tables are not created:
explain SELECT count(*) FROM v1 WHERE a=0 AND d='A' ;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                       | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------------+
|  1 | SIMPLE      | m1    | NULL       | ref    | PRIMARY       | PRIMARY | 4       | const                     |    2 |    25.00 | Using where |
|  1 | SIMPLE      | m2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 12      | const,test.m1.b,test.m1.c |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

But when you store the output in a user defined variable, it will create derived tables:
explain SELECT @a:=count(*) FROM v1 WHERE a=0 AND d='A' ;
+----+-------------+------------+------------+--------+---------------+-------------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key         | key_len | ref                           | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+-------------+---------+-------------------------------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref    | <auto_key0>   | <auto_key0> | 27      | const,const                   |    1 |   100.00 | NULL        |
|  2 | DERIVED     | m1         | NULL       | ALL    | PRIMARY       | NULL        | NULL    | NULL                          |    4 |   100.00 | NULL        |
|  2 | DERIVED     | m2         | NULL       | eq_ref | PRIMARY       | PRIMARY     | 12      | test.m1.a,test.m1.b,test.m1.c |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+-------------+---------+-------------------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

If you try this on 5.5 and 5.6, derived tables are not generated. With larger tables you'll see how slow using derived tables are in 5.7.

How to repeat:
Test on 5.5, 5.6 and 5.7:

DROP VIEW IF EXISTS v1;
DROP TABLE IF EXISTS m1;
DROP TABLE IF EXISTS m2;

CREATE TABLE `m1` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`a`,`b`)
) ENGINE=InnoDB ;

 CREATE TABLE `m2` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`,`c`)
) ENGINE=InnoDB ;

INSERT INTO m1 VALUES (0,0,0,'A'),(0,1,0,'B'),(1,0,1,'A'),(1,1,1,'B');
INSERT INTO m2 VALUES (0,0,0),(0,0,1),(1,0,0),(1,0,1);

CREATE ALGORITHM=MERGE VIEW v1 AS SELECT m1.a, m1.b, m1.d FROM m1 JOIN m2 on m1.a=m2.a AND m1.b = m2.b AND m1.c = m2.c;

EXPLAIN SELECT count(*) FROM v1 WHERE a=0 AND d='A';
EXPLAIN SELECT @a:=count(*) FROM v1 WHERE a=0 AND d='A';

[24 Jan 2018 5:50] MySQL Verification Team

Hello Jaime,

Thank you for the report and test case.

Thanks,
Umesh

[24 Jan 2018 9:04] Roy Lyseng

In 5.7, we added a heuristic so that queries that assign user variables are by default materialized and not merged. However, we should have let the ALGORITHM=MERGE override this decision. This is a bug.

In 8.0, this behavior is rectified. ALGORITHM=MERGE will merge the view into the query. In addition, we have implemented query hints (/*+ MERGE(t) */ and /*+ NO_MERGE(t) */) that affects how derived tables and views are merged or materialized.

Currently there is a good workaround in 5.7: Rewrite using SELECT ... INTO as follows:

  EXPLAIN SELECT count(*) FROM v1 WHERE a=0 AND d='A' INTO @a;