maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Alexander!

Summary: agree with 1,2,3, not so sure about 4.

> 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.

Agree

> 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.

That wouldv'e been ok if there was some "native" type of
COLUMN_GET(@aaa, 'price'). Like, you can insert the value in its
"native" type (relying on implicit type conversion):

  INSERT INTO t1 VALUES ('');

or cast it explicitly:

  INSERT INTO t1 VALUES (CAST('' AS DECIMAL))

But with dynamic columns there is no "native" type, you always *must*
cast it to something. If strict mode won't turn these warnings into
errors, there will be no way to do that. Normally you add a cast to
insert with warnings and remove a cast to let strict mode turn warnings
into errors (here I assume the case 3 from above is already fixed :).
But with dynamic columns there is no cast to remove.

So I agree with you here, because logically there is an explicit
conversion and strict mode should not turn this warning into an error.
Still, if we do that, there will be no way to catch conversion
warnings in dynamic columns. Unless you introduce STRICT_DYNAMIC_COLUMNS
mode :)

Regards,
Sergei