262 lines
8.8 KiB
PHP
262 lines
8.8 KiB
PHP
################################################################################
|
|
# 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;
|