Details
-
New Feature
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
None
-
None
-
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
|
