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

     1  -- @suit
     2  
     3  -- @case
     4  -- @desc:test for is operator in select where
     5  -- @label:bvt
     6  drop table if exists tbl_int;
     7  CREATE TABLE tbl_int (col1 INT);
     8  INSERT INTO tbl_int VALUES (1), (2), (3), (4), (5), (6), (7), (8), (NULL), (NULL);
     9  SELECT * FROM tbl_int WHERE col1 IS NULL;
    10  SELECT * FROM tbl_int WHERE col1 IS NOT NULL;
    11  drop table if exists tbl_int;
    12  
    13  drop table if exists tbl_double;
    14  CREATE TABLE tbl_double (col1 DOUBLE);
    15  INSERT INTO tbl_double VALUES (-1.1), (0.0), (1.1), (2.2), (3.3), (4.4), (5.5), (6.6), (NULL), (NULL);
    16  SELECT * FROM tbl_double WHERE col1 IS NULL;
    17  SELECT * FROM tbl_double WHERE col1 IS NOT NULL;
    18  drop table if exists tbl_double;
    19  
    20  drop table if exists tbl_datetime;
    21  CREATE TABLE tbl_datetime (col1 DATETIME(6));
    22  INSERT INTO tbl_datetime VALUES
    23    ("1000-01-01 00:00:01"), ("9999-12-31 23:59:59.999998"),
    24    ("2017-01-01 00:00:00"), ("2017-01-01 00:00:00.000001"),
    25    ("2017-02-01 00:00:00"), ("2018-01-01 00:00:00.999999"),
    26    ("2018-01-01 00:00:01"), ("3019-01-01 10:10:10.101010"), (NULL), (NULL);
    27  SELECT * FROM tbl_datetime WHERE col1 IS NULL;
    28  SELECT * FROM tbl_datetime WHERE col1 IS NOT NULL;
    29  drop table if exists tbl_datetime;
    30  
    31  drop table if exists tbl_decimal;
    32  CREATE TABLE tbl_decimal (col1 DECIMAL(65, 30));
    33  drop table if exists tbl_decimal;
    34  
    35  drop table if exists t1;
    36  create table t1 (id int not null, str char(10));
    37  insert into t1 values (1, null),(2, null),(3, "foo"),(4, "bar");
    38  select * from t1 where str is null;
    39  select * from t1 where str is not null;
    40  drop table if exists t1;
    41  
    42  drop table if exists t1;
    43  drop table if exists t2;
    44  create table t1 (dt datetime not null, t datetime not null);
    45  create table t2 (dt datetime not null);
    46  
    47  insert into t1 values ('2001-01-01 1:1:1', '2001-01-01 1:1:1'),
    48  ('2001-01-01 1:1:1', '2001-01-01 1:1:1');
    49  insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1');
    50  SELECT outr.dt FROM t1 AS outr WHERE outr.dt IN (SELECT innr.dt FROM t2 AS innr WHERE outr.dt IS NULL );
    51  
    52  drop table if exists t1;
    53  drop table if exists t2;
    54  
    55  create table t1 (id int not null, str char(10), index(str));
    56  insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar");
    57  select * from t1 where str is not null order by id;
    58  select * from t1 where str is null;
    59  drop table if exists t1;
    60  
    61  create table t1 (a int, key (a));
    62  insert into t1 values (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
    63  (10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
    64  select * from t1 where not(a is null);
    65  select * from t1 where not(a is not null);
    66  drop table if exists t1;
    67  
    68  -- @case
    69  -- @desc:test for is operator in update where
    70  -- @label:bvt
    71  drop table if exists t1;
    72  CREATE TABLE t1 (a INT);
    73  INSERT INTO t1 VALUES (1),(NULL);
    74  -- @ignore{
    75  UPDATE t1 SET a = 2 WHERE a IS NULL;
    76  select * from t1;
    77  -- @ignore}
    78  drop table if exists t1;
    79  
    80  drop table if exists t1;
    81  drop table if exists t2;
    82  drop table if exists t3;
    83  create table t1 (a int, b int);
    84  create table t2 (a int, b int);
    85  insert into t1 values (1,1),(2,1),(3,1);
    86  insert into t2 values (1,1), (3,1);
    87  -- @ignore{
    88  select t1.a, t1.b,t2.a, t2.b from t1 left join t2  on t1.a=t2.a where t1.b=1 and t2.b=1 or t2.a is NULL;
    89  -- @ignore}
    90  drop table if exists t1;
    91  drop table if exists t2;
    92  drop table if exists t3;
    93  
    94  -- @case
    95  -- @desc:test for is operator in join
    96  -- @label:bvt
    97  drop table if exists t1;
    98  drop table if exists t2;
    99  drop table if exists t3;
   100  CREATE TABLE t1 (
   101    grp int(11) default NULL,
   102    a bigint(20) unsigned default NULL,
   103    c char(10) NOT NULL default ''
   104  );
   105  INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,'');
   106  create table t2 (id int, a bigint unsigned not null, c char(10), d int, primary key (a));
   107  insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7);
   108  select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null;
   109  select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a and t2.id is null);
   110  
   111  drop table if exists t1;
   112  drop table if exists t2;
   113  drop table if exists t3;
   114  CREATE TABLE t1 (
   115    id smallint(5) unsigned NOT NULL,
   116    name char(60) DEFAULT '' NOT NULL,
   117    PRIMARY KEY (id)
   118  );
   119  INSERT INTO t1 VALUES (1,'Antonio Paz');
   120  INSERT INTO t1 VALUES (2,'Lilliana Angelovska');
   121  INSERT INTO t1 VALUES (3,'Thimble Smith');
   122  
   123  CREATE TABLE t2 (
   124    id smallint(5) unsigned NOT NULL,
   125    owner smallint(5) unsigned DEFAULT 0 NOT NULL,
   126    name char(60),
   127    PRIMARY KEY (id)
   128  );
   129  INSERT INTO t2 VALUES (1,1,'El Gato');
   130  INSERT INTO t2 VALUES (2,1,'Perrito');
   131  INSERT INTO t2 VALUES (3,3,'Happy');
   132  select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
   133  select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null;
   134  select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
   135  select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null;
   136  
   137  drop table if exists t1;
   138  drop table if exists t2;
   139  drop table if exists t3;
   140  CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);
   141  INSERT INTO t1 VALUES (1,'a',1);
   142  INSERT INTO t1 VALUES (2,'b',1);
   143  INSERT INTO t1 VALUES (3,'c',2);
   144  
   145  CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1));
   146  INSERT INTO t2 VALUES (1,'x');
   147  INSERT INTO t2 VALUES (2,'y');
   148  INSERT INTO t2 VALUES (3,'z');
   149  SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
   150  SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
   151  drop table if exists t1;
   152  drop table if exists t2;
   153  drop table if exists t3;
   154  
   155  CREATE TABLE t1 (
   156    id int(11),
   157    pid int(11),
   158    rep_del tinyint(4)
   159  );
   160  INSERT INTO t1 VALUES (1,NULL,NULL);
   161  INSERT INTO t1 VALUES (2,1,NULL);
   162  select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
   163  select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
   164  drop table if exists t1;
   165  
   166  drop table if exists t1;
   167  drop table if exists t2;
   168  drop table if exists t3;
   169  CREATE TABLE t1 (a DATE NOT NULL, b INT);
   170  INSERT INTO t1 VALUES ('1999-05-100',1), ('1999-05-10',2);
   171  
   172  CREATE TABLE t2 (a DATETIME NOT NULL, b INT);
   173  INSERT INTO t2 VALUES ('1999-05-10 00:01:01',1), ('1999-05-10 00:00:00',2);
   174  
   175  SELECT * FROM t1 WHERE a IS NULL;
   176  SELECT * FROM t2 WHERE a IS NULL;
   177  SELECT * FROM t1 LEFT JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL;
   178  SELECT * FROM t2 LEFT JOIN t2 AS t2_2 ON 1 WHERE t2_2.a IS not NULL;
   179  SELECT * FROM t1 JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL;
   180  SELECT * FROM t2 JOIN t2 AS t2_2 ON 1 WHERE t2_2.a IS not NULL;
   181  drop table if exists t1;
   182  drop table if exists t2;
   183  drop table if exists t3;
   184  
   185  -- @case
   186  -- @desc:test for is operator in function
   187  -- @label:bvt
   188  drop table if exists t1;
   189  create table t1 (col1 datetime);
   190  insert into t1 values("2004-10-31 15:30:00");
   191  insert into t1 values("2004-12-12 11:22:33");
   192  insert into t1 values("2004-12-12 10:22:59");
   193  insert into t1 values(null);
   194  select count(*) from t1 where YEAR(col1) IS NULL;
   195  select count(*) from t1 where YEAR(col1) IS not NULL;
   196  drop table if exists t1;
   197  
   198  -- @case
   199  -- @desc:test for is operator in subquery
   200  -- @label:bvt
   201  drop table if exists t1;
   202  drop table if exists t2;
   203  drop table if exists t3;
   204  create table t1 (id int(10) not null, cur_date datetime not null);
   205  create table t2 (id int(10) not null, cur_date date not null);
   206  insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22');
   207  insert into t2 (id, cur_date) values (1, '2007-04-25');
   208  select * from t1
   209  where id in (select id from t1 as x1 where (t1.cur_date is null));
   210  
   211  select * from t2
   212  where id in (select id from t2 as x1 where (t2.cur_date is null));
   213  
   214  insert into t1 (id, cur_date) values (2, '2007-04-26 18:30:22');
   215  insert into t2 (id, cur_date) values (2, '2007-04-26');
   216  
   217  select * from t1
   218  where id in (select id from t1 as x1 where (t1.cur_date is null));
   219  
   220  select * from t2
   221  where id in (select id from t2 as x1 where (t2.cur_date is null));
   222  
   223  -- @case
   224  -- @desc:test for is operator in having
   225  -- @label:bvt
   226  drop table if exists t1;
   227  drop table if exists t2;
   228  drop table if exists t3;
   229  CREATE TABLE `t1` (
   230    `numeropost` int(8) unsigned NOT NULL,
   231    `maxnumrep` int(10) unsigned NOT NULL default 0,
   232    PRIMARY KEY  (`numeropost`)
   233  ) ;
   234  
   235  INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
   236  
   237  CREATE TABLE `t2` (
   238        `mot` varchar(30) NOT NULL default '',
   239        `topic` int(8) unsigned NOT NULL default 0,
   240        `dt` date,
   241        `pseudo` varchar(35) NOT NULL default '',
   242         PRIMARY KEY  (`topic`)
   243   ) ;
   244  
   245  INSERT INTO t2 (mot,topic,dt,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
   246  SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic is null);
   247  -- @bvt:issue#3307
   248  SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);
   249  -- @bvt:issue
   250  SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic is not null);
   251  SELECT * from t2 where topic NOT IN (SELECT topic FROM t2 GROUP BY topic HAVING topic is null);
   252  
   253  -- @case
   254  -- @desc:test for is operator in case-when
   255  -- @label:bvt
   256  drop table if exists t1;
   257  drop table if exists t2;
   258  drop table if exists t3;
   259  CREATE TABLE t1 (a varchar(10), PRIMARY KEY (a));
   260  CREATE TABLE t2 (a varchar(10), b date, PRIMARY KEY(a));
   261  INSERT INTO t1 VALUES ('test1');
   262  INSERT INTO t2 VALUES('test1','2016-12-13'),('test2','2016-12-14'),('test3','2016-12-15'),('test4',NULL),('test5',NULL);
   263  SELECT b,
   264         CASE  WHEN b is NULL then 'found' ELSE 'not found' END FROM t2;
   265  SELECT b,
   266         CASE  WHEN b is not NULL then 'found' ELSE 'not found' END FROM t2;
   267  select (null) is unknown;
   268  select null is unknown;
   269  select true is unknown;
   270  select false is unknown;
   271  drop table if exists t1;
   272  CREATE TABLE t1 (a bool);
   273  insert into t1 values (false), (null), (true), (null);
   274  select a is unknown from t1 order by a;
   275  select null is false;
   276  select null is true;
   277  select null is not false;
   278  select null is not true;
   279  select 1 is true;
   280  select 1 is false;
   281  select 1 is not true;
   282  select 1 is not false;
   283  select a is true from t1 order by a;