ydb/yql/essentials/tests/postgresql/cases/bit.out

--
-- BIT types
--
--
-- Build tables for testing
--
CREATE TABLE BIT_TABLE(b BIT(11));
INSERT INTO BIT_TABLE VALUES (B'00000000000');
INSERT INTO BIT_TABLE VALUES (B'11011000000');
INSERT INTO BIT_TABLE VALUES (B'01010101010');
CREATE TABLE VARBIT_TABLE(v BIT VARYING(11));
INSERT INTO VARBIT_TABLE VALUES (B'');
INSERT INTO VARBIT_TABLE VALUES (B'0');
INSERT INTO VARBIT_TABLE VALUES (B'010101');
INSERT INTO VARBIT_TABLE VALUES (B'01010101010');
-- test overflow cases
SELECT SUBSTRING('01010101'::bit(8) FROM 2 FOR 2147483646) AS "1010101";
 1010101 
---------
 1010101
(1 row)

SELECT SUBSTRING('01010101'::bit(8) FROM -10 FOR 2147483646) AS "01010101";
 01010101 
----------
 01010101
(1 row)

SELECT SUBSTRING('01010101'::bit(8) FROM -10 FOR -2147483646) AS "error";
ERROR:  negative substring length not allowed
SELECT SUBSTRING('01010101'::varbit FROM 2 FOR 2147483646) AS "1010101";
 1010101 
---------
 1010101
(1 row)

SELECT SUBSTRING('01010101'::varbit FROM -10 FOR 2147483646) AS "01010101";
 01010101 
----------
 01010101
(1 row)

SELECT SUBSTRING('01010101'::varbit FROM -10 FOR -2147483646) AS "error";
ERROR:  negative substring length not allowed
CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16));
DROP TABLE varbit_table;
CREATE TABLE bit_table (a BIT(16), b BIT(16));
DROP TABLE bit_table;
-- The following should fail
select B'001' & B'10';
ERROR:  cannot AND bit strings of different sizes
select B'0111' | B'011';
ERROR:  cannot OR bit strings of different sizes
select B'0010' # B'011101';
ERROR:  cannot XOR bit strings of different sizes
-- More position tests, checking all the boundary cases
SELECT POSITION(B'1010' IN B'0000101');   -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'1010' IN B'00001010');  -- 5
 position 
----------
        5
(1 row)

SELECT POSITION(B'1010' IN B'00000101');  -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'1010' IN B'000001010');  -- 6
 position 
----------
        6
(1 row)

SELECT POSITION(B'' IN B'00001010');  -- 1
 position 
----------
        1
(1 row)

SELECT POSITION(B'0' IN B'');  -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'' IN B'');  -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'101101' IN B'001011011011011000');  -- 3
 position 
----------
        3
(1 row)

SELECT POSITION(B'10110110' IN B'001011011011010');  -- 3
 position 
----------
        3
(1 row)

SELECT POSITION(B'1011011011011' IN B'001011011011011');  -- 3
 position 
----------
        3
(1 row)

SELECT POSITION(B'1011011011011' IN B'00001011011011011');  -- 5
 position 
----------
        5
(1 row)

SELECT POSITION(B'11101011' IN B'11101011'); -- 1
 position 
----------
        1
(1 row)

SELECT POSITION(B'11101011' IN B'011101011'); -- 2
 position 
----------
        2
(1 row)

SELECT POSITION(B'11101011' IN B'00011101011'); -- 4
 position 
----------
        4
(1 row)

SELECT POSITION(B'11101011' IN B'0000011101011'); -- 6
 position 
----------
        6
(1 row)

SELECT POSITION(B'111010110' IN B'111010110'); -- 1
 position 
----------
        1
(1 row)

SELECT POSITION(B'111010110' IN B'0111010110'); -- 2
 position 
----------
        2
(1 row)

SELECT POSITION(B'111010110' IN B'000111010110'); -- 4
 position 
----------
        4
(1 row)

SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6
 position 
----------
        6
(1 row)

SELECT POSITION(B'111010110' IN B'11101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'011101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'00011101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'0000011101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'111010110'); -- 1
 position 
----------
        1
(1 row)

SELECT POSITION(B'111010110' IN B'0111010110'); -- 2
 position 
----------
        2
(1 row)

SELECT POSITION(B'111010110' IN B'000111010110'); -- 4
 position 
----------
        4
(1 row)

SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6
 position 
----------
        6
(1 row)

SELECT POSITION(B'111010110' IN B'000001110101111101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'0000001110101111101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'000000001110101111101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'00000000001110101111101011'); -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'111010110' IN B'0000011101011111010110'); -- 14
 position 
----------
       14
(1 row)

SELECT POSITION(B'111010110' IN B'00000011101011111010110'); -- 15
 position 
----------
       15
(1 row)

SELECT POSITION(B'111010110' IN B'0000000011101011111010110'); -- 17
 position 
----------
       17
(1 row)

SELECT POSITION(B'111010110' IN B'000000000011101011111010110'); -- 19
 position 
----------
       19
(1 row)

SELECT POSITION(B'000000000011101011111010110' IN B'000000000011101011111010110'); -- 1
 position 
----------
        1
(1 row)

SELECT POSITION(B'00000000011101011111010110' IN B'000000000011101011111010110'); -- 2
 position 
----------
        2
(1 row)

SELECT POSITION(B'0000000000011101011111010110' IN B'000000000011101011111010110'); -- 0
 position 
----------
        0
(1 row)

-- Shifting
CREATE TABLE BIT_SHIFT_TABLE(b BIT(16));
INSERT INTO BIT_SHIFT_TABLE VALUES (B'1101100000000000');
INSERT INTO BIT_SHIFT_TABLE SELECT b>>1 FROM BIT_SHIFT_TABLE;
INSERT INTO BIT_SHIFT_TABLE SELECT b>>2 FROM BIT_SHIFT_TABLE;
INSERT INTO BIT_SHIFT_TABLE SELECT b>>4 FROM BIT_SHIFT_TABLE;
INSERT INTO BIT_SHIFT_TABLE SELECT b>>8 FROM BIT_SHIFT_TABLE;
SELECT b, b >> 1 AS bsr, b << 1 AS bsl
       FROM BIT_SHIFT_TABLE ;
        b         |       bsr        |       bsl        
------------------+------------------+------------------
 1101100000000000 | 0110110000000000 | 1011000000000000
 0110110000000000 | 0011011000000000 | 1101100000000000
 0011011000000000 | 0001101100000000 | 0110110000000000
 0001101100000000 | 0000110110000000 | 0011011000000000
 0000110110000000 | 0000011011000000 | 0001101100000000
 0000011011000000 | 0000001101100000 | 0000110110000000
 0000001101100000 | 0000000110110000 | 0000011011000000
 0000000110110000 | 0000000011011000 | 0000001101100000
 0000000011011000 | 0000000001101100 | 0000000110110000
 0000000001101100 | 0000000000110110 | 0000000011011000
 0000000000110110 | 0000000000011011 | 0000000001101100
 0000000000011011 | 0000000000001101 | 0000000000110110
 0000000000001101 | 0000000000000110 | 0000000000011010
 0000000000000110 | 0000000000000011 | 0000000000001100
 0000000000000011 | 0000000000000001 | 0000000000000110
 0000000000000001 | 0000000000000000 | 0000000000000010
(16 rows)

SELECT b, b >> 8 AS bsr8, b << 8 AS bsl8
       FROM BIT_SHIFT_TABLE ;
        b         |       bsr8       |       bsl8       
------------------+------------------+------------------
 1101100000000000 | 0000000011011000 | 0000000000000000
 0110110000000000 | 0000000001101100 | 0000000000000000
 0011011000000000 | 0000000000110110 | 0000000000000000
 0001101100000000 | 0000000000011011 | 0000000000000000
 0000110110000000 | 0000000000001101 | 1000000000000000
 0000011011000000 | 0000000000000110 | 1100000000000000
 0000001101100000 | 0000000000000011 | 0110000000000000
 0000000110110000 | 0000000000000001 | 1011000000000000
 0000000011011000 | 0000000000000000 | 1101100000000000
 0000000001101100 | 0000000000000000 | 0110110000000000
 0000000000110110 | 0000000000000000 | 0011011000000000
 0000000000011011 | 0000000000000000 | 0001101100000000
 0000000000001101 | 0000000000000000 | 0000110100000000
 0000000000000110 | 0000000000000000 | 0000011000000000
 0000000000000011 | 0000000000000000 | 0000001100000000
 0000000000000001 | 0000000000000000 | 0000000100000000
(16 rows)

SELECT b::bit(15), b::bit(15) >> 1 AS bsr, b::bit(15) << 1 AS bsl
       FROM BIT_SHIFT_TABLE ;
        b        |       bsr       |       bsl       
-----------------+-----------------+-----------------
 110110000000000 | 011011000000000 | 101100000000000
 011011000000000 | 001101100000000 | 110110000000000
 001101100000000 | 000110110000000 | 011011000000000
 000110110000000 | 000011011000000 | 001101100000000
 000011011000000 | 000001101100000 | 000110110000000
 000001101100000 | 000000110110000 | 000011011000000
 000000110110000 | 000000011011000 | 000001101100000
 000000011011000 | 000000001101100 | 000000110110000
 000000001101100 | 000000000110110 | 000000011011000
 000000000110110 | 000000000011011 | 000000001101100
 000000000011011 | 000000000001101 | 000000000110110
 000000000001101 | 000000000000110 | 000000000011010
 000000000000110 | 000000000000011 | 000000000001100
 000000000000011 | 000000000000001 | 000000000000110
 000000000000001 | 000000000000000 | 000000000000010
 000000000000000 | 000000000000000 | 000000000000000
(16 rows)

SELECT b::bit(15), b::bit(15) >> 8 AS bsr8, b::bit(15) << 8 AS bsl8
       FROM BIT_SHIFT_TABLE ;
        b        |      bsr8       |      bsl8       
-----------------+-----------------+-----------------
 110110000000000 | 000000001101100 | 000000000000000
 011011000000000 | 000000000110110 | 000000000000000
 001101100000000 | 000000000011011 | 000000000000000
 000110110000000 | 000000000001101 | 000000000000000
 000011011000000 | 000000000000110 | 100000000000000
 000001101100000 | 000000000000011 | 110000000000000
 000000110110000 | 000000000000001 | 011000000000000
 000000011011000 | 000000000000000 | 101100000000000
 000000001101100 | 000000000000000 | 110110000000000
 000000000110110 | 000000000000000 | 011011000000000
 000000000011011 | 000000000000000 | 001101100000000
 000000000001101 | 000000000000000 | 000110100000000
 000000000000110 | 000000000000000 | 000011000000000
 000000000000011 | 000000000000000 | 000001100000000
 000000000000001 | 000000000000000 | 000000100000000
 000000000000000 | 000000000000000 | 000000000000000
(16 rows)

CREATE TABLE VARBIT_SHIFT_TABLE(v BIT VARYING(20));
INSERT INTO VARBIT_SHIFT_TABLE VALUES (B'11011');
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0' AS BIT VARYING(6)) >>1 FROM VARBIT_SHIFT_TABLE;
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00' AS BIT VARYING(8)) >>2 FROM VARBIT_SHIFT_TABLE;
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0000' AS BIT VARYING(12)) >>4 FROM VARBIT_SHIFT_TABLE;
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00000000' AS BIT VARYING(20)) >>8 FROM VARBIT_SHIFT_TABLE;
SELECT v, v >> 1 AS vsr, v << 1 AS vsl
       FROM VARBIT_SHIFT_TABLE ;
          v           |         vsr          |         vsl          
----------------------+----------------------+----------------------
 11011                | 01101                | 10110
 011011               | 001101               | 110110
 0011011              | 0001101              | 0110110
 00011011             | 00001101             | 00110110
 000011011            | 000001101            | 000110110
 0000011011           | 0000001101           | 0000110110
 00000011011          | 00000001101          | 00000110110
 000000011011         | 000000001101         | 000000110110
 0000000011011        | 0000000001101        | 0000000110110
 00000000011011       | 00000000001101       | 00000000110110
 000000000011011      | 000000000001101      | 000000000110110
 0000000000011011     | 0000000000001101     | 0000000000110110
 00000000000011011    | 00000000000001101    | 00000000000110110
 000000000000011011   | 000000000000001101   | 000000000000110110
 0000000000000011011  | 0000000000000001101  | 0000000000000110110
 00000000000000011011 | 00000000000000001101 | 00000000000000110110
(16 rows)

SELECT v, v >> 8 AS vsr8, v << 8 AS vsl8
       FROM VARBIT_SHIFT_TABLE ;
          v           |         vsr8         |         vsl8         
----------------------+----------------------+----------------------
 11011                | 00000                | 00000
 011011               | 000000               | 000000
 0011011              | 0000000              | 0000000
 00011011             | 00000000             | 00000000
 000011011            | 000000000            | 100000000
 0000011011           | 0000000000           | 1100000000
 00000011011          | 00000000000          | 01100000000
 000000011011         | 000000000000         | 101100000000
 0000000011011        | 0000000000000        | 1101100000000
 00000000011011       | 00000000000000       | 01101100000000
 000000000011011      | 000000000000000      | 001101100000000
 0000000000011011     | 0000000000000000     | 0001101100000000
 00000000000011011    | 00000000000000000    | 00001101100000000
 000000000000011011   | 000000000000000000   | 000001101100000000
 0000000000000011011  | 0000000000000000000  | 0000001101100000000
 00000000000000011011 | 00000000000000000000 | 00000001101100000000
(16 rows)

DROP TABLE BIT_SHIFT_TABLE;
DROP TABLE VARBIT_SHIFT_TABLE;
-- Get/Set bit
SELECT get_bit(B'0101011000100', 10);
 get_bit 
---------
       1
(1 row)

SELECT set_bit(B'0101011000100100', 15, 1);
     set_bit      
------------------
 0101011000100101
(1 row)

SELECT set_bit(B'0101011000100100', 16, 1);	-- fail
ERROR:  bit index 16 out of valid range (0..15)
-- Overlay
SELECT overlay(B'0101011100' placing '001' from 2 for 3);
  overlay   
------------
 0001011100
(1 row)

SELECT overlay(B'0101011100' placing '101' from 6);
  overlay   
------------
 0101010100
(1 row)

SELECT overlay(B'0101011100' placing '001' from 11);
    overlay    
---------------
 0101011100001
(1 row)

SELECT overlay(B'0101011100' placing '001' from 20);
    overlay    
---------------
 0101011100001
(1 row)

-- bit_count
SELECT bit_count(B'0101011100'::bit(10));
 bit_count 
-----------
         5
(1 row)

SELECT bit_count(B'1111111111'::bit(10));
 bit_count 
-----------
        10
(1 row)

-- This table is intentionally left around to exercise pg_dump/pg_upgrade
CREATE TABLE bit_defaults(
  b1 bit(4) DEFAULT '1001',
  b2 bit(4) DEFAULT B'0101',
  b3 bit varying(5) DEFAULT '1001',
  b4 bit varying(5) DEFAULT B'0101'
);