Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5307

select-union-select gives corrupted values

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Critical
    • Resolution: Unresolved
    • None
    • Icebox
    • None
    • mdb 10.11 branch bb-10.11-mdev-25080-fix1
      columnstore branch columnstore-22.08.4-1

    Description

      DROP DATABASE IF EXISTS mcol641_union_db;

      CREATE DATABASE mcol641_union_db;
      USE mcol641_union_db;

      SET SQL_MODE='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

      CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38,10), d3 DECIMAL(38,38)) ENGINE=columnstore;
      CREATE TABLE cs2 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38,10) UNSIGNED, d3 DECIMAL(38,38) UNSIGNED) ENGINE=columnstore;

      INSERT INTO cs1 VALUES (125, 1.25, 0.125);
      INSERT INTO cs1 VALUES (-125, -1.25, -0.125);
      INSERT INTO cs1 values (99999999999999999999999999999999999998, 9999999999999999999999999999.9999999998, 0.99999999999999999999999999999999999998);
      INSERT INTO cs1 values (99999999999999999999999999999999999999, 9999999999999999999999999999.9999999999, 0.99999999999999999999999999999999999999);
      INSERT INTO cs1 values (-99999999999999999999999999999999999998, -9999999999999999999999999999.9999999998, -0.99999999999999999999999999999999999998);
      INSERT INTO cs1 values (-99999999999999999999999999999999999999, -9999999999999999999999999999.9999999999, -0.99999999999999999999999999999999999999);

      SELECT d1, d1, d2 FROM cs1 UNION SELECT d2, d3, d3 FROM cs1;

      SHOULD GIVE

      d1 d1 d2
      125.0000000000 125.00000000000000000000000000000000000000 1.25000000000000000000000000000000000000
      -125.0000000000 -125.00000000000000000000000000000000000000 -1.25000000000000000000000000000000000000
      99999999999999999999999999999999999998.0000000000 999999999999999999999999999.99999999999999999999999999999999999999 999999999999999999999999999.99999999999999999999999999999999999999
      99999999999999999999999999999999999999.0000000000 999999999999999999999999999.99999999999999999999999999999999999999 999999999999999999999999999.99999999999999999999999999999999999999
      -99999999999999999999999999999999999998.0000000000 -999999999999999999999999999.99999999999999999999999999999999999999 -999999999999999999999999999.99999999999999999999999999999999999999
      -99999999999999999999999999999999999999.0000000000 -999999999999999999999999999.99999999999999999999999999999999999999 -999999999999999999999999999.99999999999999999999999999999999999999
      1.2500000000 0.12500000000000000000000000000000000000 0.12500000000000000000000000000000000000
      -1.2500000000 -0.12500000000000000000000000000000000000 -0.12500000000000000000000000000000000000
      9999999999999999999999999999.9999999998 0.99999999999999999999999999999999999998 0.99999999999999999999999999999999999998
      9999999999999999999999999999.9999999999 0.99999999999999999999999999999999999999 0.99999999999999999999999999999999999999
      -9999999999999999999999999999.9999999998 -0.99999999999999999999999999999999999998 -0.99999999999999999999999999999999999998
      -9999999999999999999999999999.9999999999 -0.99999999999999999999999999999999999999 -0.99999999999999999999999999999999999999

      BUT GIVES (~random incorrect values in columns)

      ----------------------------------------------------------------------------------------------------------------------------

      d1 d1 d2

      ----------------------------------------------------------------------------------------------------------------------------

      125.0000000000 -0.90447576074723148144860474975423823872 1.25000000000000000000000000000000000000
      -125.0000000000 0.90447576074723148144860474975423823872 -1.25000000000000000000000000000000000000
      1896011491092736564947192742.6130393088 -0.41211004042422656213785281988311121920 0.18924529734740337425340993422692974592
      1896011491092736564947192743.6130393088 0.58788995957577343786214718011688878080 0.18924529744740337425340993422692974592
      -1896011491092736564947192742.6130393088 0.41211004042422656213785281988311121920 -0.18924529734740337425340993422692974592
      -1896011491092736564947192743.6130393088 -0.58788995957577343786214718011688878080 -0.18924529744740337425340993422692974592
      1.2500000000 0.12500000000000000000000000000000000000 0.12500000000000000000000000000000000000
      -1.2500000000 -0.12500000000000000000000000000000000000 -0.12500000000000000000000000000000000000
      9999999999999999999999999999.9999999998 0.99999999999999999999999999999999999998 0.99999999999999999999999999999999999998
      9999999999999999999999999999.9999999999 0.99999999999999999999999999999999999999 0.99999999999999999999999999999999999999
      -9999999999999999999999999999.9999999998 -0.99999999999999999999999999999999999998 -0.99999999999999999999999999999999999998
      -9999999999999999999999999999.9999999999 -0.99999999999999999999999999999999999999 -0.99999999999999999999999999999999999999

      ----------------------------------------------------------------------------------------------------------------------------

      RUN WITHOUT COLUMNSTORE GIVES CORRECT RESULT:

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      d1 d1 d2

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      125.0000000000 125.00000000000000000000000000000000000000 1.25000000000000000000000000000000000000
      -125.0000000000 -125.00000000000000000000000000000000000000 -1.25000000000000000000000000000000000000
      99999999999999999999999999999999999998.0000000000 999999999999999999999999999.99999999999999999999999999999999999999 999999999999999999999999999.99999999999999999999999999999999999999
      99999999999999999999999999999999999999.0000000000 999999999999999999999999999.99999999999999999999999999999999999999 999999999999999999999999999.99999999999999999999999999999999999999
      -99999999999999999999999999999999999998.0000000000 -999999999999999999999999999.99999999999999999999999999999999999999 -999999999999999999999999999.99999999999999999999999999999999999999
      -99999999999999999999999999999999999999.0000000000 -999999999999999999999999999.99999999999999999999999999999999999999 -999999999999999999999999999.99999999999999999999999999999999999999
      1.2500000000 0.12500000000000000000000000000000000000 0.12500000000000000000000000000000000000
      -1.2500000000 -0.12500000000000000000000000000000000000 -0.12500000000000000000000000000000000000
      9999999999999999999999999999.9999999998 0.99999999999999999999999999999999999998 0.99999999999999999999999999999999999998
      9999999999999999999999999999.9999999999 0.99999999999999999999999999999999999999 0.99999999999999999999999999999999999999
      -9999999999999999999999999999.9999999998 -0.99999999999999999999999999999999999998 -0.99999999999999999999999999999999999998
      -9999999999999999999999999999.9999999999 -0.99999999999999999999999999999999999999 -0.99999999999999999999999999999999999999

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Single selects without union:
      SELECT d1, d1, d2 FROM cs1;
      SELECT d2, d3, d3 FROM cs1;
      work fine.

      Attachments

        Activity

          People

            drrtuy Roman
            kirill.perov@mariadb.com Kirill Perov
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.