Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.0.17
-
Linux
-
10.1.8-3, 10.1.8-4, 10.1.9-3
Description
SET @aaa = COLUMN_CREATE('price', ''); |
SELECT COLUMN_GET(@aaa, 'price' AS DECIMAL) aaa; |
aaa
|
--------
|
0
|
SELECT COLUMN_GET(@aaa, 'price' AS DECIMAL) + 1 aaa |
aaa
|
--------
|
1
|
DELIMITER $$
|
|
|
CREATE FUNCTION `test`( |
par_aaa DECIMAL(10,2) |
)
|
RETURNS DECIMAL(10,2) |
DETERMINISTIC
|
BEGIN |
RETURN 0; |
END$$ |
|
|
DELIMITER ;
|
SELECT test(COLUMN_GET(@aaa, 'price' AS DECIMAL)) bbb |
Error CODE: 1918
|
Encountered illegal VALUE '' WHEN converting TO DECIMAL
|
SELECT test('') bbb |
Error CODE: 1366
|
Incorrect DECIMAL VALUE: '' FOR COLUMN 'par_aaa' AT ROW 441
|
SELECT test(CAST('' AS DECIMAL(10,2))) bbb |
Error CODE: 1292
|
Truncated incorrect DECIMAL VALUE: ''
|
SELECT test(''+0) bbb |
bbb
|
--------
|
0.00
|
More expected that result '' + 0 and CAST('' AS DECIMAL(10,2)) would be same.
But we saw error when do CAST, and automatic convertion '' as 0 when try select or make math operation.
SELECT @@sql_mode |
NO_BACKSLASH_ESCAPES,STRICT_ALL_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
Attachments
Issue Links
- is blocked by
-
-
- Stalled
-
Activity
Mohit, can you please paste SHOW CREATE TABLE for t1, t2 and t3?
Thanks.
Hi Alexander,
I have uploaded actual create table and the event to ftp.askmonty.org/private.
File name is MDEV-8109-mchandel.zip
Can you please also send the structure for a_alarm_code to bar@mariadb.org?
Thanks.
Hi,
I think that I am also affected by this Bug. I have an event which basically does the following:
DECLARE v1 DECIMAL(18,0) DEFAULT 0; -- var is declaredSELECT MAX(c1) INTO v1 FROM t1; -- value is stored in varINSERT INTO t2 SELECT * FROM t3 where c2 > v1; -- var value is used in queryIssue is that my warnings are being converted into error and my event is not being executed.
2017-09-07 11:30:00 139832123037440 [ERROR] Event Scheduler: [root@localhost][netmon.ev] Encountered illegal value '' when converting to DECIMAL2017-09-07 11:30:00 139832123037440 [ERROR] Event Scheduler: [root@localhost][netmon.ev] Encountered illegal value '' when converting to DECIMAL2017-09-07 11:30:00 139832123037440 [ERROR] Event Scheduler: [root@localhost][netmon.ev] Encountered illegal value '' when converting to DECIMAL2017-09-07 11:30:00 139832123037440 [Note] Event Scheduler: [root@localhost].[netmon.ev] event execution failed.MariaDB [(none)]> select version();+-----------------------------------------+| version() |+-----------------------------------------+| 10.2.8-MariaDB-10.2.8+maria~stretch-log |+-----------------------------------------+1 row in set (0.01 sec)MariaDB [(none)]> select @@sql_mode;+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| @@sql_mode |+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)MariaDB [(none)]>