[#MDEV-8807] Implicit CAST to INT works differently for a field vs a literal

I use a string value in any context that assumes an integer input, for example LEFT(..., <string-value>), which causes implicit type cast.

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(30));

INSERT INTO t1 VALUES ('18446744073709551615');

SELECT a, LEFT('test','18446744073709551615'), LEFT('test',a) FROM t1;

SHOW WARNINGS;

+----------------------+-------------------------------------+----------------+

| a                    | LEFT('test','18446744073709551615') | LEFT('test',a) |

+----------------------+-------------------------------------+----------------+

| 18446744073709551615 |                                     | test           |

+----------------------+-------------------------------------+----------------+

1 row in set, 1 warning (0.00 sec)

+---------+------+-----------------------------------------------------------+

| Level   | Code | Message                                                   |

+---------+------+-----------------------------------------------------------+

| Warning | 1292 | Truncated incorrect INTEGER value: '18446744073709551615' |

+---------+------+-----------------------------------------------------------+

1 row in set (0.00 sec)

Notice, LEFT() with a string literal in the second argument returned an empty string, while LEFT() with a string column with the same value returned 'test'.

Also, only one warning was generated (the literal did not generate a warning, while the table column did).

The expected behavior would be to return the same result and the same set of warnings for the two LEFT() calls.

[MDEV-8807] Implicit CAST to INT works differently for a field vs a literal Created: 2015-09-16 Updated: 2015-10-06
Status:	Open
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	5.5, 10.0, 10.1
Fix Version/s:	10.1

[MDEV-8807] Implicit CAST to INT works differently for a field vs a literal Created: 2015-09-16 Updated: 2015-10-06