[MDEV-10110] Warning when to use SUBSTR function Created: 2016-05-24  Updated: 2016-06-21  Resolved: 2016-06-21

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.1.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Diego Hellas Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS 7
MariaDb 10.1.12/MariaDB 10.1.13

Attachments: Text File show_variables_maria01.txt    

 Description   

Execute this command:

select SUBSTR("MariaDB", 1, 1); show warnings;

Return this message :

+---------+------+---------------------------------------+
 
| Level   | Code | Message                               |
 
+---------+------+---------------------------------------+
 
| Warning | 1292 | Truncated incorrect DOUBLE value: 'M' |
 
+---------+------+---------------------------------------+

This message does not seem to be correct. I did not understand the function of connection with the double.

In MySQL we do not have that message.

 Comments   
Comment by Elena Stepanova [ 2016-05-27 ]

I don't get the warning:

MariaDB [test]> select SUBSTR("MariaDB", 1, 1); 
+-------------------------+
 
| SUBSTR("MariaDB", 1, 1) |
 
+-------------------------+
 
| M                       |
 
+-------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> show warnings;
 
Empty set (0.00 sec)

Please attach your cnf file(s) or paste the output of SHOW VARIABLES from the very same session where you are getting the warning.

Is it possible that what you see is a left-over warning from a previous activity? Something like this can cause it:

MariaDB [test]> select cast('M' as double);
 
+---------------------+
 
| cast('M' as double) |
 
+---------------------+
 
|                   0 |
 
+---------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+---------------------------------------+
 
| Level   | Code | Message                               |
 
+---------+------+---------------------------------------+
 
| Warning | 1292 | Truncated incorrect DOUBLE value: 'M' |
 
+---------+------+---------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select SUBSTR("MariaDB", 1, 1); 
+-------------------------+
 
| SUBSTR("MariaDB", 1, 1) |
 
+-------------------------+
 
| M                       |
 
+-------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+---------------------------------------+
 
| Level   | Code | Message                               |
 
+---------+------+---------------------------------------+
 
| Warning | 1292 | Truncated incorrect DOUBLE value: 'M' |
 
+---------+------+---------------------------------------+
 
1 row in set (0.00 sec)

Comment by Diego Hellas [ 2016-05-31 ]

show_variables_maria01.txt

Hi @Elena, attached the file with the show variables .

Comment by Elena Stepanova [ 2016-06-01 ]

diegohellas, thanks.

I don't see in the variables anything that would cause the problem.
Could you please paste the complete unabridged output of the following:

show warnings;
 
select SUBSTR("MariaDB", 1, 1); 
show warnings;

from MySQL client when you are getting the warning.

Comment by Diego Hellas [ 2016-06-03 ]

Now i know simulate the problem.

Here dosen't have problem.

show warnings;
 
select SUBSTR("MariaDB", 1, 1); 
show warnings;

The problem is here.

show warnings;
 
select SUBSTR("MariaDB", 1, 1) = 1;
 
show warnings;


+---------+------+---------------------------------------+
 
| Level   | Code | Message                               |
 
+---------+------+---------------------------------------+
 
| Warning | 1292 | Truncated incorrect DOUBLE value: 'M' |
 
+---------+------+---------------------------------------+

I need check if the first character is 1(number 1). But my column have values with number and string.

I solved my problem so

MariaDB [(none)]> select SUBSTR("MariaDB", 1, 1) = '1';
 
+-------------------------------+
 
| SUBSTR("MariaDB", 1, 1) = '1' |
 
+-------------------------------+
 
|                             0 |
 
+-------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [(none)]> show warnings;
 
Empty set (0.00 sec)

But anyway, MySQL does not return warnings.

Comment by Elena Stepanova [ 2016-06-09 ]

Thanks. This way I can reproduce it easily, but I don't think it's a bug.

You can just as well run this:

MariaDB [test]> select 'M' = 1;
 
+---------+
 
| 'M' = 1 |
 
+---------+
 
|       0 |
 
+---------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+---------------------------------------+
 
| Level   | Code | Message                               |
 
+---------+------+---------------------------------------+
 
| Warning | 1292 | Truncated incorrect DOUBLE value: 'M' |
 
+---------+------+---------------------------------------+
 
1 row in set (0.00 sec)

And in this case the warning is produced by MySQL as well:

MySQL [test]> select 'M' = 1;
 
+---------+
 
| 'M' = 1 |
 
+---------+
 
|       0 |
 
+---------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MySQL [test]> show warnings;
 
+---------+------+---------------------------------------+
 
| Level   | Code | Message                               |
 
+---------+------+---------------------------------------+
 
| Warning | 1292 | Truncated incorrect DOUBLE value: 'M' |
 
+---------+------+---------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MySQL [test]> select @@version;
 
+--------------+
 
| @@version    |
 
+--------------+
 
| 5.6.30-debug |
 
+--------------+
 
1 row in set (0.00 sec)

The warning itself seems to be justified in this case.

But the query that you identified, indeed, only produces the warning on MariaDB, but not MySQL.
So, I think MariaDB behaves more consistently here; but I might be wrong, so I will assign it to the datatype conversion expert bar to confirm.

Comment by Diego Hellas [ 2016-06-15 ]

Elena, I hope I've helped.
If you need anything let me know.

Comment by Alexander Barkov [ 2016-06-21 ]

The warning is valid here. Not a bug.
Comparison between a string and a number is done as double.

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