[MDEV-10252] Row count in 'out of range' warnings is off Created: 2016-06-19  Updated: 2017-09-18

Status: Open
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.1, 10.2
Fix Version/s: 10.2

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Consider the following scenario:

drop table if exists t1;
 
create table t1 (t tinyint, i int);
 
insert into t1 values (1,1000);
 
insert into t1 select i, i from t1;
 
show warnings;
 
insert into t1 select max(i), max(i) from t1;
 
show warnings;

The first INSERT above is inside limits and does not produce any warnings.
So, the row count in the table becomes 1.
The second INSERT attempts to insert a too big value into column t and produces a warning. Before 10.2, it said

MariaDB [test]> show warnings;
 
+---------+------+--------------------------------------------+
 
| Level   | Code | Message                                    |
 
+---------+------+--------------------------------------------+
 
| Warning | 1264 | Out of range value for column 't' at row 1 |
 
+---------+------+--------------------------------------------+
 
1 row in set (0.00 sec)

Apparently meaning that it's at the first inserted row.
In 10.2 it says instead

MariaDB [test]> show warnings;
 
+---------+------+--------------------------------------------+
 
| Level   | Code | Message                                    |
 
+---------+------+--------------------------------------------+
 
| Warning | 1264 | Out of range value for column 't' at row 2 |
 
+---------+------+--------------------------------------------+
 
1 row in set (0.00 sec)

Maybe it means to say that the row it complains about is to become the 2nd row in the table, so it's not technically incorrect, as long as it is an expected change.

Either way, the row count becomes 2.

But the 3rd INSERT is a problem. Before 10.2, it said

MariaDB [test]> show warnings;
 
+---------+------+--------------------------------------------+
 
| Level   | Code | Message                                    |
 
+---------+------+--------------------------------------------+
 
| Warning | 1264 | Out of range value for column 't' at row 3 |
 
+---------+------+--------------------------------------------+
 
1 row in set (0.00 sec)

which we can interpret as a to-be-the-3rd-row in the table. However, in 10.2 it's

MariaDB [test]> show warnings;
 
+---------+------+--------------------------------------------+
 
| Level   | Code | Message                                    |
 
+---------+------+--------------------------------------------+
 
| Warning | 1264 | Out of range value for column 't' at row 5 |
 
+---------+------+--------------------------------------------+
 
1 row in set (0.00 sec)

which just does not have a plausible explanation from the user's perspective.



 Comments   
Comment by Alexander Barkov [ 2017-05-24 ]

Elena, wrong row number in error message is not in my area of expertise. Can you please reassign it to somebody else? Thanks.

Comment by Alexander Barkov [ 2017-05-25 ]

Some more observations:

If I change the data type from tinytint to decimal(1,0):

SET sql_mode='';
 
drop table if exists t1;
 
create table t1 (t decimal(1,0), i int);
 
insert into t1 values (1,1000);
 
insert into t1 select i, i from t1;
 
show warnings;
 
insert into t1 select max(i), max(i) from t1;
 
show warnings;

In 10.0 it correctly reports:

  • row 1 in the first SHOW WARNINGS
  • row 3 in the second SHOW WARNINGS

In 10.2 it erroneously reports:

  • row 2 in the first SHOW WARNINGS
  • row 5 in the second SHOW WARNINGS

This makes me feel that this problem did not apper after MDEV-9393 (only MDEV-9705 appeared after MDEV-9393).

Another reason to think that MDEV-9393 is not the reason for MDEV-10252 is that MDEV-9393 affected ALTER behavior. Here we don't have ALTER.

Comment by Sergei Golubchik [ 2017-06-22 ]

It's in 10.1 all right. The warning does not tell, what row it will become in the table, compare with:

MariaDB [test]> insert t1 select 1000, 1000 from t1;
 
MariaDB [test]> show warnings;
 
+---------+------+--------------------------------------------+
 
| Level   | Code | Message                                    |
 
+---------+------+--------------------------------------------+
 
| Warning | 1264 | Out of range value for column 't' at row 1 |
 
+---------+------+--------------------------------------------+
 
1 row in set (0.00 sec)

Comment by Sergei Golubchik [ 2017-09-18 ]

The logic is fairly convoluted and, well, doesn't look very logical to me. It'd like to define and implement simple and consistent rule for this row number, but it'd be too big a change for 10.1.

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