[MDEV-11787] Inconsistent behavior on bad query between MariaDB 10.0 and 10.1 Created: 2017-01-13  Updated: 2022-11-04  Resolved: 2022-11-04

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.0
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Chris Calender (Inactive) Assignee: Alexander Barkov
Resolution: Won't Fix Votes: 1
Labels: None


 Description   

The following bad UPDATE leads to a "warning" and truncated data on 10.0, but it fails on 10.1.

UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;

I say it is "bad" because in theory, there should be a "," in place of "AND". However, perhaps it is possible that this is valid SQL, due to "AND".

I'm not sure what change led to this, if it is expected, if 10.0 is correct in allowing this as SQL, or is 10.1 correct in blocking this?

Note that in 10.0, the query throws a "warning", but partially changes the row.

In 10.1, the query fails, and thus the partial update does not occur.

Also, in 10.1, the SHOW WARNINGS after the failed query results in 2 results (warning for "Truncated incorrect INTEGER" and error for "Truncated incorrect DOUBLE") as opposed to only the 1 row in 10.0 (warning for "Truncated incorrect INTEGER").

Please advise which is actually correct. And assuming 10.1 is correct behavior, please advise which change caused this. And could this be considered an incompatibility between 10.0 and 10.1 that needs documented?

Test Case:

CREATE TABLE `t1` (
 
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
`status` ENUM('a','b') NOT NULL DEFAULT 'a',
 
`txt` TEXT) engine=InnODB;
 
INSERT INTO `t1` (`txt`) VALUES ('hello world');
 
SELECT * FROM `t1`;
 
UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;
 
SHOW WARNINGS;
 
SELECT * FROM `t1`;

Output for 10.0:

mysql> SELECT @@SQL_MODE, @@VERSION;
 
+--------------------------------------------+-----------------+
 
| @@SQL_MODE                                 | @@VERSION       |
 
+--------------------------------------------+-----------------+
 
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | 10.0.29-MariaDB |
 
+--------------------------------------------+-----------------+
 
1 row in set (0.00 sec)


mysql> use test;
 
Database changed
 
mysql> CREATE TABLE `t1` (
 
    -> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
    -> `status` ENUM('a','b') NOT NULL DEFAULT 'a',
 
    -> `txt` TEXT) engine=InnODB;
 
Query OK, 0 rows affected (0.13 sec)


mysql> INSERT INTO `t1` (`txt`) VALUES ('hello world');
 
Query OK, 1 row affected (0.02 sec)


mysql> SELECT * FROM `t1`;
 
+----+--------+-------------+
 
| id | status | txt         |
 
+----+--------+-------------+
 
|  1 | a      | hello world |
 
+----+--------+-------------+
 
1 row in set (0.00 sec)


mysql> UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;
 
Query OK, 1 row affected, 1 warning (0.00 sec)
 
Rows matched: 1  Changed: 1  Warnings: 1


mysql> SHOW WARNINGS;
 
+---------+------+---------------------------------------+
 
| Level   | Code | Message                               |
 
+---------+------+---------------------------------------+
 
| Warning | 1292 | Truncated incorrect INTEGER value: '' |
 
+---------+------+---------------------------------------+
 
1 row in set (0.00 sec)


mysql> SELECT * FROM `t1`;
 
+----+--------+------+
 
| id | status | txt  |
 
+----+--------+------+
 
|  1 | a      | 0    |
 
+----+--------+------+
 
1 row in set (0.00 sec)

Output for 10.1:

mysql> SELECT @@SQL_MODE, @@VERSION;
 
+--------------------------------------------+-----------------+
 
| @@SQL_MODE                                 | @@VERSION       |
 
+--------------------------------------------+-----------------+
 
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | 10.1.20-MariaDB |
 
+--------------------------------------------+-----------------+
 
1 row in set (0.00 sec)


mysql> CREATE TABLE `t1` (
 
    -> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
    -> `status` ENUM('a','b') NOT NULL DEFAULT 'a',
 
    -> `txt` TEXT) engine=InnODB;
 
Query OK, 0 rows affected (0.05 sec)


mysql> INSERT INTO `t1` (`txt`) VALUES ('hello world');
 
Query OK, 1 row affected (0.02 sec)


mysql> SELECT * FROM `t1`;
 
+----+--------+-------------+
 
| id | status | txt         |
 
+----+--------+-------------+
 
|  1 | a      | hello world |
 
+----+--------+-------------+
 
1 row in set (0.00 sec)


mysql> UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;
 
ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''


mysql> SHOW WARNINGS;
 
+---------+------+---------------------------------------+
 
| Level   | Code | Message                               |
 
+---------+------+---------------------------------------+
 
| Warning | 1292 | Truncated incorrect INTEGER value: '' |
 
| Error   | 1292 | Truncated incorrect DOUBLE value: ''  |
 
+---------+------+---------------------------------------+
 
2 rows in set (0.00 sec)


mysql> SELECT * FROM `t1`;
 
+----+--------+-------------+
 
| id | status | txt         |
 
+----+--------+-------------+
 
|  1 | a      | hello world |
 
+----+--------+-------------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Chris Calender (Inactive) [ 2017-01-13 ]

Well, I decied to test 10.1.1, and I see it has the same behavior as 10.0.29, so this seems to have been a change in a later 10.1.

101.1 Output:

MariaDB [(none)]> select version();
 
+----------------+
 
| version()      |
 
+----------------+
 
| 10.1.1-MariaDB |
 
+----------------+
 
1 row in set (0.00 sec)


MariaDB [(none)]> use test;
 
Database changed
 
MariaDB [test]> CREATE TABLE `t1` (
 
    -> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
    -> `status` ENUM('a','b') NOT NULL DEFAULT 'a',
 
    -> `txt` TEXT) engine=InnODB;
 
Query OK, 0 rows affected (0.07 sec)


MariaDB [test]> INSERT INTO `t1` (`txt`) VALUES ('hello world');
 
Query OK, 1 row affected (0.02 sec)


MariaDB [test]> SELECT * FROM `t1`;
 
+----+--------+-------------+
 
| id | status | txt         |
 
+----+--------+-------------+
 
|  1 | a      | hello world |
 
+----+--------+-------------+
 
1 row in set (0.00 sec)


MariaDB [test]> UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;
 
Query OK, 1 row affected, 1 warning (0.05 sec)
 
Rows matched: 1  Changed: 1  Warnings: 1


MariaDB [test]> SHOW WARNINGS;
 
+---------+------+---------------------------------------+
 
| Level   | Code | Message                               |
 
+---------+------+---------------------------------------+
 
| Warning | 1292 | Truncated incorrect INTEGER value: '' |
 
+---------+------+---------------------------------------+
 
1 row in set (0.00 sec)


MariaDB [test]> SELECT * FROM `t1`;
 
+----+--------+------+
 
| id | status | txt  |
 
+----+--------+------+
 
|  1 | a      | 0    |
 
+----+--------+------+
 
1 row in set (0.00 sec)

Comment by Chris Calender (Inactive) [ 2017-01-14 ]

I've narrowed down that the change in behavior begins in 10.1.8 (and was not propagated back to 10.0.x).

Comment by Chris Calender (Inactive) [ 2017-01-14 ]

This fix looks possibly related:

https://jira.mariadb.org/browse/MDEV-8852
https://github.com/MariaDB/server/commit/3cc6e5b

Comment by Alexander Barkov [ 2017-01-17 ]

This UPDATE query is valid:

UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;

It's equal to:

UPDATE `t1` SET `txt`=('' AND `status`='b') WHERE `id`=1;

Behavior was changed by this commit:
d9b25ae3db8584bde809c0ab3230cbe151fa489b

Which fixed the following bugs:
MDEV-8466 CAST works differently for DECIMAL/INT vs DOUBLE for empty strings
MDEV-8468 CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces

The above query casts an empty string to BOOLEAN, which evaluates through testing if its DOUBLE value is not zero.

Before the mentioned commit string-to-double conversion silently ignored empty string. After this commit it returns a warning. In strict mode the warning is converted to an error, hence the UPDATE is rollbacked.

The 10.1 behavior looks correct.

Comment by Chris Calender (Inactive) [ 2017-01-18 ]

Thank you for your analysis.

Should anything be added to the manual in regards to 10.0 versus 10.1 behavior changes?

Comment by Chris Calender (Inactive) [ 2017-01-19 ]

Ah, perhaps this is still a bug.... ?

Note this breaks replication from 10.0 to 10.1 since the fixes referenced were not backported to 10.0.

That is a main problem as a result of this bug.

Comment by Chris Calender (Inactive) [ 2017-01-19 ]

Perhaps the fixes for the following should be back-ported to 10.0, so replication from 10.0 to 10.1 does no break on such a statement:

MDEV-8466 CAST works differently for DECIMAL/INT vs DOUBLE for empty strings
MDEV-8468 CAST and INSERT work differently for DECIMAL/INT vs DOUBLE for a string with trailing spaces

Comment by Alexander Barkov [ 2017-01-19 ]

We discussed it with Serg and think it's not desirable to backport this to 10.0. It's too risky.

Comment by Alexander Vasilkov [ 2017-08-12 ]

I've some problem with exucute script

use rea;
 
SET @no:=0;
 
SET @pre:=0;
 
 
 
DROP VIEW IF EXISTS hirPat_98_2xxx;
 
CREATE OR REPLACE view hirPat_98_2xxx AS
 
  SELECT COUNT(*) cnt, `year`, nHist
 
        FROM hirpat h
 
        WHERE nHist<>0 AND h.dateEn>='2017-07-01' AND h.dateEn<'2017-08-01' AND h.depCode=98
 
        GROUP BY `year`, depCode, nHist, surname, `name`, patronymic
 
        HAVING cnt>1;
 
        
DROP TABLE IF EXISTS hirPat_98_2yyy;
 
CREATE TABLE hirPat_98_2yyy
 
SELECT IF(@pre=h.nHist, @no:=@no+1, @no:=1) `no`, IF(@pre=h.nHist, h.nHist, @pre:=h.nHist) nHist, h.hirPatId, h.dateEn, h.dateEx, h.year
 
        FROM hirpat h
 
        INNER JOIN hirPat_98_2xxx x ON h.year=x.year AND h.nHist=x.nHist
 
        WHERE h.depCode=98;

Table hirPat_98_2yyy not created with message

Error( 1292 ) 22007: "Truncated incorrect DOUBLE value: ''"

But request below executed successful!

SELECT IF(@pre=h.nHist, @no:=@no+1, @no:=1) `no`, IF(@pre=h.nHist, h.nHist, @pre:=h.nHist) nHist, h.hirPatId, h.dateEn, h.dateEx, h.year
 
        FROM hirpat h
 
        INNER JOIN hirPat_98_2xxx x ON h.year=x.year AND h.nHist=x.nHist
 
        WHERE h.depCode=98;

Comment by Alexander Vasilkov [ 2017-08-12 ]

This whole script exec successful on MySQL.
I've try exec script on windows MariaDB server 10.2.7 Community

Comment by Elena Stepanova [ 2022-11-04 ]

Closing since 10.0 has been EOL for a long time.

Generated at Thu Feb 08 07:52:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.