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

     1  -- @suit
     2  
     3  -- @case
     4  -- @desc:test for FIELD() function
     5  -- @label:bvt
     6  
     7  select field('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
     8  select field('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
     9  select field('aa', 'AA', 'BB','Aa', 'aA');
    10  select field(' ', 'a', ' ', '\t', '\n');
    11  select field('', ' ', NULL, '\r', '\n');
    12  select field('', '', '\r', '\n');
    13  
    14  
    15  select field(1, '1', 1, 'true');
    16  
    17  
    18  select field(1, 1, 2, 3-2);
    19  select field(1, 3-2, 2, 1);
    20  select field(1, 1.0, 2, 1);
    21  select field(1+1, 1, 2, 3, 1+1);
    22  
    23  drop table if exists t;
    24  create table t(
    25      i int,
    26      f float,
    27      d double
    28  );
    29  insert into t() values (1, 1.1, 2.2), (2, 3.3, 4.4), (0, 0, 0), (0, null, 0);
    30  select * from t;
    31  select field(1, i, f, d) from t;
    32  select field(i, 0, 1, 2) from t;
    33  select field(i, f, d, 0, 1, 2) from t;
    34  select field(null, f, d, 0, 1, 2) from t;
    35  select field('1', f, d, 0, 1, 2) from t;
    36  select field(3.3, f, d, 0, 1, 2) from t;
    37  select field(3, f, d, 0, 1, 2) from t;
    38  
    39  drop table if exists t;
    40  create table t(
    41      str1 char(20),
    42      str2 char(20)
    43  );
    44  insert into t values ('hello','world'), ('jaja','haha'), ('didi','dodo'), ('papa','gaga');
    45  select field(str1, str2) from t;
    46  select field(str2, str1) from t;
    47  select field(str2, str1, NULL) from t;
    48  
    49  drop table if exists t;
    50  create table t(
    51      str1 varchar(50),
    52      str2 varchar(50),
    53      str3 varchar(50),
    54      str4 varchar(50)
    55  );
    56  insert into t values ('&*()&DJHKSY&F', 'JHKHJD21k..fdai', 'kl;ji*(', '86168907()*&*fd');
    57  insert into t values ('&*()&DJHKSY&F', 'JHKHJD21k..fdaiJHKHJD21k..fdai', 'kl;ji*(', '86168907()*&*fd');
    58  select field(str1, str2, str3, str4) from t;
    59  select field('1', str1, str2) from t;
    60  select field('&*()&DJHKSY&F', str1, str2) from t;
    61  select field('&*()&DJHKSY&F', str1, str2, str3, str4) from t;
    62  select field('', str1, str2, str3, str4) from t;
    63  
    64  drop table if exists t1;
    65  drop table if exists t2;
    66  create table t1(
    67      str1 varchar(50),
    68      str2 varchar(50)
    69  );
    70  create table t2(
    71      str1 varchar(50),
    72      str2 varchar(50)
    73  );
    74  insert into t1 values ('',' '), ('aa', 'Aa'), ('null',null);
    75  insert into t2 values ('','\r'), ('aa', 'AA'), (null, 'null');
    76  select field(t1.str1, t2.str1) from t1 join t2 on t1.str1 = t2.str1;
    77  select field(t1.str2, t2.str2) from t1 join t2 on t1.str1 = t2.str1;
    78  select field(t1.str1, t2.str1) from t1 left join t2 on t1.str1 = t2.str1;
    79  select field(t1.str1, t2.str1) from t1 right join t2 on t1.str1 = t2.str1;
    80  
    81  drop table if exists t1;
    82  drop table if exists t2;
    83  create table t1(
    84      str1 char(50),
    85      str2 char(50),
    86      primary key (str1)
    87  );
    88  create table t2(
    89      str1 char(50),
    90      str2 char(50),
    91      primary key (str1)
    92  );
    93  insert into t1 values ('',' '), ('aa', 'Aa'), ('null',NULL);
    94  insert into t2 values ('','\r'), ('aa', 'AA'), ('null', '');
    95  select field(t1.str1, t2.str1) from t1 inner join t2 on t1.str1 = t2.str1;
    96  select field(null, '');
    97  
    98  
    99  select field(t1.str2, t2.str2) from t1 join t2 on t1.str1 = t2.str1;
   100  
   101  
   102  drop table if exists t1;
   103  drop table if exists t2;
   104  create table t1(
   105      i int,
   106      f float,
   107      d double,
   108      primary key (i)
   109  );
   110  create table t2(
   111      i int,
   112      f float,
   113      d double,
   114      primary key (i)
   115  );
   116  insert into t1 values (9999999, 999.999, 888.888), (0, 0.0, 0.00);
   117  insert into t2 values (9999999, 999.999, 888.888), (0, 0, 0);
   118  select field(t1.i, t2.i) from t1 inner join t2 on t1.i = t2.i;
   119  select field(t1.d, t2.d) from t1 left join t2 on t1.d = t2.d;
   120  select field(t1.f, t2.f) from t1 right join t2 on t1.f = t2.f;
   121  select field(t1.f, t2.d) from t1 right join t2 on t1.f = t2.f;
   122  select field(t1.i, t2.f) from t1 right join t2 on t1.f = t2.f;
   123  
   124  drop table if exists t1;
   125  drop table if exists t2;
   126  create table t1(
   127      i double,
   128      f decimal(6,3),
   129      primary key (i)
   130  );
   131  create table t2(
   132      i double,
   133      f decimal(6,3),
   134      primary key (i)
   135  );
   136  insert into t1 values (0.01, 0.001), (0.0, -1), (-0.000000001, 1);
   137  insert into t2 values (0.01, 0.01), (-1.0, -1), (0.000000001, -1);
   138  select field(t1.i, t2.i) from t1 inner join t2 on t1.i = t2.i;
   139  select t2.f, t1.f, field(t2.f, t1.f) from t1 right join t2 on t1.i = t2.i;
   140  
   141  
   142  select t1.i, t2.f, field(t1.i, t2.f) from t1 left join t2 on t1.i = t2.i;
   143