################################################################################
# t/partition_supported_sql_funcs_main.inc                                     #
#                                                                              #
# Purpose:                                                                     #
#  Tests which SQL functions are allowed in partinioning clauses.              #
#                                                                              #
#                                                                              #
#------------------------------------------------------------------------------#
# Original Author: HH                                                          #
# Original Date: 2006-11-22                                                    #
# Change Author:                                                               #
# Change Date:                                                                 #
# Change:                                                                      #
#                                                                              #
# This test uses a test frame (partition_supported_sql_funcs.inc) for every    #
# SQL function allowed in the partitioning parts of CREATE and ALTE TABLE.     #
# The variales represent the                                                   #
# - SQL function isself with a column (sqlfunc) and a literal (valsqlsunc),    #
# - the type of the column (coltype),                                          #
# - a file with test values of the coltype (infile) and                        # 
# - single test values (val1 to val4).                                         #
# The test frame includes CREATE/ALTER TABLE and some access statements.       #
# Column types are int, float(7,4), char(1), date and time depending on the    #
# SQL function. The test frame uses the include file                           # 
# "part_supported_sql_funcs_delete.inc" testing the deletion of           #
# partitions.                                                                  #
# The CREATE and ALTER TABLE statement do not cover the complete partitions    #
# functions, but will ashure that the SQL functions are basically working.     #
################################################################################


let $sqlfunc = abs(col1);
let $valsqlfunc = abs(15);
let $coltype = int;
let $infile = part_supported_sql_funcs_int_int.inc;
let $val1 =  5 ;
let $val2 = 13 ;
let $val3 = 17 ;
let $val4 = 15 ;
--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = mod(col1,10);
let $valsqlfunc = mod(15,10);
let $coltype = int;
let $infile = part_supported_sql_funcs_int_int.inc;
let $val1 =  5;
let $val2 = 19;
let $val3 = 17;
let $val4 = 15 ;
--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = day(col1);
let $valsqlfunc = day('2006-12-21');
let $coltype = date;
let $infile = part_supported_sql_funcs_int_date.inc;
let $val1 =  '2006-02-03';
let $val2 =  '2006-01-17';
let $val3 =  '2006-01-25';
let $val4 =  '2006-02-05';
--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = dayofmonth(col1);
let $valsqlfunc = dayofmonth('2006-12-24');
let $coltype = date;
let $infile = part_supported_sql_funcs_int_date.inc;
let $val1 =  '2006-02-03';
let $val2 =  '2006-01-17';
let $val3 =  '2006-01-25';
let $val4 =  '2006-02-05';
--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = dayofweek(col1);
let $valsqlfunc = dayofweek('2006-12-24');
let $coltype = date;
let $infile = part_supported_sql_funcs_int_date.inc;
let $val1 =  '2006-01-03';
let $val2 =  '2006-02-17';
let $val3 =  '2006-01-25';
let $val4 =  '2006-02-05';
--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = dayofyear(col1);
let $valsqlfunc = dayofyear('2006-12-25');
let $coltype = date;
let $infile = part_supported_sql_funcs_int_date.inc;
let $val1 =  '2006-01-03';
let $val2 =  '2006-01-17';
let $val3 =  '2006-02-25';
let $val4 =  '2006-02-05';
--source suite/parts/inc/partition_supported_sql_funcs.inc
# Disabled after fixing bug#54483.
#let $coltype = char(30);
#--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = extract(month from col1);
let $valsqlfunc = extract(year from '1998-11-23');
let $coltype = date;
let $infile = part_supported_sql_funcs_int_date.inc;
let $val1 =  '2006-01-03';
let $val2 =  '2006-02-17';
let $val3 =  '2006-01-25';
let $val4 =  '2006-02-05';
--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = hour(col1);
let $valsqlfunc = hour('18:30');
let $coltype = time;
let $infile = part_supported_sql_funcs_int_time.inc;
let $val1 =  '09:09';
let $val2 =  '14:30';
let $val3 =  '21:59';
let $val4 =  '10:30';
--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = microsecond(col1);
let $valsqlfunc = microsecond('10:30:10.000010');
let $coltype = time(6);
let $infile = part_supported_sql_funcs_int_time.inc;
let $val1 =  '09:09:15.000002';
let $val2 =  '04:30:01.000018';
let $val3 =  '00:59:22.000024';
let $val4 =  '05:30:34.000037';
--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = minute(col1);
let $valsqlfunc = minute('18:30');
let $coltype = time;
let $val1 =  '09:09:15';
let $val2 =  '14:30:45';
let $val3 =  '21:59:22';
let $val4 =  '10:24:23';
--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = second(col1);
let $valsqlfunc = second('18:30:14');
let $coltype = time;
let $infile = part_supported_sql_funcs_int_time.inc;
let $val1 =  '09:09:09';
let $val2 =  '14:30:20';
let $val3 =  '21:59:22';
let $val4 =  '10:22:33';
--source suite/parts/inc/partition_supported_sql_funcs.inc
# second(non_time_col) is disabled after bug#54483.
#let $coltype = char(30);
#--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = month(col1);
let $valsqlfunc = month('2006-10-14');
let $coltype = date;
let $infile = part_supported_sql_funcs_int_date.inc;
let $val1 =  '2006-01-03';
let $val2 =  '2006-12-17';
let $val3 =  '2006-05-25';
let $val4 =  '2006-11-06';
--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = quarter(col1);
let $valsqlfunc = quarter('2006-10-14');
let $coltype = date;
let $infile = part_supported_sql_funcs_int_date.inc;
let $val1 =  '2006-01-03';
let $val2 =  '2006-12-17';
let $val3 =  '2006-09-25';
let $val4 =  '2006-07-30';
--source suite/parts/inc/partition_supported_sql_funcs.inc

# time_to_sec() is now a double function, so it can't be portioned upon
--disable_parsing
let $sqlfunc = time_to_sec(col1)-(time_to_sec(col1)-20);
let $valsqlfunc = time_to_sec('18:30:14')-(time_to_sec('17:59:59'));
let $coltype = time;
let $infile = part_supported_sql_funcs_int_time.inc;
let $val1 =  '09:09:15';
let $val2 =  '14:30:45';
let $val3 =  '21:59:22';
let $val4 =  '10:33:11';
--source suite/parts/inc/partition_supported_sql_funcs.inc
--enable_parsing

# to_days(non_date_col) is disabled after bug#54483.
#let $sqlfunc = to_days(col1)-to_days('2006-01-01');
#let $valsqlfunc = to_days('2006-02-02')-to_days('2006-01-01');
#let $coltype = date;
#let $infile = part_supported_sql_funcs_int_date.inc;
#let $val1 =  '2006-02-03';
#let $val2 =  '2006-01-17';
#let $val3 =  '2006-01-25';
#let $val4 =  '2006-02-06';
#--source suite/parts/inc/partition_supported_sql_funcs.inc

# to_days(non_date_col) is disabled after bug#54483.
# DATEDIFF() is implemented as (TO_DAYS(d1) - TO_DAYS(d2))
#let $sqlfunc = datediff(col1, '2006-01-01');
#let $valsqlfunc = datediff('2006-02-02', '2006-01-01');
#let $coltype = date;
#let $infile = part_supported_sql_funcs_int_date.inc;
#let $val1 =  '2006-02-03';
#let $val2 =  '2006-01-17';
#let $val3 =  '2006-01-25';
#let $val4 =  '2006-02-06';
#--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = weekday(col1);
let $valsqlfunc = weekday('2006-10-14');
let $coltype = date;
let $infile = part_supported_sql_funcs_int_date.inc;
let $val1 =  '2006-12-03';
let $val2 =  '2006-11-17';
let $val3 =  '2006-05-25';
let $val4 =  '2006-02-06';
--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = year(col1)-1990;
let $valsqlfunc = year('2005-10-14')-1990;
let $coltype = date;
let $infile = part_supported_sql_funcs_int_date.inc;
let $val1 =  '1996-01-03';
let $val2 =  '2000-02-17';
let $val3 =  '2004-05-25';
let $val4 =  '2002-02-15';
--source suite/parts/inc/partition_supported_sql_funcs.inc

let $sqlfunc = yearweek(col1)-200600;
let $valsqlfunc = yearweek('2006-10-14')-200600;
let $coltype = date;
let $infile = part_supported_sql_funcs_int_date.inc;
let $val1 =  '2006-01-03';
let $val2 =  '2006-08-17';
let $val3 =  '2006-03-25';
let $val4 =  '2006-11-15';
--source suite/parts/inc/partition_supported_sql_funcs.inc