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