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

MariaDB Columnstore: Views with null-replaced timestamps yield incorrect query results

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 22.08.2, 6.4.2, 23.02.3
    • 23.10.2
    • None
    • None
    • MariaDB 10.6.9 CS 6.4.3 RHEL 8
    • 2024-1

    Description

      We plan to store null in the timestamps, and use a Pentaho-specific view to null-replace them.

      When trying this out, we found (again) a discrepancy between InnoDB and Columnstore:
      what should work (and does in InnoDB) yields incorrect query results in Columnstore.

      In detail: using YEAR(...) = 1970 in a WHERE condition on a null-replaced date yields no rows in case of Columstore-tables-based view, but the correct result set when the view is based on a InnoDB table.
      See the attached SQL.

      Attachments

        Issue Links

          Activity

            susil.behera Susil Behera added a comment -

            Repro'd on an older build. Verified on 'commit_hash | 7ec8f3d' build.

            create table t1 (a varchar(1000), b datetime, c int) ENGINE=Columnstore DEFAULT CHARSET=utf8;
            insert into t1 values ('abc', null, 1);
            insert into t1 values ('xyz', str_to_date('2022-09-22 00:00:00', '%Y-%m-%d %H:%i:%s'), 1);
            create view v1 as select a, NVL(b, str_to_date('1970-01-01 00:00:00', '%Y-%m-%d %H:%i:%s')) as b, c from t1;

            MariaDB [testMcs]> select count from v1 where YEAR(b) = 1970;
            ----------

            count

            ----------

            1

            ----------
            1 row in set (0.050 sec)
            MariaDB [testMcs]> SELECT COUNT FROM v1 WHERE MONTH(b) = 00;
            ----------

            COUNT

            ----------

            1

            ----------
            1 row in set (0.011 sec)

            MariaDB [testMcs]> SELECT COUNT FROM v1 WHERE DAY(b) = 00;
            ----------

            COUNT

            ----------

            1

            ----------
            1 row in set (0.010 sec)

            MariaDB [testMcs]> SELECT COUNT FROM v1 WHERE b = '1970-01-01 00:00:00';
            ----------

            count

            ----------

            1

            ----------
            1 row in set (0.009 sec)

            susil.behera Susil Behera added a comment - Repro'd on an older build. Verified on 'commit_hash | 7ec8f3d' build. create table t1 (a varchar(1000), b datetime, c int) ENGINE=Columnstore DEFAULT CHARSET=utf8; insert into t1 values ('abc', null, 1); insert into t1 values ('xyz', str_to_date('2022-09-22 00:00:00', '%Y-%m-%d %H:%i:%s'), 1); create view v1 as select a, NVL(b, str_to_date('1970-01-01 00:00:00', '%Y-%m-%d %H:%i:%s')) as b, c from t1; MariaDB [testMcs] > select count from v1 where YEAR(b) = 1970; ---------- count ---------- 1 ---------- 1 row in set (0.050 sec) MariaDB [testMcs] > SELECT COUNT FROM v1 WHERE MONTH(b) = 00; ---------- COUNT ---------- 1 ---------- 1 row in set (0.011 sec) MariaDB [testMcs] > SELECT COUNT FROM v1 WHERE DAY(b) = 00; ---------- COUNT ---------- 1 ---------- 1 row in set (0.010 sec) MariaDB [testMcs] > SELECT COUNT FROM v1 WHERE b = '1970-01-01 00:00:00'; ---------- count ---------- 1 ---------- 1 row in set (0.009 sec)

            People

              denis0x0D Denis Khalikov (Inactive)
              aeae81 andreas eschbacher
              Leonid Fedorov Leonid Fedorov
              Susil Behera Susil Behera
              Votes:
              1 Vote for this issue
              Watchers:
              6 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.