[MDEV-10281] Possible regression with STRICT MODE producing warning on WHERE Clause Created: 2016-06-24  Updated: 2016-08-16  Resolved: 2016-08-16

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.14
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: VAROQUI Stephane Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

Following test case was working in 10.0 and stop executing the last query after migration to 10.1
http://pastebin.com/7MpqiBzp

CREATE  TABLE the_flags
 
(
 
lot_id varchar(255) PRIMARY KEY NOT NULL DEFAULT '' ,
 
flags binary(2) NOT NULL DEFAULT '\0\0'
 
);
 
 
INSERT INTO the_flags VALUES
 
('a', 5),
 
('b', 5),
 
('c', 5);
 
 
CREATE  TABLE the_new_flags
 
(
 
lot_id varchar(255) PRIMARY KEY NOT NULL DEFAULT '',
 
flags binary(2) NOT NULL DEFAULT '\0\0'
 
);
 
 
INSERT INTO the_new_flags
 
(
 
    SELECT lot_id, 1
 
    FROM the_flags
 
    WHERE ((cast( flags as binary) & 4)=4)
 
);

We would like to understand if the enforcement on strict mode do propagate warnings from read queries and what is the truncation issue in this case ?,
The inserted values looks correct in regards to the table definition.

 Comments   
Comment by Geoff Montee (Inactive) [ 2016-07-18 ]

MariaDB 10.0 output:

MariaDB [db1]> CREATE  TABLE the_flags
 
    -> (
 
    -> lot_id varchar(255) PRIMARY KEY NOT NULL DEFAULT '' ,
 
    -> flags binary(2) NOT NULL DEFAULT '\0\0'
 
    -> );
 
Query OK, 0 rows affected (0.02 sec)
 
 
 
MariaDB [db1]> INSERT INTO the_flags VALUES
 
    -> ('a', 5),
 
    -> ('b', 5),
 
    -> ('c', 5);
 
Query OK, 3 rows affected (0.00 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [db1]> CREATE  TABLE the_new_flags
 
    -> (
 
    -> lot_id varchar(255) PRIMARY KEY NOT NULL DEFAULT '',
 
    -> flags binary(2) NOT NULL DEFAULT '\0\0'
 
    -> );
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [db1]> INSERT INTO the_new_flags
 
    -> (
 
    ->     SELECT lot_id, 1
 
    ->     FROM the_flags
 
    ->     WHERE ((cast( flags as binary) & 4)=4)
 
    -> );
 
Query OK, 3 rows affected (0.00 sec)
 
Records: 3  Duplicates: 0  Warnings: 0

MariaDB 10.1 output:

MariaDB [db1]> CREATE  TABLE the_flags
 
    -> (
 
    -> lot_id varchar(255) PRIMARY KEY NOT NULL DEFAULT '' ,
 
    -> flags binary(2) NOT NULL DEFAULT '\0\0'
 
    -> );
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [db1]> INSERT INTO the_flags VALUES
 
    -> ('a', 5),
 
    -> ('b', 5),
 
    -> ('c', 5);
 
Query OK, 3 rows affected (0.00 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [db1]> CREATE  TABLE the_new_flags
 
    -> (
 
    -> lot_id varchar(255) PRIMARY KEY NOT NULL DEFAULT '',
 
    -> flags binary(2) NOT NULL DEFAULT '\0\0'
 
    -> );
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [db1]> INSERT INTO the_new_flags
 
    -> (
 
    ->     SELECT lot_id, 1
 
    ->     FROM the_flags
 
    ->     WHERE ((cast( flags as binary) & 4)=4)
 
    -> );
 
Query OK, 3 rows affected, 3 warnings (0.01 sec)
 
Records: 3  Duplicates: 0  Warnings: 3
 
 
 
MariaDB [db1]> SHOW WARNINGS;
 
+---------+------+--------------------------------------------+
 
| Level   | Code | Message                                    |
 
+---------+------+--------------------------------------------+
 
| Warning | 1292 | Truncated incorrect INTEGER value: '5\x00' |
 
| Warning | 1292 | Truncated incorrect INTEGER value: '5\x00' |
 
| Warning | 1292 | Truncated incorrect INTEGER value: '5\x00' |
 
+---------+------+--------------------------------------------+
 
3 rows in set (0.00 sec)

Comment by Alexander Barkov [ 2016-08-01 ]

A smaller script demonstrating difference between 10.0 and 10.1:

DROP TABLE IF EXISTS t1;
 
CREATE  TABLE t1
 
(
 
  lot_id varchar(255) PRIMARY KEY NOT NULL DEFAULT '' ,
 
  flags binary(2) NOT NULL DEFAULT '\0\0'
 
);
 
INSERT INTO t1 VALUES ('a', 5), ('b', 5), ('c', 5);
 
SELECT lot_id, 1
 
FROM t1
 
WHERE ((cast( flags as binary) & 4)=4);

The last SELECT query returns no warnings in 10.0 and returns one warning per row (3 warnings total) in 10.1.
The new behavior is correct.

The problems seems to be in the SQL design.
"flags" is used in an integer operation context, so perhaps using INT or SMALLINT or BIGINT for "flags" instead of BINARY would work.

Are there any reasons for "flags" to be of BINARY type, and why is CAST(flags AS BINARY) needed?

Comment by Geoff Montee (Inactive) [ 2016-08-09 ]

Thanks for the response, bar.

It sounds like these users may have been a bit confused about how bitwise operators work in MySQL and MariaDB. I think they expected bitwise operators to work without the conversion to bigint, and they were surprised when MariaDB 10.1 started returning warnings for some of these queries, when they seemed to work fine in 10.0.

I also submitted this relevant JIRA issue: MDEV-10526.

Comment by Alexander Barkov [ 2016-08-16 ]

Thanks for creating MDEV-10526!

Can I close MDEV-10281 as "Not a bug"

Comment by VAROQUI Stephane [ 2016-08-16 ]

Yes the workaround was reported and accepted from reporters
Tx Alexander !

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