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

Bit functions must behave as with other generic engines(MyISAM, InnoDB) do

    XMLWordPrintable

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • None
    • None
    • PrimProc
    • None

    Description

      Here are some tests that demonstrate difference b/w MyISAM and Columnstore:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TIME(1)) ENGINE=ColumnStore;
      INSERT INTO t1 VALUES ('00:00:01.9');
      SELECT 1<<a, 1.0<<a, 1 & a, 1.0 & a FROM t1;
      +------+--------+-------+---------+
      | 1<<a | 1.0<<a | 1 & a | 1.0 & a |
      +------+--------+-------+---------+
      |    2 |      2 |     1 |       1 |
      +------+--------+-------+---------+
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TIME(1)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('00:00:01.9');
      SELECT 1<<a, 1.0<<a, 1 & a, 1.0 & a FROM t1;
      +------+--------+-------+---------+
      | 1<<a | 1.0<<a | 1 & a | 1.0 & a |
      +------+--------+-------+---------+
      |    2 |      2 |     0 |       0 |
      +------+--------+-------+---------+
      ######################################
       
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(10)) ENGINE=ColumnStore;
      INSERT INTO t1 VALUES ('1.9');
      SELECT 1<<a, 1.0<<a, 1 & a, 1.0 & a FROM t1;
      +------+--------+-------+---------+
      | 1<<a | 1.0<<a | 1 & a | 1.0 & a |
      +------+--------+-------+---------+
      |    2 |      2 |     1 |       1 |
      +------+--------+-------+---------+
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('1.9');
      SELECT 1<<a, 1.0<<a, 1 & a, 1.0 & a FROM t1;
      +------+--------+-------+---------+
      | 1<<a | 1.0<<a | 1 & a | 1.0 & a |
      +------+--------+-------+---------+
      |    2 |      2 |     0 |       0 |
      +------+--------+-------+---------+
      #####################################
       
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DECIMAL(30,0) NOT NULL) ENGINE=ColumnStore;
      INSERT INTO t1 VALUES (1000000000000000000000000000);
      SELECT a<<0, a <<1, a << 2 FROM t1;
      +----------------------+-------+----------------------+
      | a<<0                 | a <<1 | a << 2               |
      +----------------------+-------+----------------------+
      | 18446744073709551615 |     0 | 18446744073709551612 |
      +----------------------+-------+----------------------+
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DECIMAL(30,0) NOT NULL) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1000000000000000000000000000);
      SELECT a<<0, a <<1, a << 2 FROM t1;
      +----------------------+----------------------+----------------------+
      | a<<0                 | a <<1                | a << 2               |
      +----------------------+----------------------+----------------------+
      | 18446744073709551615 | 18446744073709551614 | 18446744073709551612 |
      +----------------------+----------------------+----------------------+
      ########################################
       
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT) ENGINE=ColumnStore;
      INSERT INTO t1 VALUES (65);
      SELECT 1<<a FROM t1;
      +------+
      | 1<<a |
      +------+
      |    2 |
      +------+
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (65);
      SELECT 1<<a FROM t1;
      +------+
      | 1<<a |
      +------+
      |    0 |
      +------+
      ########################################
       
      ColumnStore performs eager operand evaluation in bit-AND and bit-OR:
       
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT) ENGINE=ColumnStore;
      INSERT INTO t1 VALUES (NULL);
      SELECT a & SLEEP(3) FROM t1;
      +--------------+
      | a & SLEEP(3) |
      +--------------+
      |         NULL |
      +--------------+
      1 row in set (3.106 sec)  <-- notice timing
      MariaDB performs lazy operand evaluation in bit-AND and bit-OR
      (if the first operand is NULL, the second operand is not evaluated)
       
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (NULL);
      SELECT a & SLEEP(3) FROM t1;
      +--------------+
      | a & SLEEP(3) |
      +--------------+
      |         NULL |
      +--------------+
      1 row in set (0.001 sec)  <-- notice timing
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              drrtuy Roman
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.