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

     1  -- test hex/unhex and to_base64/from_base64 function
     2  SELECT hex('\xa7');
     3  SELECT unhex('616263');
     4  
     5  SELECT hex('abc'), unhex('616263');
     6  SELECT to_base64('abc'), from_base64('YWJj');
     7  
     8  SELECT unhex('invalid');
     9  SELECT from_base64('invalid');
    10  
    11  SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
    12  SELECT HEX('abc'),UNHEX(HEX('abc'));
    13  
    14  select to_base64(''),to_base64(NULL);
    15  select to_base64('@#%#$^jfe12');
    16  select to_base64(123dokgr);
    17  select to_base64(-123);
    18  select to_base64(2003-09-06);
    19  select to_base64('2003-09-06');
    20  select to_base64('数据库');
    21  
    22  select from_base64(''),from_base64(NULL);
    23  select from_base64('@#%#$^jfe12');
    24  
    25  select from_base64(123dokgr);
    26  
    27  select from_base64(-123);
    28  
    29  select from_base64(2003-09-06);
    30  select from_base64('5pWw5o2u5bqT');
    31  select from_base64('MjAwMy0wOS0wNg==');
    32  create table test_base(c1 varchar(25));
    33  insert into test_base values(to_base64('blue')),(to_base64('232525')),(to_base64('lijfe23253'));
    34  select from_base64(c1) from test_base;
    35  
    36  -- test serial() and serial_full()
    37  CREATE TABLE t1 (name varchar(255), age int);
    38  INSERT INTO t1 (name, age) VALUES ('Abby', 24);
    39  INSERT INTO t1 (age) VALUES (25);
    40  INSERT INTO t1 (name, age) VALUES ('Carol', 23);
    41  SELECT * FROM t1;
    42  SELECT serial(name,age) from t1;
    43  SELECT serial_full(name,age) from t1;
    44  
    45  
    46  -- test serial_extract
    47  SELECT serial_extract(serial(1,2), 0 as bigint);
    48  SELECT serial_extract(serial(1,2), 1 as bigint);
    49  SELECT serial_extract(serial(1,2), 2 as bigint); -- error
    50  SELECT serial_extract(serial(1,"adam"), 1 as varchar(4));
    51  SELECT serial_extract(serial(1,"adam"), 1 as varchar(255));
    52  SELECT serial_extract(serial(1,cast("[1,2,3]" as vecf32(3))), 1 as vecf32(3));
    53  SELECT serial_extract(serial(cast(2.45 as float),cast(3 as bigint)), 0 as float);
    54  SELECT serial_extract(serial(cast(2.45 as float),cast(3 as bigint)), 1 as bigint);
    55  SELECT serial_extract(serial(NULL, cast(1 as bigint)), 1 as bigint); -- serial NULL
    56  SELECT serial_extract(serial_full(NULL, cast(1 as bigint)), 1 as bigint); -- serial_full
    57  SELECT serial_extract(serial_full(NULL, cast(1 as bigint)), 0 as varchar(1)); -- serial_full (data type doesn't matter for NULL)
    58  SELECT serial_extract(serial_full(NULL, 1), 1 as int); -- error
    59  SELECT serial_extract(serial_full(NULL, "adam"), 1 as varchar(4));
    60  -- a potential dangerous case. we don't validate the subtype of Varlena. Need to be careful!!!
    61  SELECT serial_extract(serial_full(NULL, "adam"), 1 as vecf32(4));
    62  
    63  
    64  -- test min
    65  CREATE TABLE t2 (name varchar(255), age int);
    66  INSERT INTO t2 (name, age) VALUES ('Abby', 24);
    67  INSERT INTO t2 (name,age) VALUES ('Alex',23);
    68  INSERT INTO t2 (name, age) VALUES ('Carol', 23);
    69  INSERT INTO t2 (age) VALUES (25);
    70  select name, age from t2 order by name asc,age asc;
    71  SELECT min( serial(t2.name, t2.age)) from t2;
    72  SELECT min( serial_full(t2.name,t2.age)) from t2;
    73  select  serial_extract(min, 0 as varchar(255)),  serial_extract(min, 1 as int) from (SELECT min( serial_full(t2.name,t2.age)) as min from t2);
    74  select age,name from t2 order by age asc,name asc;
    75  SELECT min( serial(t2.age,t2.name)) from t2;
    76  SELECT min( serial_full(t2.age,t2.name)) from t2;
    77  select  serial_extract(min, 0 as int),  serial_extract(min, 1 as varchar(255)) from (SELECT min( serial_full(t2.age,t2.name)) as min from t2);
    78  
    79  -- test max
    80  select name, age from t2 order by name desc,age desc;
    81  SELECT max( serial(t2.name, t2.age)) from t2;
    82  SELECT max( serial_full(t2.name,t2.age)) from t2;
    83  select  serial_extract(max, 0 as varchar(255)),  serial_extract(max, 1 as int) from (SELECT max( serial_full(t2.name,t2.age)) as max from t2);
    84  select age,name from t2 order by age desc,name desc;
    85  SELECT max( serial(t2.age,t2.name)) from t2;
    86  SELECT max( serial_full(t2.age,t2.name)) from t2;
    87  select  serial_extract(max, 0 as int),  serial_extract(max, 1 as varchar(255)) from (SELECT max( serial_full(t2.age,t2.name)) as max from t2);
    88  
    89  -- test function serial()、serial_full()、max() and min()
    90  drop table if exists test01;
    91  create table test01 (col1 bigint, col2 varchar(10), col3 char);
    92  insert into test01 values (1392034, 'database', 'a');
    93  insert into test01 values (23849242, 'abcdai', 'b');
    94  insert into test01 values (-32934, 'mo', 'c');
    95  insert into test01 values (null, null, null);
    96  select * from test01;
    97  select serial(col1, col2) from test01;
    98  select serial(col1, col2, col3) from test01;
    99  select serial_full(col1, col2, col3) from test01;
   100  select serial_full(col1, col3) from test01;
   101  select max(serial(col1, col2)) from test01;
   102  select max(serial(col1, col2, col3)) from test01;
   103  select min(serial_full(col1, col2, col3)) from test01;
   104  select min(serial_full(col1, col3)) from test01;
   105  drop table test01;
   106  
   107  drop table if exists test02;
   108  create table test02 (col1 int, col2 decimal, col3 char);
   109  insert into test02 values (1,2,'a');
   110  insert into test02 values (2,3,'b');
   111  insert into test02 values (null, null, null);
   112  select * from test02;
   113  select serial_extract(max(serial(col1, col2, col3)), 1 as decimal) from test02;
   114  select serial_extract(min(serial(col1, col2, col3)), 1 as decimal) from test02;
   115  select serial_extract(max(serial_full(cast(col1 as decimal), cast(col2 as double))), 0 as decimal) from test02;
   116  select serial_extract(min(serial_full(cast(col1 as decimal), cast(col2 as double))), 0 as decimal) from test02;
   117  drop table test02;
   118  
   119  drop table if exists vtab64;
   120  create table vtab64(id int primary key auto_increment,`vecf64_3` vecf64(3),`vecf64_5` vecf64(5));
   121  insert into vtab64(vecf64_3,vecf64_5) values("[0.8166459,NULL,0.4886152]",NULL);
   122  insert into vtab64(vecf64_3,vecf64_5) values(NULL,NULL);
   123  insert into vtab64(vecf64_3,vecf64_5) values("[0.8166459,0.66616553,0.4886152]",NULL);
   124  insert into vtab64(vecf64_3,vecf64_5) values ("[8.5606893,6.7903588,821.977768]","[0.46323407,23.49801546,563.9229458,56.07673508,8732.9583881]");
   125  insert into vtab64(vecf64_3,vecf64_5) values ("[0.9260021,0.26637346,0.06567037]","[0.45756745,65.2996871,321.623636,3.60082066,87.58445764]");
   126  select * from vtab64;
   127  select serial_extract(max(serial(id, `vecf64_3`, `vecf64_5`)), 1 as vecf64(3)) from vtab64;
   128  select serial_extract(min(serial(id, `vecf64_3`, `vecf64_5`)), 1 as vecf64(3)) from vtab64;
   129  select serial_extract(max(serial_full(cast(id as decimal), `vecf64_3`)), 0 as decimal) from vtab64;
   130  select serial_extract(min(serial_full(cast(id as decimal), `vecf64_3`)), 1 as vecf64(3)) from vtab64;
   131  drop table vtab64;