Unexpected warnings "Encountered illegal value '' when converting to DECIMAL" on a query with aggregate functions and GROUP BY

Bug #1004615 reported by Elena Stepanova on 2012-05-25
16
This bug affects 3 people
Affects Status Importance Assigned to Milestone
MariaDB
New
Medium
Sergei Golubchik

Bug Description

Initially reported as a LP question: https://answers.launchpad.net/maria/+question/198446

SELECT f1, MIN(f2), AVG(f2), SUM(f2) FROM t1
GROUP BY f1 LIMIT 1;
f1 MIN(f2) AVG(f2) SUM(f2)
1 1.0 1.00000 1.0
Warnings:
Error 1918 Encountered illegal value '' when converting to DECIMAL

LIMIT is not important here, it just reduces the amount of output.
The warning only appears with certain number of rows in the table (over 11,000 in the provided test case).
Reproducible on maria-5.3 revno 3526, maria-5.5 revno 3418.
Not reproducible on maria-5.2, mysql-5.1, mysql-5.5, mysql-trunk.
Reproducible with default optimizer switch as well as all OFF values.

# Test case:

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 ( f1 INT, f2 decimal(20,1) ) ENGINE=MyISAM;

--disable_query_log
let $i= 11000;
--echo # <Insert $i rows into the table>
while ($i)
{
  eval INSERT INTO t1 values($i,$i);
  dec $i;
}
--enable_query_log

INSERT INTO t1 values(11001,NULL),(11001,NULL);

SELECT f1, MIN(f2), AVG(f2), SUM(f2) FROM t1
  GROUP BY f1 LIMIT 1;

DROP TABLE IF EXISTS t1;

# End of test case

Not reproducible on maria-5.3.8.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers