[MCOL-4464] Bitwise operations not like in MariaDB - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.4.3
Fix Version/s: 6.1.1
Component/s: PrimProc
Labels:
None

Description

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

Attachments

Issue Links

causes

MCOL-4500 Bit functions processing throws internally trying to cast char into decimal representation

Closed

relates to

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

Closed

MCOL-4531 New string-to-decimal conversion implementation

Closed

MCOL-4666 Empty set when using BIT OR and BIT AND functions in WHERE

Closed

Activity

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2020-12-18 15:16

Updated:: 2021-04-05 13:35

Resolved:: 2021-01-11 16:30

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.