Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1
-
None
Description
- There was a change of behavior in 10.1 for type casting, but I can't seem to find any mention of it in the upgrade page: https://mariadb.com/kb/en/mariadb/upgrading-from-mariadb-100-to-101/
Simplified test case:
CREATE TABLE a (
|
col1 INT UNSIGNED PRIMARY KEY,
|
col2 varchar(20) DEFAULT NULL,
|
col3 int(11) DEFAULT NULL
|
);
|
CREATE TABLE b (
|
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
|
col1 varchar(15) NOT NULL,
|
col2 varchar(20) DEFAULT NULL,
|
Col3 int(11) DEFAULT NULL
|
);
|
INSERT INTO a (col1,col2,col3) VALUES (1,NULL,1),(2,'',0),(3,'816.00',1);
|
INSERT INTO b (col1, col2, col3) SELECT col1, col2, col3 FROM a WHERE IF(ROUND(col2) > 0, col3 < ROUND(col2), col3 <= 5);
|
Behavior in 10.0:
MariaDB [test]> SELECT VERSION();
|
+-----------------+
|
| VERSION() |
|
+-----------------+
|
| 10.0.30-MariaDB |
|
+-----------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> CREATE TABLE a (col1 INT UNSIGNED PRIMARY KEY, col2 varchar(20) DEFAULT NULL, col3 int(11) DEFAULT NULL);
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [test]>
|
MariaDB [test]> CREATE TABLE b (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, col1 varchar(15) NOT NULL, col2 varchar(20) DEFAULT NULL, col3 int(11) DEFAULT NULL);
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [test]>
|
MariaDB [test]> INSERT INTO a (col1,col2,col3) VALUES (1,NULL,1),(2,'',0),(3,'816.00',1);
|
Query OK, 3 rows affected (0.00 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]>
|
MariaDB [test]> INSERT INTO b (col1, col2, col3) SELECT col1, col2, col3 FROM a WHERE IF(ROUND(col2) > 0, col3 < ROUND(col2), col3 <= 5);
|
Query OK, 3 rows affected (0.00 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]>
|
Behavior in 10.1:
MariaDB [test]> SELECT VERSION();
|
+-----------------+
|
| VERSION() |
|
+-----------------+
|
| 10.1.21-MariaDB |
|
+-----------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> CREATE TABLE a (col1 INT UNSIGNED PRIMARY KEY, col2 varchar(20) DEFAULT NULL, col3 int(11) DEFAULT NULL);
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [test]>
|
MariaDB [test]> CREATE TABLE b (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, col1 varchar(15) NOT NULL, col2 varchar(20) DEFAULT NULL, col3 int(11) DEFAULT NULL);
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [test]>
|
MariaDB [test]> INSERT INTO a (col1,col2,col3) VALUES (1,NULL,1),(2,'',0),(3,'816.00',1);
|
Query OK, 3 rows affected (0.00 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]>
|
MariaDB [test]> INSERT INTO b (col1, col2, col3) SELECT col1, col2, col3 FROM a WHERE IF(ROUND(col2) > 0, col3 < ROUND(col2), col3 <= 5);
|
Query OK, 3 rows affected, 1 warning (0.01 sec)
|
Records: 3 Duplicates: 0 Warnings: 1
|
|
|
MariaDB [test]> SHOW WARNINGS;
|
+---------+------+--------------------------------------+
|
| Level | Code | Message |
|
+---------+------+--------------------------------------+
|
| Warning | 1292 | Truncated incorrect DOUBLE value: '' |
|
+---------+------+--------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]>
|
There are many people that are sensitive to changes of behavior, even small ones like this is a concern. So we should document it in the major release upgrade page.