maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

Hi Sergei,


I'm thinking about what to do with MDEV-8109 unexpected CAST result.


Observations:


1. An empty string in VALUES

SET sql_mode='STRICT_ALL_TABLES';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DECIMAL);
INSERT INTO t1 VALUES ('');

ERROR 1292 (22007): Truncated incorrect DECIMAL value: ''

This is OK.
In strict mode INSERT with a bad value correctly returns the error.



2. CAST('' AS DECIMAL)

SET sql_mode='STRICT_ALL_TABLES';
SELECT CAST('' AS DECIMAL);
+---------------------+
| CAST('' AS DECIMAL) |
+---------------------+
|                   0 |
+---------------------+
1 row in set, 1 warning (0.00 sec)

This is also OK. There are no tables involved, and CAST itself does not 
convert warnings to errors.



3. CAST('' AS DECIMAL) in VALUES:

INSERT INTO t1 VALUES(CAST('' AS DECIMAL));
ERROR 1292 (22007): Truncated incorrect DECIMAL value: ''

I think this is not OK.

I wrote an explicit CAST, so I expect:
- CAST to return 0 with a warning, like in #2
- INSERT to write 0 into the table normally, as I'm actually inserting
  the CAST result, which is 0.



4. COLUMN_GET(AS DECIMAL) in VALUES:

SET @aaa = COLUMN_CREATE('price', '');
INSERT INTO t1 VALUES(COLUMN_GET(@aaa, 'price' AS DECIMAL));
ERROR 1918 (22007): Encountered illegal value '' when converting to DECIMAL

This is also not OK.

I wrote explicit column conversion to DECIMAL.
I expect:
- COLUMN_GET() to return 0 with a warning, like in #2
- INSERT to write 0 into the table normally, as I'm actually inserting
  the COLUMN_GET result, which is 0.



Do you agree?

Thanks.