Bug #83627 inconsistent results with generated column when meets char and padding

• Submitted: 1 Nov 2016 8:16
• Modified: 1 Nov 2016 9:28
• Reporter: 帅 Bang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7/8.0
• OS: Linux
• CPU Architecture: Any

[1 Nov 2016 8:16] 帅 Bang

Description:
mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_mode=default;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t(pk int primary key,a char(5),b varchar(5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t(pk,a,b) values(1,'a','1');
Query OK, 1 row affected (0.00 sec)

mysql> alter table t add column c varchar(10) as(concat(a,b)) stored;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> set sql_mode=PAD_CHAR_TO_FULL_LENGTH;
Query OK, 0 rows affected (0.00 sec)

mysql> create index idx on t(c);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t where concat(a,b)='a1';
+----+-------+------+------+
| pk | a     | b    | c    |
+----+-------+------+------+
|  1 | a     | 1    | a1   |
+----+-------+------+------+
1 row in set (0.00 sec)

mysql> select * from t ignore index(idx) where concat(a,b)='a1';
Empty set (0.00 sec)

yeah, it is inconsistent.

How to repeat:
drop table t;
set sql_mode=default;
create table t(pk int primary key,a char(5),b varchar(5));
insert into t(pk,a,b) values(1,'a','1');
alter table t add column c varchar(10) as(concat(a,b)) stored;
set sql_mode=PAD_CHAR_TO_FULL_LENGTH;
create index idx on t(c);
select * from t where concat(a,b)='a1';
select * from t ignore index(idx) where concat(a,b)='a1';

Suggested fix:
both return 1 record

or

both return empty set

[1 Nov 2016 9:28] MySQL Verification Team

Thank you for the bug report.

c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.1-dmr Source distribution PULL: 2016-OCT-14

Copyright (c) 2000, 2016, 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 8.0 > use test
Database changed
mysql 8.0 > drop table t;
Query OK, 0 rows affected (0.22 sec)

mysql 8.0 > set sql_mode=default;
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > create table t(pk int primary key,a char(5),b varchar(5));
Query OK, 0 rows affected (0.25 sec)

mysql 8.0 > insert into t(pk,a,b) values(1,'a','1');
Query OK, 1 row affected (0.10 sec)

mysql 8.0 > alter table t add column c varchar(10) as(concat(a,b)) stored;
Query OK, 1 row affected (0.69 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 8.0 > set sql_mode=PAD_CHAR_TO_FULL_LENGTH;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql 8.0 > create index idx on t(c);
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 8.0 > select * from t where concat(a,b)='a1';
+----+-------+------+------+
| pk | a     | b    | c    |
+----+-------+------+------+
|  1 | a     | 1    | a1   |
+----+-------+------+------+
1 row in set (0.01 sec)

mysql 8.0 > select * from t ignore index(idx) where concat(a,b)='a1';
Empty set (0.00 sec)

mysql 8.0 >