| Bug #87915 | Constant in multi-byte character fails to propagate to dependent subquery | ||
|---|---|---|---|
| Submitted: | 29 Sep 2017 13:47 | Modified: | 27 Apr 2018 14:20 |
| Reporter: | James G | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7.18/trunk | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 Sep 2017 18:23]
MySQL Verification Team
Could you please try version 5.7.19. THanks.
miguel@tikal:~ $ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE x
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE tableA (
-> id varchar(36) NOT NULL,
-> col2 int(10) unsigned zerofill NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET utf8
-> ;
Query OK, 0 rows affected (0.41 sec)
mysql> CREATE TABLE tableB (
-> id varchar(36) NOT NULL,
-> col2 int(10) unsigned zerofill NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.31 sec)
mysql> CREATE TABLE tableC (
-> refA varchar(36) NOT NULL,
-> refB varchar(36) NOT NULL,
-> PRIMARY KEY (refA, refB),
-> CONSTRAINT fkCA FOREIGN KEY (refA) REFERENCES tableA (id),
-> CONSTRAINT fkCB FOREIGN KEY (refB) REFERENCES tableB (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.35 sec)
mysql> CREATE TABLE tableD (
-> refA varchar(36) NOT NULL,
-> refB varchar(36) NOT NULL,
-> col3 int(10) unsigned zerofill NOT NULL,
-> PRIMARY KEY (refA, refB),
-> CONSTRAINT fkDA FOREIGN KEY (refA) REFERENCES tableA (id),
-> CONSTRAINT fkDB FOREIGN KEY (refB) REFERENCES tableB (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.34 sec)
mysql> insert into tableA values ('123',5)
-> ;
Query OK, 1 row affected (0.04 sec)
mysql> insert into tableB values ('234',6)
-> ;
Query OK, 1 row affected (0.04 sec)
mysql> insert into tableC values ('123','234')
-> ;
Query OK, 1 row affected (0.04 sec)
mysql> insert into tableD values ('123','234',7)
-> ;
Query OK, 1 row affected (0.04 sec)
mysql> select a.id as 'aid',
-> b.id as 'bid',
-> ( select d.col3 from tableD d where d.refA=a.id and d.refB=b.id ) as 'shouldBe7'
-> from tableA a
-> inner join tableC c on c.refA = a.id
-> inner join tableB b on c.refB = b.id
-> where a.id='123'
-> group by a.id, b.id
-> ;
+-----+-----+-----------+
| aid | bid | shouldBe7 |
+-----+-----+-----------+
| 123 | 234 | NULL |
+-----+-----+-----------+
1 row in set (0.03 sec)
mysql> explain select a.id as 'aid', b.id as 'bid', ( select d.col3 from tableD d where d.refA=a.id and d.refB=b.id ) as 'shouldBe7' from tableA a inner join tableC c on c.refA = a.id inner join tableB b on c.refB = b.id where a.id='123' group by a.id, b.id;
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+----------------------------------------------+
| 1 | PRIMARY | a | NULL | const | PRIMARY | PRIMARY | 110 | const | 1 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | PRIMARY | c | NULL | ref | PRIMARY,fkCB | PRIMARY | 110 | const | 1 | 100.00 | Using where; Using index |
| 1 | PRIMARY | b | NULL | eq_ref | PRIMARY | PRIMARY | 110 | x.c.refB | 1 | 100.00 | Using index |
| 2 | DEPENDENT SUBQUERY | d | NULL | eq_ref | PRIMARY,fkDB | PRIMARY | 220 | const,func | 1 | 100.00 | NULL |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+----------------------------------------------+
4 rows in set, 3 warnings (0.00 sec)
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'x.a.id' of SELECT #2 was resolved in SELECT #1 |
| Note | 1276 | Field or reference 'x.b.id' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | /* select#1 */ select '123' AS `aid`,`x`.`b`.`id` AS `bid`,(/* select#2 */ select `x`.`d`.`col3` from `x`.`tableD` `d` where ((`x`.`d`.`refA` = '') and (`x`.`d`.`refB` = `x`.`b`.`id`))) AS `shouldBe7` from `x`.`tableA` `a` join `x`.`tableC` `c` join `x`.`tableB` `b` where ((`x`.`c`.`refA` = '123') and (`x`.`b`.`id` = `x`.`c`.`refB`) and ('123' = '123')) group by '123',`x`.`b`.`id` |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
[29 Sep 2017 18:49]
James G
Looks like you reproduced the bug in 5.7.19. +-----+-----+-----------+ | aid | bid | shouldBe7 | +-----+-----+-----------+ | 123 | 234 | NULL | +-----+-----+-----------+ should have been: +-----+-----+-----------+ | aid | bid | shouldBe7 | +-----+-----+-----------+ | 123 | 234 | 7 | +-----+-----+-----------+
[29 Sep 2017 21:53]
MySQL Verification Team
Indeed you are right, thanks for the feedback.
miguel@tikal:~/dbs $ ./57c
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21 Source distribution 2017-SEP-15
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.7 > use u
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql 5.7 > select a.id as 'aid',
-> b.id as 'bid',
-> ( select d.col3 from tableD d where d.refA=a.id and d.refB=b.id ) as 'shouldBe7'
-> from tableA a
-> inner join tableC c on c.refA = a.id
-> inner join tableB b on c.refB = b.id
-> where a.id='123'
-> group by a.id, b.id
-> ;
+-----+-----+-----------+
| aid | bid | shouldBe7 |
+-----+-----+-----------+
| 123 | 234 | NULL |
+-----+-----+-----------+
1 row in set (0.00 sec)
mysql 5.7 >
-----------------------------------------------------------------------
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.39 Source distribution 2017-SEP-15
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.6 > use u
Database changed
mysql 5.6 > CREATE TABLE tableA (
-> id varchar(36) NOT NULL,
-> col2 int(10) unsigned zerofill NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET utf8
-> ;
Query OK, 0 rows affected (0.35 sec)
mysql 5.6 > CREATE TABLE tableB (
-> id varchar(36) NOT NULL,
-> col2 int(10) unsigned zerofill NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.30 sec)
mysql 5.6 > CREATE TABLE tableC (
-> refA varchar(36) NOT NULL,
-> refB varchar(36) NOT NULL,
-> PRIMARY KEY (refA, refB),
-> CONSTRAINT fkCA FOREIGN KEY (refA) REFERENCES tableA (id),
-> CONSTRAINT fkCB FOREIGN KEY (refB) REFERENCES tableB (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.34 sec)
mysql 5.6 > CREATE TABLE tableD (
-> refA varchar(36) NOT NULL,
-> refB varchar(36) NOT NULL,
-> col3 int(10) unsigned zerofill NOT NULL,
-> PRIMARY KEY (refA, refB),
-> CONSTRAINT fkDA FOREIGN KEY (refA) REFERENCES tableA (id),
-> CONSTRAINT fkDB FOREIGN KEY (refB) REFERENCES tableB (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.39 sec)
mysql 5.6 > insert into tableA values ('123',5)
-> ;
Query OK, 1 row affected (0.05 sec)
mysql 5.6 > insert into tableB values ('234',6)
-> ;
Query OK, 1 row affected (0.04 sec)
mysql 5.6 > insert into tableC values ('123','234')
-> ;
Query OK, 1 row affected (0.04 sec)
mysql 5.6 > insert into tableD values ('123','234',7)
-> ;
Query OK, 1 row affected (0.04 sec)
mysql 5.6 > select a.id as 'aid',
-> b.id as 'bid',
-> ( select d.col3 from tableD d where d.refA=a.id and d.refB=b.id ) as 'shouldBe7'
-> from tableA a
-> inner join tableC c on c.refA = a.id
-> inner join tableB b on c.refB = b.id
-> where a.id='123'
-> group by a.id, b.id
-> ;
+-----+-----+-----------+
| aid | bid | shouldBe7 |
+-----+-----+-----------+
| 123 | 234 | 7 |
+-----+-----+-----------+
1 row in set (0.00 sec)
mysql 5.6 >
[27 Apr 2018 14:20]
James G
This bug still occurs in MySQL 8.0 and will be more relevant now that the default character set is changing to utf8mb4. See https://www.db-fiddle.com/f/sYEpEP2FwNZs2VT7yHDXyx/2 In the result "shouldBe7" is null when run on MySQL 8.0 or 5.7. It is 7 for MySQL 5.6 and below.
Description: We have report queries that use dependent subqueries in the select statement. As of 5.7 they return null even if a result exists. I've noticed this happens when the charset is utf8 or utf8mb4 and not ascii. I've included the schema and query to reproduce it. If you do an explain on the select statement and then "show warnings" you can see the dependent subquery is comparing against the constant '' rather than '123' which would be correct. How to repeat: CREATE TABLE tableA ( id varchar(36) NOT NULL, col2 int(10) unsigned zerofill NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET utf8 ; CREATE TABLE tableB ( id varchar(36) NOT NULL, col2 int(10) unsigned zerofill NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET utf8; CREATE TABLE tableC ( refA varchar(36) NOT NULL, refB varchar(36) NOT NULL, PRIMARY KEY (refA, refB), CONSTRAINT fkCA FOREIGN KEY (refA) REFERENCES tableA (id), CONSTRAINT fkCB FOREIGN KEY (refB) REFERENCES tableB (id) ) ENGINE=InnoDB DEFAULT CHARSET utf8; CREATE TABLE tableD ( refA varchar(36) NOT NULL, refB varchar(36) NOT NULL, col3 int(10) unsigned zerofill NOT NULL, PRIMARY KEY (refA, refB), CONSTRAINT fkDA FOREIGN KEY (refA) REFERENCES tableA (id), CONSTRAINT fkDB FOREIGN KEY (refB) REFERENCES tableB (id) ) ENGINE=InnoDB DEFAULT CHARSET utf8; insert into tableA values ('123',5) ; insert into tableB values ('234',6) ; insert into tableC values ('123','234') ; insert into tableD values ('123','234',7) ; select a.id as 'aid', b.id as 'bid', ( select d.col3 from tableD d where d.refA=a.id and d.refB=b.id ) as 'shouldBe7' from tableA a inner join tableC c on c.refA = a.id inner join tableB b on c.refB = b.id where a.id='123' group by a.id, b.id ;