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

     1  -- @suit
     2  
     3  -- @case
     4  -- @desc:test for LIKE operator
     5  -- @label:bvt
     6  
     7  SELECT 'a' LIKE 'ae';
     8  SELECT 'ae' LIKE 'a';
     9  SELECT 'MYSQL' LIKE 'mysql';
    10  SELECT 'David!' LIKE 'David_';
    11  SELECT 'David!' LIKE '%D%v%';
    12  SELECT 'David!' LIKE 'David\_';
    13  SELECT 'David_' LIKE 'David\_';
    14  
    15  DROP TABLE IF EXISTS like_test;
    16  CREATE TABLE like_test(
    17      str1 VARCHAR(50)
    18  );
    19  
    20  INSERT INTO like_test VALUES('D:');
    21  INSERT INTO like_test VALUES('D:\\');
    22  INSERT INTO like_test VALUES('D:\\System_files');
    23  INSERT INTO like_test VALUES('D:\\System_files\\');
    24  INSERT INTO like_test VALUES(NULL);
    25  INSERT INTO like_test VALUES('NULL');
    26  INSERT INTO like_test VALUES('  A');
    27  INSERT INTO like_test VALUES('s _');
    28  
    29  SELECT * FROM like_test WHERE str1 LIKE '%:';
    30  SELECT * FROM like_test WHERE str1 LIKE '%\_%';
    31  SELECT * FROM like_test WHERE str1 LIKE '___';
    32  SELECT * FROM like_test WHERE str1 LIKE ' %';
    33  SELECT * FROM like_test WHERE str1 LIKE NULL;
    34  SELECT * FROM like_test WHERE str1 LIKE '_:%';
    35  SELECT * FROM like_test WHERE str1 LIKE 's__';
    36  SELECT * FROM like_test WHERE str1 LIKE '%s';
    37  SELECT * FROM like_test WHERE str1 LIKE 'd%';
    38  SELECT * FROM like_test WHERE str1 LIKE '%%';
    39  SELECT * FROM like_test WHERE str1 IS NULL;
    40  SELECT * FROM like_test WHERE str1 LIKE '%U%';
    41  SELECT * FROM like_test WHERE str1 LIKE '%:%' AND str1 LIKE '%s%';
    42  SELECT * FROM like_test WHERE str1 LIKE '' AND str1 LIKE 's%';
    43  SELECT * FROM like_test WHERE str1 LIKE '% %' AND str1 LIKE '%N%';
    44  SELECT * FROM like_test WHERE str1 LIKE '%L' OR str1 LIKE '%s%';
    45  SELECT * FROM like_test WHERE str1 LIKE '% %' OR str1 LIKE '%N%';
    46  SELECT * FROM (SELECT * FROM like_test WHERE str1 LIKE 'D%') AS a WHERE LENGTH(str1) > 4;
    47  SELECT str1 FROM (SELECT * FROM like_test WHERE str1 LIKE '_:%') AS T WHERE str1 LIKE '%S%';
    48  SELECT * FROM like_test WHERE str1 NOT LIKE '___';
    49  SELECT * FROM like_test WHERE str1 NOT LIKE 'D%';
    50  SELECT * FROM like_test WHERE str1 NOT LIKE '%\_%';
    51  SELECT * FROM like_test WHERE str1 NOT LIKE 'NULL';
    52  SELECT * FROM like_test WHERE str1 NOT LIKE NULL;
    53  SELECT * FROM like_test WHERE str1 NOT LIKE '';
    54  SELECT COUNT(*) FROM like_test WHERE str1 LIKE '%baz%';
    55  SELECT COUNT(*) FROM like_test WHERE str1 NOT LIKE '%baz%';
    56  SELECT COUNT(*) FROM like_test WHERE str1 NOT LIKE '%baz%' OR str1 IS NULL;
    57  SELECT str1, str1 LIKE '%\\' FROM like_test;
    58  
    59  -- @bvt:issue#5078
    60  SELECT str1, str1 LIKE '%\\\\' FROM like_test;
    61  -- @bvt:issue
    62  
    63  DELETE FROM like_test;
    64  INSERT INTO like_test VALUES('99.9');
    65  INSERT INTO like_test VALUES('88');
    66  INSERT INTO like_test VALUES('89.88');
    67  INSERT INTO like_test VALUES(19.88);
    68  INSERT INTO like_test VALUES(2887.08);
    69  
    70  SELECT * FROM like_test WHERE str1 LIKE '9%';
    71  SELECT * FROM like_test WHERE str1 LIKE '%9';
    72  SELECT * FROM like_test WHERE str1 LIKE '%88';
    73  SELECT * FROM like_test WHERE str1 NOT LIKE '%88';
    74  
    75  DELETE FROM like_test;
    76  INSERT INTO like_test VALUES('ABC1_23D');
    77  INSERT INTO like_test VALUES('123ABCD\\');
    78  INSERT INTO like_test VALUES('ABCD\\123');
    79  INSERT INTO like_test VALUES(' ');
    80  
    81  -- Same Problem with bvt:issue#5078
    82  SELECT * FROM like_test WHERE str1 LIKE '%D\\%';
    83  SELECT * FROM like_test WHERE str1 LIKE '% ';
    84  SELECT * FROM like_test WHERE str1 LIKE '%3_';
    85  
    86  DROP TABLE IF EXISTS like_test;
    87  CREATE TABLE like_test(
    88      str1 VARCHAR(50),
    89      str2 CHAR(50)
    90  );
    91  
    92  INSERT INTO like_test(str1, str2) VALUES('CHINA IS OUR HOMELAND', 'BEIJING IS THE CAPITAL');
    93  INSERT INTO like_test(str1, str2) VALUES('DHINA IS OUR HOMELAN ', 'THIS MAN PUSH HIM');
    94  INSERT INTO like_test(str1, str2) VALUES(' HINA IS OUR HOMELAND%', 'HE LIKE WHORE');
    95  INSERT INTO like_test(str1, str2) VALUES('THE UNITED OF AMERICAN_', 'NORTH AMERICA');
    96  INSERT INTO like_test(str1, str2) VALUES('AB%CDE', 'PDFXLXDOC');
    97  INSERT INTO like_test(str1, str2) VALUES('THE UNITED OF ENGLAND\\', "KINGDOM'");
    98  INSERT INTO like_test(str1, str2) VALUES(' ', '');
    99  INSERT INTO like_test(str1, str2) VALUES('', NULL);
   100  INSERT INTO like_test(str1, str2) VALUES('法的萨菲厄张三三三三三', '考虑理论可看见年年年年年年');
   101  
   102  SELECT NULL LIKE 'ABC', 'ABC' LIKE NULL;
   103  SELECT * FROM like_test WHERE str1 LIKE 'C%';
   104  SELECT * FROM like_test WHERE str1 LIKE '% ';
   105  SELECT * FROM like_test WHERE str1 LIKE '%s%';
   106  SELECT * FROM like_test WHERE str1 LIKE '%C%' AND str1 LIKE '%D%';
   107  SELECT * FROM like_test WHERE str2 LIKE '__I%';
   108  SELECT * FROM like_test WHERE str2 LIKE '__ %';
   109  SELECT * FROM like_test WHERE str2 LIKE '';
   110  SELECT * FROM like_test WHERE str2 LIKE NULL;
   111  SELECT * FROM like_test WHERE str1 LIKE '%\\';
   112  SELECT * FROM like_test WHERE str2 LIKE '%\'';
   113  SELECT * FROM like_test WHERE str1 LIKE '%三__';
   114  
   115  -- @bvt:issue#5056
   116  SELECT * FROM like_test WHERE str1 LIKE '%\%';
   117  -- @bvt:issue
   118  
   119  -- NOT LIKE
   120  SELECT * FROM like_test WHERE str1 NOT LIKE 'D%';
   121  SELECT * FROM like_test WHERE str1 NOT LIKE '%E';
   122  DELETE FROM like_test;
   123  DROP TABLE like_test;
   124  
   125  DROP TABLE IF EXISTS chinese_test;
   126  CREATE TABLE chinese_test(
   127      name VARCHAR(50),
   128      home VARCHAR(100),
   129      job VARCHAR(20)
   130  );
   131  INSERT INTO chinese_test VALUES('张三', '河南省信阳市桥东区广平小区', '软件工程师');
   132  INSERT INTO chinese_test VALUES('张飞', '北京市朝阳区天宫苑桥东街道', '教师');
   133  INSERT INTO chinese_test VALUES('里斯', '广东省广州村', '学生教师');
   134  INSERT INTO chinese_test VALUES('刘五', '%%%江苏省南京市金区陵新村', '%%工人');
   135  SELECT name FROM chinese_test WHERE home LIKE '%桥东%';
   136  SELECT * FROM chinese_test WHERE name LIKE '张%';
   137  SELECT * FROM (SELECT * FROM chinese_test WHERE name LIKE '张%') AS a WHERE home LIKE '%广%';
   138  SELECT * FROm (SELECT * FROM chinese_test WHERE job LIKE '%师') AS a WHERE home LIKE '%市%';
   139  SELECT name FROM chinese_test WHERE home LIKE '\%%' AND job LIKE '\%%';
   140  SELECT * FROM chinese_test WHERE home LIKE '%省%区';
   141  DROP TABLE chinese_test;
   142  
   143  CREATE TABLE stu(
   144      id INT PRIMARY KEY,
   145      name VARCHAR(20),
   146      class INT,
   147      sex VARCHAR(2),
   148      address VARCHAR(50)
   149  );
   150  
   151  CREATE TABLE score(
   152      id INT PRIMARY KEY,
   153      name VARCHAR(20),
   154      chinese FLOAT,
   155      math FLOAT,
   156      english FLOAT
   157  );
   158  
   159  INSERT INTO stu VALUES('1001', 'JACK', 7, 'M', 'US');
   160  INSERT INTO stu VALUES('1002', 'TOM', 7, '', 'UK');
   161  INSERT INTO stu VALUES('1005', 'ROMMY', 8, 'F', 'CHINA');
   162  INSERT INTO stu VALUES('1006', 'RACK', 8, 'M', 'ITALY');
   163  INSERT INTO score VALUES('1001', 'JACK', 89.5, 45.2, 67.5);
   164  INSERT INTO score VALUES('1002', 'TOM', 58.5, 76, 78);
   165  INSERT INTO score VALUES('1005', 'ROMMY', 68, 55, 69);
   166  
   167  SELECT * FROM stu INNER JOIN score ON stu.id = score.id WHERE stu.name LIKE '_O%';
   168  SELECT * FROM stu INNER JOIN score ON stu.id = score.id WHERE score.math > 60 AND stu.address LIKE 'U%_';
   169  SELECT * FROM stu RIGHT JOIN score ON stu.id = score.id WHERE score.english > 65 AND stu.sex LIKE '%';
   170  SELECT * FROM stu WHERE address LIKE '%A%' AND name LIKE '%R%';
   171  SELECT * FROM stu INNER JOIN score ON stu.id = score.id WHERE stu.address NOT LIKE 'CH%';
   172  SELECT * FROM stu WHERE name NOT LIKE '%A%';
   173  
   174  -- LIKE with SUBQUERY
   175  SELECT
   176      stu.id, stu.name, stu.address, a.chinese, a.english
   177  FROM
   178      (SELECT * FROM score WHERE math < 60 AND name LIKE '%M__') AS a,stu
   179  WHERE
   180      a.id = stu.id AND stu.address LIKE '%H%';
   181  
   182  -- LIKE with SUBSTRING and SUBQUERY
   183  SELECT
   184      stu.name, stu.address
   185  FROM
   186      (SELECT * FROm score WHERE chinese BETWEEN 60 AND 70) AS a, stu
   187  WHERE
   188      a.id = stu.id AND address LIKE SUBSTRING('THE CHINA',5);
   189  
   190  -- LIKE with JOIN
   191  SELECT
   192      stu.id, stu.name, stu.address, score.english
   193  FROM
   194      stu
   195  INNER JOIN
   196      score
   197  ON
   198      stu.id = score.id
   199  WHERE stu.sex NOT LIKE '';
   200  
   201  DROP TABLE IF EXISTS stu;
   202  DROP TABLE IF EXISTS score;
   203  
   204  create table t1(a tinyint, b smallint, c int, d bigint);
   205  insert into t1 values(121, 121, 121, 121);
   206  select * from t1 where (a like '%2%' and b like '%2%' and c like '%2%' and d like '%2%');
   207  
   208  drop table t1;
   209  create table t1(a tinyint unsigned, b smallint unsigned, c int unsigned, d bigint unsigned);
   210  insert into t1 values(121, 121, 121, 121);
   211  select * from t1 where (a like '%2%' and b like '%2%' and c like '%2%' and d like '%2%');
   212  
   213  drop table t1;
   214  create table t1(a text);
   215  insert into t1 values(rpad('1',50000,'1') + rpad('1',50000,'1'));
   216  select * from t1 where a like ".";
   217  
   218  drop table t1;
   219  create table t1(a varchar(20));
   220  insert into t1 values ('abc'), ('ABC'), ('abC');
   221  select * from t1 where a ilike '%abC%';
   222  show variables where value ilike "%system%" and variable_name = "time_zone";