Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-736

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

    XMLWordPrintable

Details

    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

      Attachments

        Activity

          People

            serg Sergei Golubchik
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.