oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4

5
#owner: linlin.xll
6
#owner group: sql1
7
#description: test pl variables
8
#tags: pl
9

10
--result_format 4
11

12
set ob_query_timeout=100000000;
13

14
###########################################################################
15
#
16
# Cleanup.
17
#
18
###########################################################################
19

20
--disable_warnings
21

22
# Drop stored routines (if any) for general SP-vars test cases. These routines
23
# are created in include/sp-vars.inc file.
24

25
DROP PROCEDURE IF EXISTS sp_vars_check_dflt;
26
DROP PROCEDURE IF EXISTS sp_vars_check_assignment;
27
DROP FUNCTION IF EXISTS sp_vars_check_ret1;
28
DROP FUNCTION IF EXISTS sp_vars_check_ret2;
29
DROP FUNCTION IF EXISTS sp_vars_check_ret3;
30
DROP FUNCTION IF EXISTS sp_vars_check_ret4;
31
DROP FUNCTION IF EXISTS sp_vars_div_zero;
32

33
--enable_warnings
34

35
###########################################################################
36
#
37
# Some general tests for SP-vars functionality.
38
#
39
###########################################################################
40

41
# Create the procedure in ANSI mode. Check that all necessary warnings are
42
# emitted properly.
43

44
## SET @@sql_mode = 'ansi';
45
## REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI
46
## for ansi, oceanbase only support PIPES_AS_CONCAT and ONLY_FULL_GROUP_BY
47
set @@sql_mode = "PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY";
48

49
--source mysql_test/include/sp-vars.inc
50

51
--echo
52
--echo ---------------------------------------------------------------
53
--echo Calling the routines, created in ANSI mode.
54
--echo ---------------------------------------------------------------
55
--echo
56

57
CALL sp_vars_check_dflt();
58

59
CALL sp_vars_check_assignment();
60

61
SELECT sp_vars_check_ret1();
62

63
SELECT sp_vars_check_ret2();
64

65
SELECT sp_vars_check_ret3();
66

67
SELECT sp_vars_check_ret4();
68

69
SELECT sp_vars_div_zero();
70

71
# Check that changing sql_mode after creating a store procedure does not
72
# matter.
73

74
## SET @@sql_mode = 'traditional';
75
## STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
76
## for traditional oceanbase only support STRICT_TRANS_TABLES, STRICT_ALL_TABLES
77
SET @@sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES';
78
--echo
79
--echo ---------------------------------------------------------------
80
--echo Calling in TRADITIONAL mode the routines, created in ANSI mode.
81
--echo ---------------------------------------------------------------
82
--echo
83

84
CALL sp_vars_check_dflt();
85

86
CALL sp_vars_check_assignment();
87

88
SELECT sp_vars_check_ret1();
89

90
SELECT sp_vars_check_ret2();
91

92
SELECT sp_vars_check_ret3();
93

94
SELECT sp_vars_check_ret4();
95

96
SELECT sp_vars_div_zero();
97

98
# Create the procedure in TRADITIONAL mode. Check that error will be thrown on
99
# execution.
100

101
DROP PROCEDURE sp_vars_check_dflt;
102
DROP PROCEDURE sp_vars_check_assignment;
103
DROP FUNCTION sp_vars_check_ret1;
104
DROP FUNCTION sp_vars_check_ret2;
105
DROP FUNCTION sp_vars_check_ret3;
106
DROP FUNCTION sp_vars_check_ret4;
107
DROP FUNCTION sp_vars_div_zero;
108

109
--source mysql_test/include/sp-vars.inc
110

111
--echo
112
--echo ---------------------------------------------------------------
113
--echo Calling the routines, created in TRADITIONAL mode.
114
--echo ---------------------------------------------------------------
115
--echo
116

117
--error ER_WARN_DATA_OUT_OF_RANGE
118
CALL sp_vars_check_dflt();
119

120
--error ER_WARN_DATA_OUT_OF_RANGE
121
CALL sp_vars_check_assignment();
122

123
--error ER_WARN_DATA_OUT_OF_RANGE
124
SELECT sp_vars_check_ret1();
125

126
--error ER_WARN_DATA_OUT_OF_RANGE
127
SELECT sp_vars_check_ret2();
128

129
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
130
SELECT sp_vars_check_ret3();
131

132
# TODO: Is it an error, that only a warning is emitted here? Check the same
133
# behaviour with tables.
134

135
SELECT sp_vars_check_ret4();
136

137
## --error ER_DIVISION_BY_ZERO
138
SELECT sp_vars_div_zero();
139

140
## SET @@sql_mode = 'ansi';
141
set @@sql_mode = "PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY";
142

143
#
144
# Cleanup.
145
#
146

147
DROP PROCEDURE sp_vars_check_dflt;
148
DROP PROCEDURE sp_vars_check_assignment;
149
DROP FUNCTION sp_vars_check_ret1;
150
DROP FUNCTION sp_vars_check_ret2;
151
DROP FUNCTION sp_vars_check_ret3;
152
DROP FUNCTION sp_vars_check_ret4;
153
DROP FUNCTION sp_vars_div_zero;
154

155
###########################################################################
156
#
157
# Tests for BIT data type.
158
#
159
###########################################################################
160

161
--echo
162
--echo ---------------------------------------------------------------
163
--echo BIT data type tests
164
--echo ---------------------------------------------------------------
165
--echo
166

167
#
168
# Prepare.
169
#
170

171
--disable_warnings
172
DROP PROCEDURE IF EXISTS p1;
173
--enable_warnings
174

175
#
176
# Test case.
177
#
178

179
delimiter |;
180
CREATE PROCEDURE p1()
181
BEGIN
182
  DECLARE v1 BIT;
183
  DECLARE v2 BIT(1);
184
  DECLARE v3 BIT(3) DEFAULT b'101';
185
  DECLARE v4 BIT(64) DEFAULT 0x5555555555555555;
186
  DECLARE v5 BIT(3);
187
  DECLARE v6 BIT(64);
188
  DECLARE v7 BIT(8) DEFAULT 128;
189
  DECLARE v8 BIT(8) DEFAULT '128';
190
  DECLARE v9 BIT(8) DEFAULT ' 128';
191
  DECLARE v10 BIT(8) DEFAULT 'x 128';
192

193
  SET v1 = v4;
194
  SET v2 = 0;
195
  SET v5 = v4; # check overflow
196
  SET v6 = v3; # check padding
197

198
  SELECT HEX(v1);
199
  SELECT HEX(v2);
200
  SELECT HEX(v3);
201
  SELECT HEX(v4);
202
  SELECT HEX(v5);
203
  SELECT HEX(v6);
204
  SELECT HEX(v7);
205
  SELECT HEX(v8);
206
  SELECT HEX(v9);
207
  SELECT HEX(v10);
208
END|
209
delimiter ;|
210

211
CALL p1();
212

213
#
214
# Cleanup.
215
#
216

217
DROP PROCEDURE p1;
218

219
###########################################################################
220
#
221
# Tests for CASE statements functionality:
222
#   - test for general functionality (scopes, nested cases, CASE in loops);
223
#   - test that if type of the CASE expression is changed on each iteration,
224
#     the execution will be correct.
225
#
226
###########################################################################
227

228
--echo
229
--echo ---------------------------------------------------------------
230
--echo CASE expression tests.
231
--echo ---------------------------------------------------------------
232
--echo
233

234
#
235
# Prepare.
236
#
237

238
--disable_warnings
239
DROP PROCEDURE IF EXISTS p1;
240
DROP PROCEDURE IF EXISTS p2;
241
DROP TABLE IF EXISTS t1;
242
--enable_warnings
243

244
#
245
# Test case.
246
#
247
## use create time to make the order of log_msg stable
248
CREATE TABLE t1(ctime int auto_increment, log_msg VARCHAR(1024));
249

250
delimiter |;
251

252
CREATE PROCEDURE p1(arg VARCHAR(255))
253
BEGIN
254
  INSERT INTO t1(log_msg) VALUES('p1: step1');
255
  CASE arg * 10
256
    WHEN 10 * 10 THEN
257
      INSERT INTO t1(log_msg) VALUES('p1: case1: on 10');
258
    WHEN 10 * 10 + 10 * 10 THEN
259
      BEGIN
260
        CASE arg / 10
261
          WHEN 1 THEN
262
            INSERT INTO t1(log_msg) VALUES('p1: case1: case2: on 1');
263
          WHEN 2 THEN
264
            BEGIN
265
              DECLARE i TINYINT DEFAULT 10;
266

267
              WHILE i > 0 DO
268
                INSERT INTO t1(log_msg) VALUES(CONCAT('p1: case1: case2: loop: i: ', i));
269
                
270
                CASE MOD(i, 2)
271
                  WHEN 0 THEN
272
                    INSERT INTO t1(log_msg) VALUES('p1: case1: case2: loop: i is even');
273
                  WHEN 1 THEN
274
                    INSERT INTO t1(log_msg) VALUES('p1: case1: case2: loop: i is odd');
275
                  ELSE
276
                    INSERT INTO t1(log_msg) VALUES('p1: case1: case2: loop: ERROR');
277
                END CASE;
278
                    
279
                SET i = i - 1;
280
              END WHILE;
281
            END;
282
          ELSE
283
            INSERT INTO t1(log_msg) VALUES('p1: case1: case2: ERROR');
284
        END CASE;
285

286
        CASE arg
287
          WHEN 10 THEN
288
            INSERT INTO t1(log_msg) VALUES('p1: case1: case3: on 10');
289
          WHEN 20 THEN
290
            INSERT INTO t1(log_msg) VALUES('p1: case1: case3: on 20');
291
          ELSE
292
            INSERT INTO t1(log_msg) VALUES('p1: case1: case3: ERROR');
293
        END CASE;
294
      END;
295
    ELSE
296
      INSERT INTO t1(log_msg) VALUES('p1: case1: ERROR');
297
  END CASE;
298

299
  CASE arg * 10
300
    WHEN 10 * 10 THEN
301
      INSERT INTO t1(log_msg) VALUES('p1: case4: on 10');
302
    WHEN 10 * 10 + 10 * 10 THEN
303
      BEGIN
304
        CASE arg / 10
305
          WHEN 1 THEN
306
            INSERT INTO t1(log_msg) VALUES('p1: case4: case5: on 1');
307
          WHEN 2 THEN
308
            BEGIN
309
              DECLARE i TINYINT DEFAULT 10;
310

311
              WHILE i > 0 DO
312
                INSERT INTO t1(log_msg) VALUES(CONCAT('p1: case4: case5: loop: i: ', i));
313
                
314
                CASE MOD(i, 2)
315
                  WHEN 0 THEN
316
                    INSERT INTO t1(log_msg) VALUES('p1: case4: case5: loop: i is even');
317
                  WHEN 1 THEN
318
                    INSERT INTO t1(log_msg) VALUES('p1: case4: case5: loop: i is odd');
319
                  ELSE
320
                    INSERT INTO t1(log_msg) VALUES('p1: case4: case5: loop: ERROR');
321
                END CASE;
322
                    
323
                SET i = i - 1;
324
              END WHILE;
325
            END;
326
          ELSE
327
            INSERT INTO t1(log_msg) VALUES('p1: case4: case5: ERROR');
328
        END CASE;
329

330
        CASE arg
331
          WHEN 10 THEN
332
            INSERT INTO t1(log_msg) VALUES('p1: case4: case6: on 10');
333
          WHEN 20 THEN
334
            INSERT INTO t1(log_msg) VALUES('p1: case4: case6: on 20');
335
          ELSE
336
            INSERT INTO t1(log_msg) VALUES('p1: case4: case6: ERROR');
337
        END CASE;
338
      END;
339
    ELSE
340
      INSERT INTO t1(log_msg) VALUES('p1: case4: ERROR');
341
  END CASE;
342
END|
343

344
CREATE PROCEDURE p2()
345
BEGIN
346
  DECLARE i TINYINT DEFAULT 3;
347

348
  WHILE i > 0 DO
349
    IF MOD(i, 2) = 0 THEN
350
      SET @_test_session_var = 10;
351
    ELSE
352
      SET @_test_session_var = 'test';
353
    END IF;
354

355
    CASE @_test_session_var
356
      WHEN 10 THEN
357
        INSERT INTO t1(log_msg) VALUES('p2: case: numerical type');
358
      WHEN 'test' THEN
359
        INSERT INTO t1(log_msg) VALUES('p2: case: string type');
360
      ELSE
361
        INSERT INTO t1(log_msg) VALUES('p2: case: ERROR');
362
    END CASE;
363

364
    SET i = i - 1;
365
  END WHILE;
366
END|
367

368
delimiter ;|
369

370
CALL p1(10);
371
CALL p1(20);
372

373
CALL p2();
374

375
SELECT ctime, log_msg FROM t1 order by ctime, log_msg;
376

377
#
378
# Cleanup.
379
#
380

381
DROP PROCEDURE p1;
382
DROP PROCEDURE p2;
383
DROP TABLE t1;
384

385
###########################################################################
386
#
387
# Test case for BUG#14161: Stored procedure cannot retrieve bigint unsigned.
388
#
389
###########################################################################
390

391
--echo
392
--echo ---------------------------------------------------------------
393
--echo BUG#14161
394
--echo ---------------------------------------------------------------
395
--echo
396

397
#
398
# Prepare.
399
#
400

401
--disable_warnings
402
DROP TABLE IF EXISTS t1;
403
DROP PROCEDURE IF EXISTS p1;
404
--enable_warnings
405

406
#
407
# Test case.
408
#
409

410
CREATE TABLE t1(col BIGINT UNSIGNED);
411

412
INSERT INTO t1 VALUE(18446744073709551614);
413

414
delimiter |;
415
CREATE PROCEDURE p1(IN arg BIGINT UNSIGNED)
416
BEGIN
417
  SELECT arg;
418
  SELECT * FROM t1;
419
  SELECT * FROM t1 WHERE col = arg;
420
END|
421
delimiter ;|
422

423
CALL p1(18446744073709551614);
424

425
#
426
# Cleanup.
427
#
428

429
DROP TABLE t1;
430
DROP PROCEDURE p1;
431

432
###########################################################################
433
#
434
# Test case for BUG#13705: parameters to stored procedures are not verified.
435
#
436
###########################################################################
437

438
--echo
439
--echo ---------------------------------------------------------------
440
--echo BUG#13705
441
--echo ---------------------------------------------------------------
442
--echo
443

444
#
445
# Prepare.
446
#
447

448
--disable_warnings
449
DROP PROCEDURE IF EXISTS p1;
450
--enable_warnings
451

452
#
453
# Test case.
454
#
455

456
delimiter |;
457
CREATE PROCEDURE p1(x VARCHAR(10), y CHAR(3)) READS SQL DATA
458
BEGIN
459
  SELECT x, y;
460
END|
461
delimiter ;|
462

463
CALL p1('alpha', 'abc');
464
CALL p1('alpha', 'abcdef');
465

466
#
467
# Cleanup.
468
#
469

470
DROP PROCEDURE p1;
471

472
###########################################################################
473
#
474
# Test case for BUG#13675: DATETIME/DATE type in store proc param seems to be
475
# converted as varbinary.
476
#
477
# TODO: test case failed.
478
#
479
###########################################################################
480

481
--echo
482
--echo ---------------------------------------------------------------
483
--echo BUG#13675
484
--echo ---------------------------------------------------------------
485
--echo
486

487
#
488
# Prepare.
489
#
490

491
## --disable_warnings
492
## DROP PROCEDURE IF EXISTS p1;
493
## DROP TABLE IF EXISTS t1;
494
## --enable_warnings
495

496
#
497
# Test case.
498
#
499
## TODO: DDL not support select x with variables
500
## delimiter |;
501
## CREATE PROCEDURE p1(x DATETIME)
502
## BEGIN
503
##   CREATE TABLE t1 AS SELECT x;
504
##   SHOW CREATE TABLE t1;
505
##   DROP TABLE t1;
506
## END|
507
## delimiter ;|
508
## 
509
## CALL p1(NOW());
510
## CALL p1('test');
511

512
#
513
# Cleanup.
514
#
515

516
## DROP PROCEDURE p1;
517

518
###########################################################################
519
#
520
# Test case for BUG#12976: Boolean values reversed in stored procedures?
521
#
522
###########################################################################
523

524
--echo
525
--echo ---------------------------------------------------------------
526
--echo BUG#12976
527
--echo ---------------------------------------------------------------
528
--echo
529

530
#
531
# Prepare.
532
#
533

534
--disable_warnings
535
DROP TABLE IF EXISTS t1;
536
DROP PROCEDURE IF EXISTS p1;
537
DROP PROCEDURE IF EXISTS p2;
538
--enable_warnings
539

540
#
541
# Test case.
542
#
543

544
CREATE TABLE t1(b BIT(1));
545

546
INSERT INTO t1(b) VALUES(b'0'), (b'1');
547

548
delimiter |;
549
CREATE PROCEDURE p1()
550
BEGIN
551
  SELECT HEX(b),
552
    b = 0,
553
    b = FALSE,
554
    b IS FALSE,
555
    b = 1,
556
    b = TRUE,
557
    b IS TRUE
558
  FROM t1;
559
END|
560

561
CREATE PROCEDURE p2()
562
BEGIN
563
  DECLARE vb BIT(1);
564
  SELECT b FROM t1 WHERE b = 0 INTO vb;
565

566
  SELECT HEX(vb),
567
    vb = 0,
568
    vb = FALSE,
569
    vb IS FALSE,
570
    vb = 1,
571
    vb = TRUE,
572
    vb IS TRUE;
573

574
  SELECT b FROM t1 WHERE b = 1 INTO vb;
575

576
  SELECT HEX(vb),
577
    vb = 0,
578
    vb = FALSE,
579
    vb IS FALSE,
580
    vb = 1,
581
    vb = TRUE,
582
    vb IS TRUE;
583
END|
584
delimiter ;|
585

586
call p1();
587
call p2();
588

589
#
590
# Cleanup.
591
#
592

593
DROP TABLE t1;
594
DROP PROCEDURE p1;
595
DROP PROCEDURE p2;
596

597
# Additional tests for Bug#12976
598

599
--disable_warnings
600
DROP TABLE IF EXISTS table_12976_a;
601
DROP TABLE IF EXISTS table_12976_b;
602
DROP PROCEDURE IF EXISTS proc_12976_a;
603
DROP PROCEDURE IF EXISTS proc_12976_b;
604
--enable_warnings
605

606
CREATE TABLE table_12976_a (val bit(1));
607

608
CREATE TABLE table_12976_b(
609
  appname varchar(15),
610
  emailperm bit not null default 1,
611
  phoneperm bit not null default 0);
612

613
insert into table_12976_b values ('A', b'1', b'1'), ('B', b'0', b'0');
614

615
delimiter ||;
616
CREATE PROCEDURE proc_12976_a()
617
BEGIN
618
  declare localvar bit(1);
619
  SELECT val FROM table_12976_a INTO localvar;
620
  SELECT coalesce(localvar, 1)+1, coalesce(val, 1)+1 FROM table_12976_a;
621
END||
622

623
CREATE PROCEDURE proc_12976_b(
624
  name varchar(15),
625
  out ep bit,
626
  out msg varchar(10))
627
BEGIN
628
  SELECT emailperm FROM table_12976_b where (appname = name) INTO ep;
629
  IF ep is true THEN
630
    SET msg = 'True';
631
  ELSE
632
    SET msg = 'False';
633
  END IF;
634
END||
635

636
delimiter ;||
637

638
INSERT table_12976_a VALUES (0);
639
--disable_warnings
640
call proc_12976_a();
641
--enable_warnings
642
UPDATE table_12976_a set val=1;
643
--disable_warnings
644
call proc_12976_a();
645
--enable_warnings
646

647
call proc_12976_b('A', @ep, @msg);
648
select HEX(@ep), @msg;
649

650
call proc_12976_b('B', @ep, @msg);
651
select HEX(@ep), @msg;
652

653
DROP TABLE table_12976_a;
654
DROP TABLE table_12976_b;
655
DROP PROCEDURE proc_12976_a;
656
DROP PROCEDURE proc_12976_b;
657

658

659
###########################################################################
660
#
661
# Test case for BUG#9572: Stored procedures: variable type declarations
662
# ignored.
663
#
664
###########################################################################
665

666
--echo
667
--echo ---------------------------------------------------------------
668
--echo BUG#9572
669
--echo ---------------------------------------------------------------
670
--echo
671

672
#
673
# Prepare.
674
#
675

676
--disable_warnings
677
DROP PROCEDURE IF EXISTS p1;
678
DROP PROCEDURE IF EXISTS p2;
679
DROP PROCEDURE IF EXISTS p3;
680

681
DROP PROCEDURE IF EXISTS p4;
682
DROP PROCEDURE IF EXISTS p5;
683
DROP PROCEDURE IF EXISTS p6;
684
--enable_warnings
685

686
#
687
# Test case.
688
#
689

690
## SET @@sql_mode = 'traditional';
691
SET @@sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES';
692

693
delimiter |;
694

695
CREATE PROCEDURE p1()
696
BEGIN
697
  DECLARE v TINYINT DEFAULT 1e200;
698
  SELECT v;
699
END|
700

701
CREATE PROCEDURE p2()
702
BEGIN
703
  DECLARE v DECIMAL(5) DEFAULT 1e200;
704
  SELECT v;
705
END|
706

707
CREATE PROCEDURE p3()
708
BEGIN
709
  DECLARE v CHAR(5) DEFAULT 'abcdef';
710
  SELECT v LIKE 'abc___';
711
END|
712

713
CREATE PROCEDURE p4(arg VARCHAR(2))
714
BEGIN
715
    DECLARE var VARCHAR(1);
716
    SET var := arg;
717
    SELECT arg, var;
718
END|
719

720
CREATE PROCEDURE p5(arg CHAR(2))
721
BEGIN
722
    DECLARE var CHAR(1);
723
    SET var := arg;
724
    SELECT arg, var;
725
END|
726

727
CREATE PROCEDURE p6(arg DECIMAL(2))
728
BEGIN
729
    DECLARE var DECIMAL(1);
730
    SET var := arg;
731
    SELECT arg, var;
732
END|
733

734
delimiter ;|
735

736
--error ER_WARN_DATA_OUT_OF_RANGE
737
CALL p1();
738
--error ER_WARN_DATA_OUT_OF_RANGE
739
CALL p2();
740
--error ER_DATA_TOO_LONG
741
CALL p3();
742

743
--error ER_DATA_TOO_LONG
744
CALL p4('aaa'); 
745
--error ER_DATA_TOO_LONG
746
CALL p5('aa');
747
--error ER_WARN_DATA_OUT_OF_RANGE
748
CALL p6(10);
749

750
#
751
# Cleanup.
752
#
753

754
## SET @@sql_mode = 'ansi';
755
set @@sql_mode = "PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY";
756

757
DROP PROCEDURE p1;
758
DROP PROCEDURE p2;
759
DROP PROCEDURE p3;
760

761
DROP PROCEDURE p4;
762
DROP PROCEDURE p5;
763
DROP PROCEDURE p6;
764

765
###########################################################################
766
#
767
# Test case for BUG#9078: STORED PROCDURE: Decimal digits are not displayed
768
# when we use DECIMAL datatype.
769
#
770
###########################################################################
771

772
--echo
773
--echo ---------------------------------------------------------------
774
--echo BUG#9078
775
--echo ---------------------------------------------------------------
776
--echo
777

778
#
779
# Prepare.
780
#
781

782
--disable_warnings
783
DROP PROCEDURE IF EXISTS p1;
784
--enable_warnings
785

786
#
787
# Test case.
788
#
789

790
delimiter |;
791
CREATE PROCEDURE p1 (arg DECIMAL(64,2))
792
BEGIN
793
  DECLARE var DECIMAL(64,2);
794

795
  SET var = arg;
796
  SELECT var;
797
END|
798
delimiter ;|
799

800
CALL p1(1929);
801
CALL p1(1929.00);
802
CALL p1(1929.003);
803

804
#
805
# Cleanup.
806
#
807

808
DROP PROCEDURE p1;
809

810
###########################################################################
811
#
812
# Test case for BUG#8768: Functions: For any unsigned data type, -ve values can
813
# be passed and returned.
814
#
815
# TODO: there is a bug here -- the function created in ANSI mode should not
816
# throw errors instead of warnings if called in TRADITIONAL mode.
817
#
818
###########################################################################
819

820
--echo
821
--echo ---------------------------------------------------------------
822
--echo BUG#8768
823
--echo ---------------------------------------------------------------
824
--echo
825

826
#
827
# Prepare.
828
#
829

830
--disable_warnings
831
DROP FUNCTION IF EXISTS f1;
832
--enable_warnings
833

834
#
835
# Test case.
836
#
837

838
# Create a function in ANSI mode.
839

840
delimiter |;
841
CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT
842
BEGIN
843
  RETURN arg;
844
END|
845
delimiter ;|
846
# mysql8.0 会报错
847
--error 1264
848
SELECT f1(-2500);
849

850
# Call in TRADITIONAL mode the function created in ANSI mode.
851

852
## SET @@sql_mode = 'traditional';
853
SET @@sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES';
854

855
# TODO: a warning should be emitted here.
856
## --error ER_WARN_DATA_OUT_OF_RANGE
857
--error 1264
858
SELECT f1(-2500);
859

860
# Recreate the function in TRADITIONAL mode.
861

862
DROP FUNCTION f1;
863

864
delimiter |;
865
CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT
866
BEGIN
867
  RETURN arg;
868
END|
869
delimiter ;|
870

871
## --error ER_WARN_DATA_OUT_OF_RANGE
872
--error 1264
873
SELECT f1(-2500);
874

875
#
876
# Cleanup.
877
#
878

879
## SET @@sql_mode = 'ansi';
880
set @@sql_mode = "PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY";
881

882
DROP FUNCTION f1;
883

884
###########################################################################
885
#
886
# Test case for BUG#8769: Functions: For Int datatypes, out of range values can
887
# be passed and returned.
888
#
889
# TODO: there is a bug here -- the function created in ANSI mode should not
890
# throw errors instead of warnings if called in TRADITIONAL mode.
891
#
892
###########################################################################
893

894
--echo
895
--echo ---------------------------------------------------------------
896
--echo BUG#8769
897
--echo ---------------------------------------------------------------
898
--echo
899

900
#
901
# Prepare.
902
#
903

904
--disable_warnings
905
DROP FUNCTION IF EXISTS f1;
906
--enable_warnings
907

908
#
909
# Test case.
910
#
911

912
# Create a function in ANSI mode.
913

914
delimiter |;
915
CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT
916
BEGIN
917
  RETURN arg;
918
END|
919
delimiter ;|
920

921
#mysql 8.0报错
922
--error 1264
923
SELECT f1(8388699);
924

925
# Call in TRADITIONAL mode the function created in ANSI mode.
926

927
## SET @@sql_mode = 'traditional';
928
SET @@sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES';
929

930
# TODO: a warning should be emitted here.
931
## --error ER_WARN_DATA_OUT_OF_RANGE
932
--error 1264
933
SELECT f1(8388699);
934

935
# Recreate the function in TRADITIONAL mode.
936

937
DROP FUNCTION f1;
938

939
delimiter |;
940
CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT
941
BEGIN
942
  RETURN arg;
943
END|
944
delimiter ;|
945

946
## --error ER_WARN_DATA_OUT_OF_RANGE
947
--error 1264
948
SELECT f1(8388699);
949

950
#
951
# Cleanup.
952
#
953

954
## SET @@sql_mode = 'ansi';
955
set @@sql_mode = "PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY";
956

957
DROP FUNCTION f1;
958

959
###########################################################################
960
#
961
# Test case for BUG#8702: Stored Procedures: No Error/Warning shown for
962
# inappropriate data type matching.
963
#
964
###########################################################################
965

966
--echo
967
--echo ---------------------------------------------------------------
968
--echo BUG#8702
969
--echo ---------------------------------------------------------------
970
--echo
971

972
#
973
# Prepare.
974
#
975

976
--disable_warnings
977
DROP PROCEDURE IF EXISTS p1;
978
DROP TABLE IF EXISTS t1;
979
--enable_warnings
980

981
#
982
# Test case.
983
#
984

985
CREATE TABLE t1(col VARCHAR(255));
986

987
INSERT INTO t1(col) VALUES('Hello, world!');
988

989
delimiter |;
990
CREATE PROCEDURE p1()
991
BEGIN
992
  DECLARE sp_var INTEGER;
993

994
  SELECT col FROM t1 LIMIT 1 INTO sp_var;
995
  SET @user_var = sp_var;
996

997
  SELECT sp_var;
998
  SELECT @user_var;
999
END|
1000
delimiter ;|
1001

1002
CALL p1();
1003

1004
#
1005
# Cleanup.
1006
#
1007

1008
DROP PROCEDURE p1;
1009
DROP TABLE t1;
1010

1011
###########################################################################
1012
#
1013
# Test case for BUG#12903: upper function does not work inside a function.
1014
#
1015
###########################################################################
1016

1017
--echo
1018
--echo ---------------------------------------------------------------
1019
--echo BUG#12903
1020
--echo ---------------------------------------------------------------
1021
--echo
1022

1023
#
1024
# Prepare.
1025
#
1026

1027
--disable_warnings
1028
DROP FUNCTION IF EXISTS f1;
1029
DROP TABLE IF EXISTS t1;
1030
--enable_warnings
1031

1032
#
1033
# Test case.
1034
#
1035

1036
CREATE TABLE t1(txt VARCHAR(255));
1037

1038
delimiter |;
1039
CREATE FUNCTION f1(arg VARCHAR(255)) RETURNS VARCHAR(255)
1040
BEGIN
1041
  DECLARE v1 VARCHAR(255);
1042
  DECLARE v2 VARCHAR(255);
1043

1044
  SET v1 = CONCAT(LOWER(arg), UPPER(arg));
1045
  SET v2 = CONCAT(LOWER(v1), UPPER(v1));
1046

1047
  INSERT INTO t1 VALUES(v1), (v2);
1048

1049
  RETURN CONCAT(LOWER(arg), UPPER(arg));
1050
END|
1051
delimiter ;|
1052

1053
SELECT f1('_aBcDe_');
1054

1055
SELECT * FROM t1;
1056

1057
#
1058
# Cleanup.
1059
#
1060

1061
DROP FUNCTION f1;
1062
DROP TABLE t1;
1063

1064
###########################################################################
1065
#
1066
# Test case for BUG#13808: ENUM type stored procedure parameter accepts
1067
# non-enumerated data.
1068
#
1069
###########################################################################
1070

1071
## --echo
1072
## --echo ---------------------------------------------------------------
1073
## --echo BUG#13808
1074
## --echo ---------------------------------------------------------------
1075
## --echo
1076

1077
#
1078
# Prepare.
1079
#
1080

1081
## --disable_warnings
1082
## DROP PROCEDURE IF EXISTS p1;
1083
## DROP PROCEDURE IF EXISTS p2;
1084
## DROP FUNCTION IF EXISTS f1;
1085
## --enable_warnings
1086

1087
#
1088
# Test case.
1089
#
1090
## delimiter |;
1091
## 
1092
## CREATE PROCEDURE p1(arg ENUM('a', 'b'))
1093
## BEGIN
1094
##   SELECT arg;
1095
## END|
1096
## 
1097
## CREATE PROCEDURE p2(arg ENUM('a', 'b'))
1098
## BEGIN
1099
##   DECLARE var ENUM('c', 'd') DEFAULT arg;
1100
## 
1101
##   SELECT arg, var;
1102
## END|
1103
## 
1104
## CREATE FUNCTION f1(arg ENUM('a', 'b')) RETURNS ENUM('c', 'd')
1105
## BEGIN
1106
##   RETURN arg;
1107
## END|
1108
## 
1109
## delimiter ;|
1110
## 
1111
## CALL p1('c');
1112
## 
1113
## CALL p2('a');
1114
## 
1115
## SELECT f1('a');
1116

1117
#
1118
# Cleanup.
1119
#
1120

1121
## DROP PROCEDURE p1;
1122
## DROP PROCEDURE p2;
1123
## DROP FUNCTION f1;
1124

1125
###########################################################################
1126
#
1127
# Test case for BUG#13909: Varchar Stored Procedure Parameter always BINARY
1128
# string (ignores CHARACTER SET).
1129
#
1130
###########################################################################
1131

1132
--echo
1133
--echo ---------------------------------------------------------------
1134
--echo BUG#13909
1135
--echo ---------------------------------------------------------------
1136
--echo
1137

1138
#
1139
# Prepare.
1140
#
1141

1142
--disable_warnings
1143
DROP PROCEDURE IF EXISTS p1;
1144
DROP PROCEDURE IF EXISTS p2;
1145
--enable_warnings
1146

1147
#
1148
# Test case.
1149
#
1150

1151
delimiter |;
1152

1153
CREATE PROCEDURE p1(arg VARCHAR(255))
1154
BEGIN
1155
  SELECT CHARSET(arg);
1156
END|
1157

1158
CREATE PROCEDURE p2(arg VARCHAR(255) CHARACTER SET UTF8)
1159
BEGIN
1160
    SELECT CHARSET(arg);
1161
END|
1162

1163
delimiter ;|
1164

1165
CALL p1('t');
1166
CALL p1(_UTF8 't');
1167

1168

1169
CALL p2('t');
1170
## CALL p2(_LATIN1 't');
1171

1172
#
1173
# Cleanup.
1174
#
1175

1176
DROP PROCEDURE p1;
1177
DROP PROCEDURE p2;
1178

1179
###########################################################################
1180
#
1181
# Test case for BUG#14188: BINARY variables have no 0x00 padding.
1182
#
1183
###########################################################################
1184

1185
--echo
1186
--echo ---------------------------------------------------------------
1187
--echo BUG#14188
1188
--echo ---------------------------------------------------------------
1189
--echo
1190

1191
#
1192
# Prepare.
1193
#
1194

1195
--disable_warnings
1196
DROP PROCEDURE IF EXISTS p1;
1197
--enable_warnings
1198

1199
#
1200
# Test case.
1201
#
1202

1203
delimiter |;
1204
CREATE PROCEDURE p1(arg1 BINARY(2), arg2 VARBINARY(2))
1205
BEGIN
1206
  DECLARE var1 BINARY(2) DEFAULT 0x41;
1207
  DECLARE var2 VARBINARY(2) DEFAULT 0x42;
1208

1209
  SELECT HEX(arg1), HEX(arg2);
1210
  SELECT HEX(var1), HEX(var2);
1211
END|
1212
delimiter ;|
1213

1214
CALL p1(0x41, 0x42);
1215

1216
#
1217
# Cleanup.
1218
#
1219

1220
DROP PROCEDURE p1;
1221

1222
###########################################################################
1223
#
1224
# Test case for BUG#15148: Stored procedure variables accept non-scalar values.
1225
#
1226
###########################################################################
1227

1228
--echo
1229
--echo ---------------------------------------------------------------
1230
--echo BUG#15148
1231
--echo ---------------------------------------------------------------
1232
--echo
1233

1234
#
1235
# Prepare.
1236
#
1237

1238
--disable_warnings
1239
DROP PROCEDURE IF EXISTS p1;
1240
DROP TABLE IF EXISTS t1;
1241
--enable_warnings
1242

1243
#
1244
# Test case.
1245
#
1246

1247
CREATE TABLE t1(col1 TINYINT, col2 TINYINT);
1248

1249
INSERT INTO t1 VALUES(1, 2), (11, 12);
1250

1251
delimiter |;
1252
CREATE PROCEDURE p1(arg TINYINT)
1253
BEGIN
1254
  SELECT arg;
1255
END|
1256
delimiter ;|
1257

1258
--error ER_OPERAND_COLUMNS
1259
CALL p1((1, 2));
1260

1261
## --error ER_OPERAND_COLUMNS
1262
## oceanbase not support yet
1263
## CALL p1((SELECT * FROM t1 LIMIT 1));
1264

1265
## --error ER_OPERAND_COLUMNS
1266
## oceanbase not support yet
1267
## CALL p1((SELECT col1, col2 FROM t1 LIMIT 1));
1268

1269
#
1270
# Cleanup.
1271
#
1272

1273
DROP PROCEDURE p1;
1274
DROP TABLE t1;
1275

1276
###########################################################################
1277
#
1278
# Test case for BUG#13613: substring function in stored procedure.
1279
#
1280
###########################################################################
1281

1282
--echo
1283
--echo ---------------------------------------------------------------
1284
--echo BUG#13613
1285
--echo ---------------------------------------------------------------
1286
--echo
1287

1288
#
1289
# Prepare.
1290
#
1291

1292
--disable_warnings
1293
DROP PROCEDURE IF EXISTS p1;
1294
DROP FUNCTION IF EXISTS f1;
1295
--enable_warnings
1296

1297
#
1298
# Test case.
1299
#
1300

1301
delimiter |;
1302

1303
CREATE PROCEDURE p1(x VARCHAR(50))
1304
BEGIN
1305
  SET x = SUBSTRING(x, 1, 3);
1306
  SELECT x;
1307
END|
1308

1309
CREATE FUNCTION f1(x VARCHAR(50)) RETURNS VARCHAR(50)
1310
BEGIN
1311
  RETURN SUBSTRING(x, 1, 3);
1312
END|
1313

1314
delimiter ;|
1315

1316
CALL p1('abcdef');
1317

1318
SELECT f1('ABCDEF');
1319

1320
#
1321
# Cleanup.
1322
#
1323

1324
DROP PROCEDURE p1;
1325
DROP FUNCTION f1;
1326

1327
###########################################################################
1328
#
1329
# Test case for BUG#13665: concat with '' produce incorrect results in SP.
1330
#
1331
###########################################################################
1332

1333
--echo
1334
--echo ---------------------------------------------------------------
1335
--echo BUG#13665
1336
--echo ---------------------------------------------------------------
1337
--echo
1338

1339
#
1340
# Prepare.
1341
#
1342

1343
--disable_warnings
1344
DROP FUNCTION IF EXISTS f1;
1345
--enable_warnings
1346

1347
#
1348
# Test case.
1349
#
1350

1351
delimiter |;
1352
CREATE FUNCTION f1() RETURNS VARCHAR(20000)
1353
BEGIN
1354
  DECLARE var VARCHAR(2000);
1355

1356
  SET var = '';
1357
  SET var = CONCAT(var, 'abc');
1358
  SET var = CONCAT(var, '');
1359

1360
  RETURN var;
1361
END|
1362
delimiter ;|
1363

1364
SELECT f1();
1365

1366
#
1367
# Cleanup.
1368
#
1369

1370
DROP FUNCTION f1;
1371

1372

1373
#
1374
# Bug#17226: Variable set in cursor on first iteration is assigned
1375
# second iterations value
1376
#
1377
# The problem was in incorrect handling of local variables of type
1378
# TEXT (BLOB).
1379
#
1380
--disable_warnings
1381
DROP PROCEDURE IF EXISTS p1;
1382
--enable_warnings
1383

1384
delimiter |;
1385
CREATE PROCEDURE p1()
1386
BEGIN
1387
  DECLARE v_char VARCHAR(255);
1388
  DECLARE v_text VARCHAR(255) DEFAULT '';
1389

1390
  SET v_char = 'abc';
1391

1392
  SET v_text = v_char;
1393

1394
  SET v_char = 'def';
1395

1396
  SET v_text = concat(v_text, '|', v_char);
1397

1398
  SELECT v_text;
1399
END|
1400
delimiter ;|
1401

1402
CALL p1();
1403

1404
DROP PROCEDURE p1;
1405

1406
#
1407
# Bug #27415 Text Variables in stored procedures
1408
# If the SP varible was also referenced on the right side
1409
# the result was corrupted.
1410
#
1411
DELIMITER |;
1412

1413
--disable_warnings
1414
DROP PROCEDURE IF EXISTS bug27415_text_test|
1415
DROP PROCEDURE IF EXISTS bug27415_text_test2|
1416
--enable_warnings
1417

1418
CREATE PROCEDURE bug27415_text_test(entity_id_str_in VARCHAR(255))
1419
BEGIN
1420
    DECLARE str_remainder VARCHAR(255);
1421

1422
    SET str_remainder = entity_id_str_in;
1423

1424
    select 'before substr', str_remainder;
1425
    SET str_remainder = SUBSTRING(str_remainder, 3);
1426
    select 'after substr', str_remainder;
1427
END|
1428

1429
CREATE PROCEDURE bug27415_text_test2(entity_id_str_in VARCHAR(255))
1430
BEGIN
1431
    DECLARE str_remainder VARCHAR(255);
1432
    DECLARE str_remainder2 VARCHAR(255);
1433
 
1434
    SET str_remainder2 = entity_id_str_in;
1435
    select 'before substr', str_remainder2;
1436
    SET str_remainder = SUBSTRING(str_remainder2, 3);
1437
    select 'after substr', str_remainder;
1438
END|
1439

1440
CALL bug27415_text_test('a,b,c')|
1441
CALL bug27415_text_test('a,b,c')|
1442
CALL bug27415_text_test2('a,b,c')|
1443
CALL bug27415_text_test('a,b,c')|
1444

1445
DROP PROCEDURE bug27415_text_test|
1446
DROP PROCEDURE bug27415_text_test2|
1447

1448
DELIMITER ;|
1449

1450
# End of 5.0 tests.
1451

1452
#
1453
# Bug #26277 User variable returns one type in SELECT @v and other for CREATE as SELECT @v
1454
#
1455
--disable_warnings
1456
drop function if exists f1;
1457
drop table if exists t1;
1458
--enable_warnings
1459

1460
delimiter |;
1461
create function f1() returns int 
1462
begin
1463
 if @a=1 then set @b='abc';
1464
 else set @b=1;
1465
 end if;
1466
 set @a=1;
1467
 return 0;
1468
end|
1469

1470
create table t1 (a int)|
1471
insert into t1 (a) values (1), (2)|
1472

1473
set @b:='test'|
1474
set @a=0|
1475
select f1(), @b from t1|
1476

1477
set @b=1|
1478
set @a=0|
1479
select f1(), @b from t1|
1480

1481
delimiter ;|
1482

1483
drop function f1;
1484
drop table t1;
1485
# End of 5.1 tests.
1486

1487

1488
###########################################################################
1489
#
1490
# Test case for BUG#28299: To-number conversion warnings work
1491
# differenly with CHAR and VARCHAR sp variables
1492
#
1493
###########################################################################
1494

1495
--echo
1496
--echo ---------------------------------------------------------------
1497
--echo BUG#28299
1498
--echo ---------------------------------------------------------------
1499
--echo
1500

1501
DELIMITER |;
1502
CREATE PROCEDURE ctest()
1503
BEGIN
1504
  DECLARE i CHAR(16);
1505
  DECLARE j INT;
1506
  SET i= 'string';
1507
  SET j= 1 + i;
1508
END|
1509
DELIMITER ;|
1510

1511
CALL ctest();
1512
DROP PROCEDURE ctest;
1513

1514
DELIMITER |;
1515
CREATE PROCEDURE vctest()
1516
BEGIN
1517
  DECLARE i VARCHAR(16);
1518
  DECLARE j INT;
1519
  SET i= 'string';
1520
  SET j= 1 + i;
1521
END|
1522
DELIMITER ;|
1523

1524
CALL vctest();
1525
DROP PROCEDURE vctest;
1526