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

     1  -- @suit
     2  
     3  -- @case
     4  -- @desc:Test prepared statements with signed and unsigned integer user variables
     5  -- @label:bvt
     6  set time_zone="+08:00";
     7  drop table if exists numbers;
     8  CREATE TABLE numbers
     9  (pk INTEGER PRIMARY KEY,
    10   ui BIGINT UNSIGNED,
    11   si BIGINT
    12  );
    13  
    14  INSERT INTO numbers VALUES
    15  (0, 0, -9223372036854775808), (1, 18446744073709551615, 9223372036854775807);
    16  
    17  
    18  SET @ui_min = CAST(0 AS UNSIGNED);
    19  
    20  SET @ui_min = 0;
    21  SET @ui_max = 18446744073709551615;
    22  SET @si_min = -9223372036854775808;
    23  SET @si_max = 9223372036854775807;
    24  
    25  PREPARE s1 FROM 'SELECT * FROM numbers WHERE ui=?';
    26  EXECUTE s1 USING @ui_min;
    27  EXECUTE s1 USING @ui_max;
    28  -- @bvt:issue#7278
    29  EXECUTE s1 USING @si_min;
    30  -- @bvt:issue
    31  EXECUTE s1 USING @si_max;
    32  DEALLOCATE PREPARE s1;
    33  
    34  PREPARE s2 FROM 'SELECT * FROM numbers WHERE si=?';
    35  EXECUTE s2 USING @ui_min;
    36  EXECUTE s2 USING @ui_max;
    37  EXECUTE s2 USING @si_min;
    38  EXECUTE s2 USING @si_max;
    39  
    40  DEALLOCATE PREPARE s2;
    41  
    42  DROP TABLE numbers;
    43  
    44  
    45  -- @case
    46  -- @desc:Test prepared statements with float and double floating user variables
    47  -- @label:bvt
    48  drop table if exists test_table;
    49  CREATE TABLE test_table
    50  (pk INTEGER PRIMARY KEY,
    51   fl FLOAT,
    52   dou DOUBLE
    53  );
    54  
    55  set @float1_num=1.2345678;
    56  set @float2_num=1.8765432;
    57  set @double_num1=1.223344556677889900;
    58  set @double_num2=1.223344556677889900;
    59  INSERT INTO test_table VALUES(0, @float1_num, @double_num1), (1, @float2_num, @double_num2);
    60  
    61  
    62  INSERT INTO test_table VALUES(0, 1.2345678, 1.223344556677889900), (1, 1.876599999432, 1.223344556677889900);
    63  select * from test_table;
    64  select * from test_table where fl=1.2345678;
    65  
    66  SET @fl_hit=1.2345678;
    67  SET @fl_not_hit=1.234567800;
    68  SET @dou_not_hit=1.223344556677889;
    69  SET @dou_hit=1.223344556677889900;
    70  
    71  PREPARE s1 FROM 'SELECT * FROM test_table WHERE fl=?';
    72  PREPARE s2 FROM 'SELECT * FROM test_table WHERE dou=?';
    73  
    74  EXECUTE s1 USING @fl_hit;
    75  EXECUTE s1 USING @fl_not_hit;
    76  EXECUTE s1 USING @dou_hit;
    77  EXECUTE s1 USING @dou_not_hit;
    78  EXECUTE s2 USING @fl_hit;
    79  EXECUTE s2 USING @fl_not_hit;
    80  EXECUTE s2 USING @dou_hit;
    81  EXECUTE s2 USING @dou_not_hit;
    82  
    83  DEALLOCATE PREPARE s1;
    84  DEALLOCATE PREPARE s2;
    85  
    86  DROP TABLE test_table;
    87  
    88  
    89  -- @case
    90  -- @desc:Test prepared statements with varchar and char string user variables
    91  -- @label:bvt
    92  drop table if exists t1;
    93  create table t1 (
    94      str1 varchar(25),
    95      str2 char(25)
    96  );
    97  
    98  insert into t1 values('a1','b1'),('a2', 'b2'),('a3', '');
    99  insert into t1(str1) values('a4');
   100  
   101  prepare s1 from 'update t1 set str1="xx1" where str2=?';
   102  
   103  set @hit_str2='b1';
   104  set @not_hit_str2='b';
   105  
   106  execute s1 using @hit_str2;
   107  execute s1 using @not_hit_str2;
   108  
   109  select * from t1;
   110  
   111  DEALLOCATE PREPARE s1;
   112  
   113  
   114  prepare s2 from 'update t1 set str2="yy1" where str1=?';
   115  
   116  set @hit_str1='a2';
   117  set @not_hit_str2='a';
   118  
   119  execute s2 using @hit_str1;
   120  execute s2 using @not_hit_str1;
   121  
   122  select * from t1;
   123  
   124  DEALLOCATE PREPARE s2;
   125  
   126  
   127  prepare s3 from 'select * from t1 where str1 like ?';
   128  prepare s4 from 'select * from t1 where str2 not like ?';
   129  
   130  set @a='a%';
   131  execute s3 using @a;
   132  
   133  DEALLOCATE PREPARE s3;
   134  DEALLOCATE PREPARE s4;
   135  
   136  prepare s5 from 'select * from t1 where str2=?';
   137  
   138  set @hit_empty='';
   139  
   140  execute s5 using @hit_empty;
   141  
   142  DEALLOCATE PREPARE s5;
   143  
   144  DROP TABLE t1;
   145  
   146  
   147  -- @case
   148  -- @desc:Test prepared statements with DATE and DATETIME and TIMESTAMP time user variables
   149  -- @label:bvt
   150  
   151  drop table if exists t2;
   152  create table t2 (
   153      time1 Date,
   154      time2 DateTime,
   155      time3 TIMESTAMP
   156  );
   157  
   158  insert into t2 values ('1000-01-01', '0001-01-01 00:00:00.000000', '2038-01-19 03:14:07.999999');
   159  insert into t2 values ('1000-01-01', '9999-12-31 23:59:59.999999', '2038-01-19 03:14:07.999999');
   160  insert into t2 values ('9999-12-31', '9999-12-31 23:59:59.999999', '2038-01-19 03:14:07.999999');
   161  
   162  insert into t2 values ('1000-01-01', '0001-01-01 00:00:00.000000', '1970-01-01 00:00:01.000000');
   163  insert into t2 values ('1000-01-01', '0001-01-01 00:00:00.000000', '1970-01-01 00:00:01.000000');
   164  insert into t2 values ('1000-01-01', '0001-01-01 00:00:00.000000', '1970-01-01 00:00:01.000000');
   165  
   166  insert into t2 values ('2022-10-24', '2022-10-24 10:10:10.000000', '2022-10-24 00:00:01.000000');
   167  insert into t2 values ('2022-10-25', '2022-10-25 10:10:10.000000', '2022-10-25 00:00:01.000000');
   168  insert into t2 values ('2022-10-26', '2022-10-26 10:10:10.000000', '2022-10-26 00:00:01.000000');
   169  
   170  select * from t2;
   171  
   172  set @max_date='9999-12-31';
   173  set @min_date='1000-01-01';
   174  
   175  set @max_datetime='9999-12-31 23:59:59.999999';
   176  set @min_datetime='0001-01-01 00:00:00.000000';
   177  set @max_timestamp='1970-01-01 00:00:01.000000';
   178  set @min_timestamp='2038-01-19 03:14:07.999999';
   179  
   180  
   181  prepare s1 from 'select * from t2 where time1=?';
   182  
   183  execute s1 using @max_date;
   184  execute s1 using @min_date;
   185  execute s1 using @max_datetime;
   186  execute s1 using @min_datetime;
   187  execute s1 using @max_timestamp;
   188  execute s1 using @min_timestamp;
   189  
   190  DEALLOCATE PREPARE s1;
   191  
   192  
   193  prepare s2 from 'select * from t2 where time2=?';
   194  
   195  execute s2 using @max_date;
   196  execute s2 using @min_date;
   197  execute s2 using @max_datetime;
   198  execute s2 using @min_datetime;
   199  execute s2 using @max_timestamp;
   200  execute s2 using @min_timestamp;
   201  
   202  DEALLOCATE PREPARE s2;
   203  
   204  
   205  prepare s3 from 'select * from t2 where time3=?';
   206  
   207  execute s3 using @max_date;
   208  execute s3 using @min_date;
   209  execute s3 using @max_datetime;
   210  execute s3 using @min_datetime;
   211  execute s3 using @max_timestamp;
   212  execute s3 using @min_timestamp;
   213  
   214  DEALLOCATE PREPARE s3;
   215  
   216  
   217  set @time1='2022-10-24';
   218  set @time2='2022-10-25 10:10:10.000000';
   219  set @time3='2022-10-26 00:00:01.000000';
   220  
   221  prepare s4 from 'delete from t2 where time1=?';
   222  prepare s5 from 'delete from t2 where time2=?';
   223  prepare s6 from 'delete from t2 where time3=?';
   224  
   225  execute s4 using @time1;
   226  execute s5 using @time2;
   227  execute s6 using @time3;
   228  
   229  select * from t2;
   230  
   231  DEALLOCATE PREPARE s4;
   232  DEALLOCATE PREPARE s5;
   233  DEALLOCATE PREPARE s6;
   234  
   235  drop table t2;
   236  
   237  
   238  -- @case
   239  -- @desc:Test prepared statements with decimal64 and decimal128 decimal variables
   240  -- @label:bvt
   241  
   242  drop table if exists t3;
   243  create table t3(
   244      dec1 decimal(5,2) default  NULL,
   245      dec2 decimal(25,10)
   246  );
   247  
   248  insert into t3 values (12.345, 10000.222223333344444);
   249  insert into t3 values (123.45, 1111122222.222223333344444);
   250  insert into t3 values (133.45, 1111122222.222223333344444);
   251  insert into t3 values (153.45, 1111122222.222223333344444);
   252  insert into t3 values (123.45678, 111112222233333.222223333344444);
   253  insert into t3(dec2) values (111112222233333.222223333344444);
   254  
   255  select * from t3;
   256  
   257  set @hit_dec1=12.34;
   258  set @hit_dec2=1111122222.2222233333;
   259  set @dec1_max=200;
   260  set @dec1_min=10;
   261  set @dec2_max=111112222233339;
   262  set @dec2_min=1000;
   263  
   264  prepare s1 from 'select * from t3 where dec1>?';
   265  prepare s2 from 'select * from t3 where dec1>=?';
   266  prepare s3 from 'select * from t3 where dec1<?';
   267  prepare s4 from 'select * from t3 where dec1<=?';
   268  prepare s5 from 'select * from t3 where dec1<>?';
   269  prepare s6 from 'select * from t3 where dec1!=?';
   270  prepare s7 from 'select * from t3 where dec1 between ? and ?';
   271  prepare s8 from 'select * from t3 where dec1 not between ? and ?';
   272  
   273  execute s1 using @hit_dec1;
   274  execute s1 using @dec1_max;
   275  execute s1 using @dec1_min;
   276  
   277  execute s2 using @hit_dec1;
   278  execute s2 using @dec1_max;
   279  execute s2 using @dec1_min;
   280  
   281  execute s3 using @hit_dec1;
   282  execute s3 using @dec1_max;
   283  execute s3 using @dec1_min;
   284  
   285  execute s4 using @hit_dec1;
   286  
   287  execute s4 using @dec1_max;
   288  execute s4 using @dec1_min;
   289  
   290  execute s5 using @hit_dec1;
   291  
   292  execute s5 using @dec1_max;
   293  execute s5 using @dec1_min;
   294  
   295  execute s6 using @hit_dec1;
   296  
   297  execute s6 using @dec1_max;
   298  execute s6 using @dec1_min;
   299  
   300  
   301  execute s7 using @dec1_min, @dec1_max;
   302  execute s7 using @dec1_max, @dec1_min;
   303  
   304  execute s8 using @dec1_min, @dec1_max;
   305  execute s8 using @dec1_max, @dec1_min;
   306  
   307  DEALLOCATE PREPARE s1;
   308  DEALLOCATE PREPARE s2;
   309  DEALLOCATE PREPARE s3;
   310  DEALLOCATE PREPARE s4;
   311  DEALLOCATE PREPARE s5;
   312  DEALLOCATE PREPARE s6;
   313  DEALLOCATE PREPARE s7;
   314  DEALLOCATE PREPARE s8;
   315  
   316  drop table t3;
   317  
   318  
   319  -- @case
   320  -- @desc:test group by having scene
   321  -- @label:bvt
   322  drop table if exists t4;
   323  create table t4(
   324      a1 INT,
   325      str1 varchar(25)
   326  );
   327  
   328  insert into t4 values (10, 'aaa');
   329  insert into t4 values (10, 'bbb');
   330  insert into t4 values (20, 'aaa');
   331  insert into t4 values (20, 'bbb');
   332  insert into t4 values (20, 'bbb');
   333  insert into t4 values (20, 'bbb');
   334  insert into t4 values (20, 'bbb');
   335  insert into t4 values (20, 'ccc');
   336  
   337  set @min=1;
   338  set @max=5;
   339  
   340  prepare s1 from 'select str1,count(a1) as c from t4 group by str1 having count(a1)>?';
   341  prepare s2 from 'select str1,count(a1) as c from t4 group by str1 having count(a1)>=?';
   342  prepare s3 from 'select str1,count(a1) as c from t4 group by str1 having count(a1)<?';
   343  prepare s4 from 'select str1,count(a1) as c from t4 group by str1 having count(a1)<=?';
   344  
   345  execute s1 using @min;
   346  execute s2 using @min;
   347  execute s3 using @max;
   348  execute s4 using @max;
   349  
   350  
   351  DEALLOCATE PREPARE s1;
   352  DEALLOCATE PREPARE s2;
   353  DEALLOCATE PREPARE s3;
   354  DEALLOCATE PREPARE s4;
   355  
   356  drop table t4;
   357  
   358  
   359  -- @case
   360  -- @desc:test join on where scene
   361  -- @label:bvt
   362  
   363  drop table if exists t5;
   364  create table t5(
   365      a1 int,
   366      a2 varchar(25)
   367  );
   368  
   369  drop table if exists t6;
   370  create table t6(
   371      b1 int,
   372      b2 varchar(25)
   373  );
   374  
   375  insert into t5 values (10, 'xxx1');
   376  insert into t5 values (20, 'xxx1');
   377  insert into t5 values (30, 'xxx1');
   378  insert into t5 values (10, 'yyy1');
   379  insert into t5 values (10, 'zzz1');
   380  insert into t5 values (20, 'yyy1');
   381  insert into t5 values (40, 'xxx1');
   382  
   383  insert into t6 values (10, 'aaa1');
   384  insert into t6 values (20, 'aaa1');
   385  insert into t6 values (30, 'aaa1');
   386  insert into t6 values (40, 'bbb1');
   387  insert into t6 values (50, 'aaa1');
   388  insert into t6 values (60, 'ccc1');
   389  insert into t6 values (10, 'aaa1');
   390  insert into t6 values (20, 'ccc1');
   391  
   392  set @a2_val='yyy1';
   393  set @min=10;
   394  
   395  prepare s1 from 'select * from t5 a inner join t6 b on a.a1=b.b1 where a.a2=?';
   396  prepare s2 from 'select * from t5 a inner join t6 b on a.a1=b.b1 where a.a1>=?';
   397  prepare s3 from 'select * from t5 a inner join t6 b on a.a1=b.b1 where b.b1>=?';
   398  
   399  prepare s4 from 'select * from t5 a left join t6 b on a.a1=b.b1 where a.a2=?';
   400  prepare s5 from 'select * from t5 a left join t6 b on a.a1=b.b1 where a.a1>=?';
   401  prepare s6 from 'select * from t5 a left join t6 b on a.a1=b.b1 where b.b1>=?';
   402  
   403  prepare s7 from 'select * from t5 a right join t6 b on a.a1=b.b1 where a.a2=?';
   404  prepare s8 from 'select * from t5 a right join t6 b on a.a1=b.b1 where a.a1>=?';
   405  prepare s9 from 'select * from t5 a right join t6 b on a.a1=b.b1 where b.b1>=?';
   406  
   407  execute s1 using @a2_val;
   408  execute s2 using @min;
   409  execute s3 using @min;
   410  
   411  execute s4 using @a2_val;
   412  execute s5 using @min;
   413  execute s6 using @min;
   414  
   415  execute s7 using @a2_val;
   416  
   417  execute s8 using @min;
   418  execute s9 using @min;
   419  
   420  DEALLOCATE PREPARE s1;
   421  DEALLOCATE PREPARE s2;
   422  DEALLOCATE PREPARE s3;
   423  DEALLOCATE PREPARE s4;
   424  DEALLOCATE PREPARE s5;
   425  DEALLOCATE PREPARE s6;
   426  DEALLOCATE PREPARE s7;
   427  DEALLOCATE PREPARE s8;
   428  DEALLOCATE PREPARE s9;
   429  
   430  set @a1=10;
   431  set @b1=10;
   432  
   433  prepare s1 from 'select * from t5 where a1 > ? union select * from t6 where b1 > ?';
   434  prepare s2 from 'select * from t5 where a1 > ? union all select * from t6 where b1 > ?';
   435  
   436  execute s1 using @a1, @b1;
   437  execute s2 using @a1, @b1;
   438  
   439  
   440  drop table t5;
   441  drop table t6;
   442  
   443  -- @case
   444  -- @desc:test maxint operation
   445  -- @label:bvt
   446  
   447  set @maxint=18446744073709551615;
   448  select @maxint;
   449  
   450  SELECT @maxint + 0e0;
   451  SELECT 18446744073709551615 + 0e0;
   452  
   453  SELECT @maxint + 0.0;
   454  SELECT 18446744073709551615 + 0.0;
   455  
   456  
   457  PREPARE s FROM 'SELECT 0e0 + ?';
   458  
   459  EXECUTE s USING @maxint;
   460  DEALLOCATE PREPARE s;
   461  
   462  PREPARE s FROM 'SELECT 0.0 + ?';
   463  
   464  EXECUTE s USING @maxint;
   465  DEALLOCATE PREPARE s;
   466  
   467  PREPARE s FROM 'SELECT 0 + ?';
   468  
   469  EXECUTE s USING @maxint;
   470  DEALLOCATE PREPARE s;
   471  
   472  PREPARE s FROM 'SELECT concat(?,"")';
   473  
   474  EXECUTE s USING @maxint;
   475  DEALLOCATE PREPARE s;
   476  
   477  --test order by clause contains placeholder
   478  CREATE DATABASE mocloud_meta;
   479  PREPARE mo_stmt_id_1 FROM SELECT SCHEMA_NAME from Information_schema.SCHEMATA where SCHEMA_NAME LIKE ? ORDER BY SCHEMA_NAME=? DESC,SCHEMA_NAME limit 1;
   480  SET @dbname1 = 'mocloud_meta%';
   481  SET @dbname2 = 'mocloud_meta';
   482  EXECUTE mo_stmt_id_1 USING @dbname1, @dbname2;
   483  DEALLOCATE PREPARE mo_stmt_id_1;
   484  DROP DATABASE mocloud_meta;
   485  
   486