github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_bit.sql (about)

     1  -- @suit
     2  -- @case
     3  -- @desc:test for bit_and(), bit_or(), bit_xor() in x86 or ARM
     4  -- @label:bvt
     5  
     6  SELECT BIT_AND(0), BIT_OR(0), BIT_XOR(0);
     7  SELECT BIT_AND(1), BIT_OR(1), BIT_XOR(1);
     8  SELECT BIT_AND(-1), BIT_OR(-1), BIT_XOR(-1);
     9  SELECT BIT_AND(ABS(-1)), BIT_OR(ABS(-1)), BIT_XOR(ABS(-1));
    10  SELECT BIT_AND(1 + 1), BIT_OR(1 + 1), BIT_XOR(1 + 1);
    11  SELECT BIT_AND(COS(0)), BIT_OR(COS(0)), BIT_XOR(COS(0));
    12  
    13  DROP TABLE IF EXISTS t;
    14  CREATE TABLE t(
    15  	tiny TINYINT NOT NULL,
    16  	small SMALLINT NOT NULL,
    17  	int_t INT NOT NULL,
    18  	big BIGINT NOT NULL
    19  );
    20  INSERT INTO t VALUES (-128, -32768, -2147483648, -9223372036854775808);
    21  INSERT INTO t VALUES (127, 32767, 2147483647, 9223372036854775807);
    22  SELECT BIT_AND(tiny), BIT_OR(tiny), BIT_XOR(tiny) FROM t;
    23  SELECT BIT_AND(small), BIT_OR(small), BIT_XOR(small) FROM t;
    24  SELECT BIT_AND(int_t), BIT_OR(int_t), BIT_XOR(int_t) FROM t;
    25  SELECT BIT_AND(big), BIT_OR(big), BIT_XOR(big) FROM t;
    26  
    27  DROP TABLE IF EXISTS t;
    28  CREATE TABLE t(
    29  	tiny TINYINT UNSIGNED NOT NULL,
    30  	small SMALLINT UNSIGNED NOT NULL,
    31  	int_t INT UNSIGNED NOT NULL,
    32  	big BIGINT UNSIGNED NOT NULL
    33  );
    34  INSERT INTO t VALUES (255, 65535, 65535, 18446744073709551615);
    35  SELECT BIT_AND(tiny), BIT_OR(tiny), BIT_XOR(tiny) FROM t;
    36  SELECT BIT_AND(small), BIT_OR(small), BIT_XOR(small) FROM t;
    37  SELECT BIT_AND(int_t), BIT_OR(int_t), BIT_XOR(int_t) FROM t;
    38  SELECT BIT_AND(big), BIT_OR(big), BIT_XOR(big) FROM t;
    39  
    40  DROP TABLE IF EXISTS t;
    41  CREATE TABLE t(
    42  	tiny TINYINT NOT NULL,
    43  	small SMALLINT NOT NULL,
    44  	int_t INT NOT NULL,
    45  	big BIGINT NOT NULL
    46  );
    47  INSERT INTO t VALUES (0, 1, -1, 1-0);
    48  INSERT INTO t VALUES (1, 9, 66666, TRUE);
    49  SELECT BIT_AND(tiny + small), BIT_AND(int_t + big) FROM t;
    50  SELECT BIT_AND(tiny + SIN(0)), BIT_XOR(int_t + TAN(0)) FROM t;
    51  SELECT BIT_OR(tiny + small), BIT_XOR(int_t + big) FROM t;
    52  SELECT BIT_XOR(tiny + SIN(0)), BIT_OR(int_t + big) FROM t;
    53  
    54  -- @bvt:issue#5638
    55  SELECT BIT_AND(tiny + 1), BIT_OR(small - 1), BIT_XOR(int_t / 0) FROM t;
    56  -- @bvt:issue
    57  
    58  -- JOIN
    59  DROP TABLE IF EXISTS t;
    60  DROP TABLE IF EXISTS t1;
    61  CREATE TABLE t(
    62  	id INT,
    63  	score FLOAT,
    64  	PRIMARY KEY (id)
    65  );
    66  CREATE TABLE t1(
    67  	id INT,
    68  	salary DOUBLE,
    69  	PRIMARY KEY (id)
    70  );
    71  INSERT INTO t VALUES (1, 67.5), (2, 87.5), (3, 72.5), (4, 90.1), (5, 81.0);
    72  INSERT INTO t1 VALUES (1, 1120.2), (2, 4320), (3, 9078), (4, 3071);
    73  SELECT BIT_AND(score) FROM t JOIN t1 ON t.id = t1.id WHERE t.id > 1;
    74  SELECT BIT_AND(salary) FROM t LEFT JOIN t1 ON t.id = t1.id;
    75  SELECT BIT_OR(salary) FROM t LEFT JOIN t1 ON t.id = t1.id WHERE t.id BETWEEN 2 AND 5;
    76  SELECT BIT_OR(score) FROM t RIGHT JOIN t1 ON t.id = t1.id;
    77  SELECT BIT_XOR(salary) FROM t RIGHT JOIN t1 ON t.id = t1.id;
    78  
    79  -- type of VARCHAR
    80  DROP TABLE IF EXISTS t;
    81  CREATE TABLE t(
    82  	id INT,
    83  	str1 VARCHAR(20),
    84  	PRIMARY KEY (id)
    85  );
    86  INSERT INTO t VALUES (1,'123'), (2,'234'), (3,'345'), (4,'456');
    87  SELECT BIT_OR(CAST(str1 AS FLOAT)) FROM t;
    88  SELECT BIT_XOR(CAST(str1 AS DOUBLE)) FROM t;
    89  
    90  -- type of VARCHAR, CHAR
    91  DROP TABLE IF EXISTS t;
    92  DROP TABLE IF EXISTS t1;
    93  CREATE TABLE t(
    94  	id INT,
    95  	str1 VARCHAR(50),
    96  	str2 CHAR(50),
    97  	PRIMARY KEY (id)
    98  );
    99  CREATE TABLE t1(
   100  	id INT,
   101  	str1 VARCHAR(50),
   102  	str2 CHAR(50),
   103  	PRIMARY KEY (id)
   104  );
   105  
   106  INSERT INTO t VALUES (1,'12.3','9.00200001'), (2,'0','1'), (3,'0.000000000000111101010101','189'), (4,'456','1.001');
   107  INSERT INTO t1 VALUES (1,'00000.01','3.001'), (2,'-1','99999999999999999'), (3,'1.0111','0.00001'), (4,'1.01','000.001');
   108  SELECT BIT_AND(CAST(t.str1 AS FLOAT)), BIT_AND(CAST(t1.str2 AS DOUBLE)) FROM t,t1;
   109  SELECT BIT_AND(CAST(t.str2 AS FLOAT)), BIT_OR(CAST(t1.str1 AS DOUBLE)) FROM t,t1;
   110  SELECT BIT_XOR(CAST(t.str1 AS FLOAT)), BIT_AND(CAST(t1.str2 AS FLOAT)) FROM t JOIN t1 ON t.id = t1.id;
   111  SELECT BIT_XOR(CAST(t.str1 AS FLOAT)), BIT_AND(CAST(t.str2 AS FLOAT)), BIT_OR(CAST(t1.str1 AS FLOAT)) FROM t JOIN t1 ON t.id = t1.id;
   112  SELECT BIT_XOR(CAST(t.str1 AS DOUBLE)), BIT_XOR(CAST(t1.str2 AS FLOAT)) FROM t RIGHT JOIN t1 ON t.id = t1.id;
   113  
   114  -- type of DECIMAL, no decimal point and no decimal portion
   115  DROP TABLE IF EXISTS t;
   116  CREATE TABLE t(
   117  	id INT,
   118  	d1 DECIMAL(28,20),
   119  	PRIMARY KEY(id)
   120  );
   121  INSERT INTO t VALUES (1,12.300101010100), (2,34.557978974325555),(3,56.),(4,0.000043524389789999);
   122  SELECT * FROM t;
   123  SELECT BIT_AND(d1) FROM t;
   124  
   125  -- type of DECIMAL
   126  DROP TABLE IF EXISTS t;
   127  CREATE TABLE t(
   128  	id INT,
   129  	d1 DECIMAL,
   130  	PRIMARY KEY(id)
   131  );
   132  INSERT INTO t VALUES (1,12.300101010100), (2,34.557978974325555),(3,56.),(4,0.000043524389789999);
   133  SELECT * FROM t;
   134  SELECT BIT_AND(d1), BIT_OR(d1), BIT_XOR(d1) FROM t;