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

"is null" operator reports an error for a

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Won't Do
    • 1.1.5
    • Icebox
    • ExeMgr
    • None
    • Single Node UM/PM installation in Centos.

    Description

      The "is null" as well "is not null" operators do not work with Boolean predicate expressions. It works in the select list. The same queries work in MariaDB server product. Here is the output of queries i tried:

      --------------
      DROP TABLE if exists t1
      --------------

      --------------
      DROP TABLE if exists t2
      --------------

      --------------
      CREATE TABLE t1 ( c1 int not null, c2 float, c3 char(10)) engine=columnstore
      --------------

      --------------
      insert into t1 values(1,1.0, 'one'), (2,2.0, 'two'), (3, 3.1, 'three')
      --------------

      --------------
      CREATE TABLE t2 ( c1 int not null, c2 float, c3 char(10)) engine=innodb
      --------------

      --------------
      insert into t2 values(1,1.0, 'one'), (2,2.0, 'two'), (3, 3.1, 'three')
      --------------

      --------------
      select c1, c2+5 from t1 where (case when c1 > 2 then 1 else null end)
      --------------

      ---------------------+

      c1 c2+5

      ---------------------+

      3 8.099999904632568

      ---------------------+
      --------------
      select c1, c2+5 from t1 where c1 > 2 is not null
      --------------

      ERROR 1815 (HY000) at line 16: Internal error: IDB-2030: Predicate and Logic operators can not be used where an expression is expected.
      --------------
      select c1, c2+5, (c1 > 1) is null from t1
      --------------

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

      c1 c2+5 (c1 > 1) is null

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

      1 6 0
      2 7 0
      3 8.099999904632568 0

      -------------------------------------
      --------------
      select c1, c2+5 from t2 where (case when c1 > 2 then 1 else null end)
      --------------

      ---------------------+

      c1 c2+5

      ---------------------+

      3 8.099999904632568

      ---------------------+
      --------------
      select c1, c2+5 from t2 where c1 > 2 is not null
      --------------

      ---------------------+

      c1 c2+5

      ---------------------+

      1 6
      2 7
      3 8.099999904632568

      ---------------------+

      I have attached my sql script.

      Attachments

        Activity

          People

            Unassigned Unassigned
            rprakash Ravi Prakash (Inactive)
            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.