mysql56/part__blocked__sql__funcs__main_8inc_source.html

################################################################################

# t/partition_blocked_sql_funcs_main.inc                                       #

#                                                                              #

# Purpose:                                                                     #

#  Tests around sql functions                                                  #

#                                                                              #

#                                                                              #

#------------------------------------------------------------------------------#

# Original Author: HH                                                          #

# Original Date: 2006-11-22                                                    #

# Change Author:                                                               #

# Change Date:                                                                 #

# Change:                                                                      #

################################################################################


--echo -------------------------------------------------------------------------

--echo ---   All SQL functions should be rejected, otherwise BUG (see 18198)

--echo -------------------------------------------------------------------------


let $sqlfunc = ascii(col1);

let $valsqlfunc = ascii('a');

let $coltype = char(30);

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = ord(col1);

let $valsqlfunc = ord('a');

let $coltype = char(30);

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = greatest(col1,15);

let $valsqlfunc = greatest(1,15);

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = isnull(col1);

let $valsqlfunc = isnull(15);

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = least(col1,15);

let $valsqlfunc = least(15,30);

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = case when col1>15 then 20 else 10 end;

let $valsqlfunc = case when 1>30 then 20 else 15 end;

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = ifnull(col1,30);

let $valsqlfunc = ifnull(1,30);

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = nullif(col1,30);

let $valsqlfunc = nullif(1,30);

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = bit_length(col1);

let $valsqlfunc = bit_length(255);

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc

let $coltype = char(30);

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = char_length(col1);

let $valsqlfunc = char_length('a');

#let $coltype = int;

#--source suite/parts/inc/partition_blocked_sql_funcs.inc

let $coltype = char(30);

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = character_length(col1);

let $valsqlfunc = character_length('a');

let $coltype = char(30)

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = find_in_set(col1,'1,2,3,4,5,6,7,8,9');

let $valsqlfunc = find_in_set('i','a,b,c,d,e,f,g,h,i');

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc

let $coltype = char(30);

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = instr(col1,'acb');

let $valsqlfunc = instr('i','a,b,c,d,e,f,g,h,i');

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc

let $coltype = char(30);

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = length(col1);

let $valsqlfunc = length('a,b,c,d,e,f,g,h,i');

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = locate('a',col1);

let $valsqlfunc = locate('i','a,b,c,d,e,f,g,h,i');

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc

let $coltype = char(30);

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = octet_length(col1);

let $valsqlfunc = octet_length('a,b,c,d,e,f,g,h,i');

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = position('a' in col1);

let $valsqlfunc = position('i' in 'a,b,c,d,e,f,g,h,i');

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc

let $coltype = char(30);

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = strcmp(col1,'acb');

let $valsqlfunc = strcmp('i','a,b,c,d,e,f,g,h,i');

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc

let $coltype = char(30);

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = crc32(col1);

let $valsqlfunc = crc32('a,b,c,d,e,f,g,h,i');

let $coltype = char(30);

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = round(col1);

let $valsqlfunc = round(15);

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = sign(col1);

let $valsqlfunc = sign(123);

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = period_add(col1,5);

let $valsqlfunc = period_add(9804,5);

let $coltype = datetime;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = period_diff(col1,col2);

let $valsqlfunc = period_diff(9809,199907);

let $coltype = datetime,col2 datetime;

--source suite/parts/inc/partition_blocked_sql_funcs.inc

let $coltype = int,col2 int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = timestampdiff(day,5,col1);

let $valsqlfunc = timestampdiff(YEAR,'2002-05-01','2001-01-01');

let $coltype = datetime;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


################################################################################

# After the fix for bug #42849 the server behavior does not fit into this test's

# architecture: for UNIX_TIMESTAMP() some of the queries in

# suite/parts/inc/partition_blocked_sql_funcs.inc will fail with a different

# error (ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR) and some will succeed where

################################################################################

#let $sqlfunc = unix_timestamp(col1);

#let $valsqlfunc = unix_timestamp ('2002-05-01');

#let $coltype = date;

#--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = week(col1);

let $valsqlfunc = week('2002-05-01');

let $coltype = datetime;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = weekofyear(col1);

let $valsqlfunc = weekofyear('2002-05-01');

let $coltype = datetime;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = cast(col1 as signed);

let $valsqlfunc = cast(123 as signed);

let $coltype = varchar(30);

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = convert(col1,unsigned);

let $valsqlfunc = convert(123,unsigned);

let $coltype = varchar(30);

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = col1 | 20;

let $valsqlfunc = 10 | 20;

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = col1 & 20;

let $valsqlfunc = 10 & 20;

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = col1 ^ 20;

let $valsqlfunc = 10 ^ 20;

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = col1 << 20;

let $valsqlfunc = 10 << 20;

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = col1 >> 20;

let $valsqlfunc = 10 >> 20;

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = ~col1;

let $valsqlfunc = ~20;

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = bit_count(col1);

let $valsqlfunc = bit_count(20);

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


let $sqlfunc = inet_aton(col1);

let $valsqlfunc = inet_aton('192.168.1.1');

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


set @var =20;

let $sqlfunc = bit_length(col1)+@var-@var;

let $valsqlfunc = bit_length(20)+@var-@var;

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc


delimiter //;

create function getmaxsigned_t1(col int) returns int

begin

  declare done int default 0;

  declare v4 int;

  declare max int;

  declare cur1 cursor for

     select col from t1;

  declare continue handler for sqlstate '01000' set done = 1;

  declare continue handler for sqlstate '02000' set done = 1;

  open cur1;

  set max = 0;

  fetch cur1 into v4;

  wl_loop: WHILE NOT done DO

     fetch cur1 into v4;

     IF v4 > max

     then set max = v4;

     END IF;

  END WHILE wl_loop;

  close cur1;

  return max;

 end//

delimiter ;//


let $sqlfunc = getmaxsigned_t1(col1);

let $valsqlfunc = getmaxsigned(10);

let $coltype = int;

--source suite/parts/inc/partition_blocked_sql_funcs.inc

drop function if exists getmaxsigned_t1;