Bug #43690 Falcon gives wrong error & add warnings on updating for cols. in where clause
Submitted: 17 Mar 2009 8:02 Modified: 26 May 2010 17:52
Reporter: Nidhi Shrotriya Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S3 (Non-critical)
Version:6.0.11 OS:Any
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: F_ERROR HANDLING

[17 Mar 2009 8:02] Nidhi Shrotriya
Description:
CREATE TABLE t1(c1 FLOAT(10,5) UNSIGNED NOT NULL, c2 FLOAT(10,5) SIGNED NULL, c3 FLOAT, c4 INT, UNIQUE INDEX idx(c1,c4));

inserted some rows.

This piece of code-
SELECT c1 FROM t1 WHERE c1>1000;
SET SQL_MODE=STRICT_ALL_TABLES;
--error ER_BAD_NULL_ERROR
UPDATE t1 SET c1=NULL WHERE c1>1000;

gives-

query 'UPDATE t1 SET c1=NULL WHERE c1>1000' failed with wrong errno 1264: 'Out of range value for column 'c1' at row 1', instead of 1048...

SELECT c1 FROM t1 WHERE c1>1000;
c1
10000.00000
100000.00000
100000.00000
100000.00000
100000.00000
100000.00000
10001.00000
SET SQL_MODE=STRICT_ALL_TABLES;

whereas all other engines do fine, give bad null error.

How to repeat:
Please find attached the test case file.
[17 Mar 2009 8:03] Nidhi Shrotriya
Test Case

Attachment: update_decimal_bug_falcon.test (application/octet-stream, text), 1.92 KiB.

[17 Mar 2009 8:14] Nidhi Shrotriya
Update ignore gives different warnings.

UPDATE IGNORE t1 SET c1=NULL WHERE c1>1000;
 Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
 Warning	1048	Column 'c1' cannot be null
+Warning	1264	Out of range value for column 'c1' at row 2
 Warning	1048	Column 'c1' cannot be null
 Warning	1048	Column 'c1' cannot be null
+Warning	1264	Out of range value for column 'c1' at row 4
 Warning	1048	Column 'c1' cannot be null
+Warning	1264	Out of range value for column 'c1' at row 5
 Warning	1048	Column 'c1' cannot be null
+Warning	1264	Out of range value for column 'c1' at row 6
 Warning	1048	Column 'c1' cannot be null
 Warning	1048	Column 'c1' cannot be null
[17 Mar 2009 8:55] Sveta Smirnova
Thank you for the report.

Verified as described.
[17 Mar 2009 9:09] Nidhi Shrotriya
Appears at more places than float and null combination. Has become more generic. So changing the subject line.

With table as below
CREATE TABLE t4(c1 YEAR(2), c2 YEAR(2), UNIQUE INDEX idx(c1,c2));

Other engines:
--------------
UPDATE t4 SET c1=-70 WHERE c2=75;
Warnings:
Warning 1264    Out of range value for column 'c1' at row 17

Falcon:
---------
UPDATE t4 SET c1=-70 WHERE c2=75;
Warnings:
Warning 1264    Out of range value for column 'c2' at row 7
Warning 1264    Out of range value for column 'c1' at row 17
Warning 1264    Out of range value for column 'c2' at row 21

Gives additional warnings for the col. in where clause.