| Bug #79540 | Generated Column Issue | ||
|---|---|---|---|
| Submitted: | 7 Dec 2015 6:53 | Modified: | 11 Jan 2016 1:57 |
| Reporter: | kim ssauravy | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.7.9 | OS: | CentOS (5.6) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | generated column | ||
[10 Dec 2015 10:53]
MySQL Verification Team
Hello kim kyoung youn, Thank you for the report and test case. Verified as described with 5.7.9. Thanks, Umesh
[10 Dec 2015 10:54]
MySQL Verification Team
test results
Attachment: 79540.results (application/octet-stream, text), 4.71 KiB.
[10 Dec 2015 11:28]
MySQL Verification Team
I'm not seeing this issue on 5.7.10, could you please confirm?
Post update statement, there is no matching "NM" and behavior seems to be correct to me. pls check and let us know if you are still seeing this issue
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.10 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.10/79400 --core-file --socket=/tmp/mysql_ushastry.sock --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.7.10/79400/log.err 2>&1 &
[1] 20792
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10:
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, 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> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS T_TEST;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE T_TEST(
-> IDX INT NOT NULL
-> ,NM VARCHAR(10) NOT NULL
-> ,V_SORT CHAR(1) AS (CASE
-> WHEN (SUBSTR(NM,1,1) REGEXP '^[ㄱ-힣]' )=1 THEN '1'
-> WHEN (SUBSTR(NM,1,1) REGEXP '^[A-Za-z]')=1 THEN '2'
-> WHEN (SUBSTR(NM,1,1) REGEXP '^[0-9]' )=1 THEN '3'
-> ELSE '4' END) VIRTUAL
-> )CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> ALTER TABLE T_TEST ADD PRIMARY KEY (NM);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE T_TEST ADD KEY (IDX, V_SORT);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO T_TEST (IDX, NM) VALUES (1,'대한민국');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO T_TEST (IDX, NM) VALUES (2,'ΛBCD');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO T_TEST (IDX, NM) VALUES (3,'ABBB');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO T_TEST (IDX, NM) VALUES (4,'123456');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM T_TEST;
+-----+--------------+--------+
| IDX | NM | V_SORT |
+-----+--------------+--------+
| 1 | 대한민국 | 1 |
| 2 | ΛBCD | 1 |
| 3 | ABBB | 2 |
| 4 | 123456 | 3 |
+-----+--------------+--------+
4 rows in set (0.00 sec)
mysql>
mysql> ALTER TABLE T_TEST
-> MODIFY V_SORT CHAR(1) AS (CASE
-> WHEN (SUBSTR(NM,1,1) REGEXP '^[ㄱ-힣]' )=1 AND LENGTH(SUBSTR(NM,1,1))=3 THEN '1'
-> WHEN (SUBSTR(NM,1,1) REGEXP '^[A-Za-z]')=1 THEN '2'
-> WHEN (SUBSTR(NM,1,1) REGEXP '^[0-9]' )=1 THEN '3'
-> ELSE '4' END) ;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> SELECT * FROM T_TEST;
+-----+--------------+--------+
| IDX | NM | V_SORT |
+-----+--------------+--------+
| 1 | 대한민국 | 1 |
| 2 | ΛBCD | 4 |
| 3 | ABBB | 2 |
| 4 | 123456 | 3 |
+-----+--------------+--------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM T_TEST WHERE NM='ΛBCD';
+-----+-------+--------+
| IDX | NM | V_SORT |
+-----+-------+--------+
| 2 | ΛBCD | 4 |
+-----+-------+--------+
1 row in set (0.00 sec)
mysql> SELECT NM, IDX, V_SORT FROM T_TEST USE INDEX(IDX) WHERE NM='ΛBCD';
+-------+-----+--------+
| NM | IDX | V_SORT |
+-------+-----+--------+
| ΛBCD | 2 | 4 |
+-------+-----+--------+
1 row in set (0.00 sec)
mysql>
mysql> UPDATE T_TEST
-> SET NM='가나다'
-> WHERE NM='ΛBCD';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> SELECT * FROM T_TEST;
+-----+--------------+--------+
| IDX | NM | V_SORT |
+-----+--------------+--------+
| 1 | 대한민국 | 1 |
| 2 | 가나다 | 1 |
| 3 | ABBB | 2 |
| 4 | 123456 | 3 |
+-----+--------------+--------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM T_TEST WHERE NM='ΛBCD';
Empty set (0.00 sec)
mysql> SELECT NM, IDX, V_SORT FROM T_TEST USE INDEX(IDX) WHERE NM='ΛBCD';
Empty set (0.00 sec)
[11 Jan 2016 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[11 Jan 2016 1:57]
kim ssauravy
thank you for your help.
Description: I found a bug related generated column in mysql 5.7.6. If you created an index on a virtual column, I proceed with the update, the results appear on the dummy row neuneun phenomenon. generated column looks at the phenomenon that is found when the virtual options granted. This return is the result of a broken integrity. How to repeat: SELECT VERSION(); /* VERSION() ----------- 5.7.9 */ DROP TABLE IF EXISTS T_TEST; CREATE TABLE T_TEST( IDX INT NOT NULL ,NM VARCHAR(10) NOT NULL ,V_SORT CHAR(1) AS (CASE WHEN (SUBSTR(NM,1,1) REGEXP '^[ㄱ-힣]' )=1 THEN '1' WHEN (SUBSTR(NM,1,1) REGEXP '^[A-Za-z]')=1 THEN '2' WHEN (SUBSTR(NM,1,1) REGEXP '^[0-9]' )=1 THEN '3' ELSE '4' END) VIRTUAL ); ALTER TABLE T_TEST ADD PRIMARY KEY (NM); ALTER TABLE T_TEST ADD KEY (IDX, V_SORT); INSERT INTO T_TEST (IDX, NM) VALUES (1,'대한민국'); INSERT INTO T_TEST (IDX, NM) VALUES (2,'ΛBCD'); INSERT INTO T_TEST (IDX, NM) VALUES (3,'ABBB'); INSERT INTO T_TEST (IDX, NM) VALUES (4,'123456'); SELECT * FROM T_TEST; /* IDX NM V_SORT ------ ------------ -------- 1 대한민국 1 2 ΛBCD 1 3 ABBB 2 4 123456 3 */ ALTER TABLE T_TEST MODIFY V_SORT CHAR(1) AS (CASE WHEN (SUBSTR(NM,1,1) REGEXP '^[ㄱ-힣]' )=1 AND LENGTH(SUBSTR(NM,1,1))=3 THEN '1' WHEN (SUBSTR(NM,1,1) REGEXP '^[A-Za-z]')=1 THEN '2' WHEN (SUBSTR(NM,1,1) REGEXP '^[0-9]' )=1 THEN '3' ELSE '4' END) ; /* 실행 시간 : 0.016 sec 전송 시간 : 0.002 sec 총 시간 : 0.018 sec */ SELECT * FROM T_TEST; /* IDX NM V_SORT ------ ------------ -------- 1 대한민국 1 2 ΛBCD 1 3 ABBB 2 4 123456 3 */ UPDATE T_TEST SET NM='가나다' WHERE NM='ΛBCD'; /* 쿼리: UPDATE T_TEST SET NM='가나다' WHERE NM='ΛBCD' 1 열에 적용됨 실행 시간 : 0.010 sec 전송 시간 : 0.002 sec 총 시간 : 0.012 sec */ SELECT * FROM T_TEST /* IDX NM V_SORT ------ ------------ -------- 1 대한민국 1 2 ΛBCD 1 2 가나다 1 3 ABBB 2 4 123456 3 */ SELECT * FROM T_TEST WHERE NM='ΛBCD'; /* IDX NM V_SORT ------ ------ -------- */ SELECT NM, IDX, V_SORT FROM T_TEST USE INDEX(IDX) WHERE NM='ΛBCD'; /* IDX V_SORT ------ -------- 2 1 */ WHAT'S HAPPEN? Suggested fix: Create an index to the virtual column of the virtual options, Arbitrarily phenomenon that occurs when you modify the column, Either change or modify the restriction even when the virtual option, if automatic change.