|
Bitwise operations in ColumnStore demonstate multiple cases when they work not like in other MariaDB engines.
DECIMAL(30,1) input
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1()
|
BEGIN
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(30,1) NOT NULL);
|
INSERT INTO t1 VALUES (99999999999999999999999999999.9);
|
INSERT INTO t1 VALUES (28446744073709551615);
|
INSERT INTO t1 VALUES (2.9);
|
INSERT INTO t1 VALUES (-2.9);
|
INSERT INTO t1 VALUES (-28446744073709551615);
|
INSERT INTO t1 VALUES (-99999999999999999999999999999.9);
|
SELECT a, a & a, a | a, a^0, a<<1, a>>1, bit_count(a) FROM t1;
|
END;
|
$$
|
DELIMITER ;
|
SET @@default_storage_engine=MyISAM;
|
CALL p1;
|
SET @@default_storage_engine=ColumnStore;
|
CALL p1;
|
Observations:
- Negative numbers return bad results in some cases
- bit_count() returns more than 64
+----------------------------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
| a | a & a | a | a | a^0 | a<<1 | a>>1 | bit_count(a) |
|
+----------------------------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
| 99999999999999999999999999999.9 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551614 | 9223372036854775807 | 64 |
|
| 28446744073709551615.0 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551614 | 9223372036854775807 | 64 |
|
| 2.9 | 3 | 3 | 3 | 6 | 1 | 2 |
|
| -2.9 | 18446744073709551613 | 18446744073709551613 | 18446744073709551613 | 18446744073709551610 | 9223372036854775806 | 63 |
|
| -28446744073709551615.0 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 0 | 4611686018427387904 | 1 |
|
| -99999999999999999999999999999.9 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 0 | 4611686018427387904 | 1 |
|
+----------------------------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
+----------------------------------+----------------------+----------------------+----------------------+----------------------+----------------------+--------------+
|
| a | a & a | a | a | a^0 | a<<1 | a>>1 | bit_count(a) |
|
+----------------------------------+----------------------+----------------------+----------------------+----------------------+----------------------+--------------+
|
| 99999999999999999999999999999.9 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 36 |
|
| 28446744073709551615.0 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 14223372036854775807 | 38 |
|
| 2.9 | 3 | 3 | 3 | 6 | 1 | 2 |
|
| -2.9 | 18446744073709551613 | 18446744073709551613 | 18446744073709551613 | 18446744073709551610 | 1 | 127 |
|
| -28446744073709551615.0 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 91 |
|
| -99999999999999999999999999999.9 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 64 |
|
+----------------------------------+----------------------+----------------------+----------------------+----------------------+----------------------+--------------+
|
DECIMAL(20,0) input
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1()
|
BEGIN
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(20,0) NOT NULL);
|
INSERT INTO t1 VALUES (28446744073709551615);
|
INSERT INTO t1 VALUES (-28446744073709551615);
|
SELECT a, a & a, a | a, a^0, a<<1, a>>1, bit_count(a) FROM t1;
|
END;
|
$$
|
DELIMITER ;
|
SET @@default_storage_engine=MyISAM;
|
CALL p1;
|
SET @@default_storage_engine=ColumnStore;
|
CALL p1;
|
+-----------------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
| a | a & a | a | a | a^0 | a<<1 | a>>1 | bit_count(a) |
|
+-----------------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
| 28446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551614 | 9223372036854775807 | 64 |
|
| -28446744073709551615 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 0 | 4611686018427387904 | 1 |
|
+-----------------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
+-----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+--------------+
|
| a | a & a | a | a | a^0 | a<<1 | a>>1 | bit_count(a) |
|
+-----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+--------------+
|
| 28446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 14223372036854775807 | 38 |
|
| -28446744073709551615 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 91 |
|
+-----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+--------------+
|
Observations:
- ColumnStore return a wrong result for <<
- ColumnStore return a wrong result for >>
- ColumnStore return a wrong result for bit_count()
VARCHAR input
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1()
|
BEGIN
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(30) NOT NULL);
|
INSERT INTO t1 VALUES ('2.9');
|
INSERT INTO t1 VALUES ('18446744073709551615');
|
SELECT a, a & a, a | a, a^0, a<<1, a>>1, bit_count(a) FROM t1;
|
END;
|
$$
|
DELIMITER ;
|
SET @@default_storage_engine=MyISAM;
|
CALL p1;
|
SET @@default_storage_engine=ColumnStore;
|
CALL p1;
|
+----------------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
| a | a & a | a | a | a^0 | a<<1 | a>>1 | bit_count(a) |
|
+----------------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
| 2.9 | 3 | 3 | 3 | 6 | 1 | 2 |
|
| 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551614 | 9223372036854775807 | 64 |
|
+----------------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
+----------------------+---------------------+---------------------+---------------------+------+---------------------+--------------+
|
| a | a & a | a | a | a^0 | a<<1 | a>>1 | bit_count(a) |
|
+----------------------+---------------------+---------------------+---------------------+------+---------------------+--------------+
|
| 2.9 | 2 | 2 | 2 | 4 | 1 | 1 |
|
| 18446744073709551615 | 9223372036854775807 | 9223372036854775807 | 9223372036854775807 | 4 | 4611686018427387903 | 63 |
|
+----------------------+---------------------+---------------------+---------------------+------+---------------------+--------------+
|
Observations:
- Columnstore does not perform rounding
- Columnstore does not handle huge positive values well
Double input
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1()
|
BEGIN
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DOUBLE NOT NULL);
|
INSERT INTO t1 VALUES (2.9);
|
INSERT INTO t1 VALUES (10e30);
|
SELECT a, a & a, a | a, a^0, a<<1, a>>1, bit_count(a) FROM t1;
|
END;
|
$$
|
DELIMITER ;
|
SET @@default_storage_engine=MyISAM;
|
CALL p1;
|
SET @@default_storage_engine=ColumnStore;
|
CALL p1;
|
+------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
| a | a & a | a | a | a^0 | a<<1 | a>>1 | bit_count(a) |
|
+------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
| 2.9 | 3 | 3 | 3 | 6 | 1 | 2 |
|
| 1e31 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551614 | 9223372036854775807 | 64 |
|
+------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
+------+---------------------+---------------------+---------------------+------+---------------------+--------------+
|
| a | a & a | a | a | a^0 | a<<1 | a>>1 | bit_count(a) |
|
+------+---------------------+---------------------+---------------------+------+---------------------+--------------+
|
| 2.9 | 2 | 2 | 2 | 4 | 1 | 1 |
|
| 1e31 | 9223372036854775808 | 9223372036854775808 | 9223372036854775808 | 0 | 4611686018427387904 | 1 |
|
+------+---------------------+---------------------+---------------------+------+---------------------+--------------+
|
Observations:
- ColumnStore does not perform rounding
- Columnstore does not handle huge positive values well
- Right shift returns a strange result
TIME input
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1()
|
BEGIN
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a TIME(1) NOT NULL);
|
INSERT INTO t1 VALUES ('00:00:02.9');
|
INSERT INTO t1 VALUES ('800:00:02.9');
|
INSERT INTO t1 VALUES ('-800:00:02.9');
|
SELECT a, a & a, a | a, a^0, a<<1, a>>1, bit_count(a) FROM t1;
|
END;
|
$$
|
DELIMITER ;
|
SET @@default_storage_engine=MyISAM;
|
CALL p1;
|
SET @@default_storage_engine=ColumnStore;
|
CALL p1;
|
+--------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
| a | a & a | a | a | a^0 | a<<1 | a>>1 | bit_count(a) |
|
+--------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
| 00:00:02.9 | 3 | 3 | 3 | 6 | 1 | 2 |
|
| 800:00:02.9 | 8000003 | 8000003 | 8000003 | 16000006 | 4000001 | 9 |
|
| -800:00:02.9 | 18446744073701551613 | 18446744073701551613 | 18446744073701551613 | 18446744073693551610 | 9223372036850775806 | 56 |
|
+--------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
+--------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
| a | a & a | a | a | a^0 | a<<1 | a>>1 | bit_count(a) |
|
+--------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
| 00:00:02.9 | 2 | 2 | 2 | 4 | 1 | 1 |
|
| 800:00:02.9 | 8000002 | 8000002 | 8000002 | 16000004 | 4000001 | 8 |
|
| -800:00:02.9 | 18446744073701551614 | 18446744073701551614 | 18446744073701551614 | 18446744073693551612 | 9223372036850775807 | 56 |
|
+--------------+----------------------+----------------------+----------------------+----------------------+---------------------+--------------+
|
Observations:
- ColumnStore does not perform rounding
Datetime input
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1()
|
BEGIN
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DATETIME(1) NOT NULL);
|
INSERT INTO t1 VALUES ('2001-01-01 00:00:02.9');
|
INSERT INTO t1 VALUES ('2001-01-01 23:59:59.9');
|
INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9');
|
SELECT a, a & a, a | a, a^0, a<<1, a>>1, bit_count(a) FROM t1;
|
END;
|
$$
|
DELIMITER ;
|
SET @@default_storage_engine=MyISAM;
|
CALL p1;
|
SET @@default_storage_engine=ColumnStore;
|
CALL p1;
|
+-----------------------+----------------+----------------+----------------+-----------------+----------------+--------------+
|
| a | a & a | a | a | a^0 | a<<1 | a>>1 | bit_count(a) |
|
+-----------------------+----------------+----------------+----------------+-----------------+----------------+--------------+
|
| 2001-01-01 00:00:02.9 | 20010101000003 | 20010101000003 | 20010101000003 | 40020202000006 | 10005050500001 | 24 |
|
| 2001-01-01 23:59:59.9 | 20010101235960 | 20010101235960 | 20010101235960 | 40020202471920 | 10005050617980 | 25 |
|
| 9999-12-31 23:59:59.9 | 99991231235960 | 99991231235960 | 99991231235960 | 199982462471920 | 49995615617980 | 23 |
|
+-----------------------+----------------+----------------+----------------+-----------------+----------------+--------------+
|
+-----------------------+----------------+----------------+----------------+-----------------+----------------+--------------+
|
| a | a & a | a | a | a^0 | a<<1 | a>>1 | bit_count(a) |
|
+-----------------------+----------------+----------------+----------------+-----------------+----------------+--------------+
|
| 2001-01-01 00:00:02.9 | 20010101000002 | 20010101000002 | 20010101000002 | 40020202000004 | 10005050500001 | 23 |
|
| 2001-01-01 23:59:59.9 | 20010101235959 | 20010101235959 | 20010101235959 | 40020202471918 | 10005050617979 | 27 |
|
| 9999-12-31 23:59:59.9 | 99991231235959 | 99991231235959 | 99991231235959 | 199982462471918 | 49995615617979 | 25 |
|
+-----------------------+----------------+----------------+----------------+-----------------+----------------+--------------+
|
Observations:
- ColumnStore does not perform rounding
Bit shift for more than 63 bits
DELIMITER $$
|
CREATE OR REPLACE PROCEDURE p1()
|
BEGIN
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT NOT NULL);
|
INSERT INTO t1 VALUES (63),(64),(65);
|
SELECt a, 1<<a,1>>a FROM t1;
|
END;
|
$$
|
DELIMITER ;
|
SET @@default_storage_engine=MyISAM;
|
CALL p1;
|
SET @@default_storage_engine=ColumnStore;
|
CALL p1;
|
+----+---------------------+------+
|
| a | 1<<a | 1>>a |
|
+----+---------------------+------+
|
| 63 | 9223372036854775808 | 0 |
|
| 64 | 0 | 0 |
|
| 65 | 0 | 0 |
|
+----+---------------------+------+
|
+----+---------------------+------+
|
| a | 1<<a | 1>>a |
|
+----+---------------------+------+
|
| 63 | 9223372036854775808 | 0 |
|
| 64 | 1 | 1 |
|
| 65 | 2 | 0 |
|
+----+---------------------+------+
|
Observations:
- ColumnStore returns a non-zero result when shifting more than 63 bits
|