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

Wrong result for CAST(AVG(double_column) AS SIGNED)

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.0(EOL), 10.1(EOL)
    • 10.4(EOL)
    • Data types
    • None

    Description

      It's similar to MDEV-8919, but now for a DOUBLE rather than BIGINT column.

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (id INT, a DOUBLE);
      INSERT INTO t1 VALUES (1,0x7FFFFFFFFFFFFFFF),(2,0x7FFFFFFFFFFFFFFF);
      SELECT id, AVG(a) AS avg, CAST(MIN(a) AS SIGNED) AS cast_min,CAST(AVG(a) AS SIGNED) AS cast_avg FROM t1 GROUP BY id HAVING avg!=123 ORDER BY id;

      returns

      +------+----------------------+---------------------+----------------------+
      | id   | avg                  | cast_min            | cast_avg             |
      +------+----------------------+---------------------+----------------------+
      |    1 | 9.223372036854776e18 | 9223372036854775807 | -9223372036854775808 |
      |    2 | 9.223372036854776e18 | 9223372036854775807 | -9223372036854775808 |
      +------+----------------------+---------------------+----------------------+

      The values for the cast_avg column are wrong.

      A similar problem:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DOUBLE);
      INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFF);
      SELECT MIN(a), SUM(a), CAST(SUM(a) AS SIGNED), CAST(AVG(a) AS SIGNED) FROM t1;

      returns

      +----------------------+----------------------+------------------------+------------------------+
      | MIN(a)               | SUM(a)               | CAST(SUM(a) AS SIGNED) | CAST(AVG(a) AS SIGNED) |
      +----------------------+----------------------+------------------------+------------------------+
      | 9.223372036854776e18 | 9.223372036854776e18 |   -9223372036854775808 |   -9223372036854775808 |
      +----------------------+----------------------+------------------------+------------------------+

      The value for the third and the fourth columns are wrong.

      Attachments

        Issue Links

          Activity

            There are no comments yet on this issue.

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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