Description:
mysql> use test;
Database changed
mysql> set @@collation_connection ='utf8_bin';
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t5;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t5(c1 varchar(10) CHARACTER SET utf8 DEFAULT NULL,c2 int(11) GENERATED ALWAYS AS ((`c1` > 'a')) VIRTUAL,c3 varchar(10) COLLATE utf8_bin DEFAULT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t5(c1,c3) values('C', 'C');
Query OK, 1 row affected (0.00 sec)
mysql> alter table t5 add column c4 int as ('a'='A');
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set @@collation_connection ='utf8_general_ci';
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t5 add column c5 int as ('a'='A');
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set @@collation_connection ='utf8_bin';
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t5 add column c6 int as ('a'='A');
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index idx_t5 on t5(c4);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set @@collation_connection ='utf8_general_ci';
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t5 add column c7 int as ('a'='A');
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t5;
+------+------+------+------+------+------+------+
| c1 | c2 | c3 | c4 /*notice here, please */ | c5 | c6 | c7 |
+------+------+------+------+------+------+------+
| C | 1 | C | 1 | 1 | 1 | 1 |
+------+------+------+------+------+------+------+
1 row in set (0.00 sec)
mysql> select c4 from t5;
+------+
| c4 |
+------+
| 0 | /*notice here, please */
+------+
1 row in set (0.00 sec)
With the help of explain, you will know that index is used when it is performed:
mysql> explain select c4 from t5;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t5 | NULL | index | NULL | idx_t5 | 5 | NULL | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
So, 'select c4 from t5;' and 'select * from t5' return inconsistent results in terms of column c4.
How to repeat:
set @@collation_connection ='utf8_bin';
drop table if exists t5;
create table t5(c1 varchar(10) CHARACTER SET utf8 DEFAULT NULL,c2 int(11) GENERATED ALWAYS AS ((`c1` > 'a')) VIRTUAL,c3 varchar(10) COLLATE utf8_bin DEFAULT NULL);
insert into t5(c1,c3) values('C', 'C');
alter table t5 add column c4 int as ('a'='A');
set @@collation_connection ='utf8_general_ci';
alter table t5 add column c5 int as ('a'='A');
set @@collation_connection ='utf8_bin';
alter table t5 add column c6 int as ('a'='A');
create index idx_t5 on t5(c4);
set @@collation_connection ='utf8_general_ci';
alter table t5 add column c7 int as ('a'='A');
select * from t5;
select c4 from t5;
Suggested fix:
We must take the collation_connection into consideration,
so both of the results in c4 should be 1.
Description: mysql> use test; Database changed mysql> set @@collation_connection ='utf8_bin'; Query OK, 0 rows affected (0.00 sec) mysql> drop table if exists t5; Query OK, 0 rows affected (0.01 sec) mysql> create table t5(c1 varchar(10) CHARACTER SET utf8 DEFAULT NULL,c2 int(11) GENERATED ALWAYS AS ((`c1` > 'a')) VIRTUAL,c3 varchar(10) COLLATE utf8_bin DEFAULT NULL); Query OK, 0 rows affected (0.01 sec) mysql> insert into t5(c1,c3) values('C', 'C'); Query OK, 1 row affected (0.00 sec) mysql> alter table t5 add column c4 int as ('a'='A'); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> set @@collation_connection ='utf8_general_ci'; Query OK, 0 rows affected (0.00 sec) mysql> alter table t5 add column c5 int as ('a'='A'); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> set @@collation_connection ='utf8_bin'; Query OK, 0 rows affected (0.00 sec) mysql> alter table t5 add column c6 int as ('a'='A'); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index idx_t5 on t5(c4); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> set @@collation_connection ='utf8_general_ci'; Query OK, 0 rows affected (0.00 sec) mysql> alter table t5 add column c7 int as ('a'='A'); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t5; +------+------+------+------+------+------+------+ | c1 | c2 | c3 | c4 /*notice here, please */ | c5 | c6 | c7 | +------+------+------+------+------+------+------+ | C | 1 | C | 1 | 1 | 1 | 1 | +------+------+------+------+------+------+------+ 1 row in set (0.00 sec) mysql> select c4 from t5; +------+ | c4 | +------+ | 0 | /*notice here, please */ +------+ 1 row in set (0.00 sec) With the help of explain, you will know that index is used when it is performed: mysql> explain select c4 from t5; +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t5 | NULL | index | NULL | idx_t5 | 5 | NULL | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+ So, 'select c4 from t5;' and 'select * from t5' return inconsistent results in terms of column c4. How to repeat: set @@collation_connection ='utf8_bin'; drop table if exists t5; create table t5(c1 varchar(10) CHARACTER SET utf8 DEFAULT NULL,c2 int(11) GENERATED ALWAYS AS ((`c1` > 'a')) VIRTUAL,c3 varchar(10) COLLATE utf8_bin DEFAULT NULL); insert into t5(c1,c3) values('C', 'C'); alter table t5 add column c4 int as ('a'='A'); set @@collation_connection ='utf8_general_ci'; alter table t5 add column c5 int as ('a'='A'); set @@collation_connection ='utf8_bin'; alter table t5 add column c6 int as ('a'='A'); create index idx_t5 on t5(c4); set @@collation_connection ='utf8_general_ci'; alter table t5 add column c7 int as ('a'='A'); select * from t5; select c4 from t5; Suggested fix: We must take the collation_connection into consideration, so both of the results in c4 should be 1.