###################################################
#                                                 #
#  Functions within VIEWs                         #
#                                                 #
###################################################
# 2007-11-09 HHunger enabled all disabled parts belonging to fixed bugs.
# 2006-12-08 mleich  Maintenance + refinements
# 2005-09-14 mleich  Create this test
#
# 0.  Some notes about this test:
# #################################################################
#
# 0.1   This test is unfinished and incomplete, but already useful.
# -----------------------------------------------------------------
# 0.1.1 There will be architectural changes in future.
#       The long sequences with
#           let $col_type= <column to use>;
#           --source suite/funcs_1/views/<file containing the
#                                         select with function>
#       per every column type do not look very smart.
#
#       Ugly combinations of functions and data types must be also checked,
#       because an accidental typo like assigning a string column to an
#       numeric parameter could happen and should not result in a server crash.
#
#       Maybe it is better to change the architecture of this test in such
#       a way:
#       1. A generator script (this one or written in Perl or SP language)
#          generates an prototype of the the final testscript.
#       2. Some manual adjustments because of open bugs (depending on
#          storage engine or function) might be needed (I hope not :)
#       3. The final testscript is pushed to the other regression testscripts.
#       Advantage:    The analysis of bugs, extension and maintenance of this
#                     test will be much easier.
#       Disadvantage: Much redundant code within the final testscript,
#                     but the maintenance of the redundant code will be done
#                     by the script generator.
#
# 0.1.2 The behaviour of SELECTs on VIEWs could be affected by the SQL mode
#       which was valid during VIEW creation time. This means some variations
#       of the SQL mode are needed.
# 0.1.3 There are much more functions to be tested.
# 0.1.4 The result sets of some CAST sub testcases with ugly function parameter
#       column data type combinations must be discussed.
#
#
# 0.2   How to valuate the test results:
# ---------------------------------------------------------------------------
#       Due to the extreme "greedy bug hunting" architecture (combinatorics
#       + heavy use of sourced scripts) of the following tests, there will be
#       - no abort of the test execution, when one statements gets an return
#         code != 0 (The sub testcases are independend.)
#         But statements, which do not make sense like SELECT on non existent
#         VIEW will be omitted. This decreases the amount of useless output.
#       - a file with expected results, which might contain incorrect server
#         responses
#         There are open bugs and I cannot omit statements which reveal these
#         bugs.
#         But there will be a special messages within the protocol files.
#         Example:
#            "Attention: CAST --> SIGNED INTEGER
#            The file with expected results suffers from Bug 5913";
#            means, the file with expected results contains result sets which
#            are known to be wrong.
#            "Attention: The last <whatever> failed"
#            means, a statement which should be successful (bugfree MySQL)
#            failed.
#
#       "Passed" : The behaviour of your MySQL version does not differ from the
#                  version used to generate the files with expected results.
#                  Known bugs affecting these tests could be retrieved by
#                  grep "Attention" r/<testcase>.result .
#
#       "Failed" : The behaviour of your MySQL version differs from the version
#                  used to generate the files with expected results.
#                  These differences could be result of bug fixes or new bugs.
#                  Please compare r/<testcase>.reject and r/<testcase>.result .
#
#      The test will abort if one of the basic preparation statement fails
#      (except ALTER TABLE ADD ...).
#
#
# 0.3   How to debug sub testcases with "unexpected" results:
# ---------------------------------------------------------------------------
#       1. Please execute this test and copy the "reject" file to a save place.
#          Search within the "reject" file for the sub testcase (the SELECT)
#          with the suspicious result set or server response.
#          Now all t1_values records are preloaded.
#       2. Start the server without the initial cleanup of databases etc.
#          This preserves the content of the table t1_values, which
#          might be needed for replaying the situation.
#          Example:
#            ./mysql-test-run.pl   --socket=var/tmp/master.sock --start-dirty
#       3. Issue the statements needed by using "mysql" or "mysqltest".
#
#       Maybe an internal routine of this test fails. Please ask me (mleich) or
#       enable the logging of auxiliary queries and try to analyze the
#       problem.
#
#
# 0.4   How to extend the number of functions to be checked:
# ---------------------------------------------------------------------------
#       Please jump to the paragraphs of the basic preparations
#       1. Extend t1_values with the columns you need
#       2. Insert some predefinded rows
#       3. Add the SELECTs with function which should be used within VIEWs
#          and
#          records which should be used dedicated to the SELECT above
#
#
# 0.5  How to alter the internal routines of this test:
# ---------------------------------------------------------------------------
#      Please try to achieve a state where the protocol
#      - contains ALL statements, which are needed to replay a problem within
#        the field of functions within VIEWs
#      - does not contain too much auxiliary statements, which are not needed
#        to replay a problem (--> "--disable_query_log")
#      Example:
#        Needed for replay:
#        - DROP/CREATE TABLE t1_values
#        - INSERT of records into t1_values
#        - DROP/CREATE/SELECT/SHOW VIEW v1
#        - SELECT direct on base table
#        Not needed for replay:
#        - SET @<uservariable> = <value>
#        - DROP/CREATE/INSERT/SELECT TABLE t1_selects, t1_modes
#
#
# 0.6  A trick for checking results
# ---------------------------------------------------------------------------
#   Standard setting for common execution of this test:
    let $simple_select_result= 1;
    let $view_select_result=   1;
#      The implementation of some additional function tests may lead to
#      masses of result sets, which have to be checked. The result sets of
#      the simple selects on the base table must equal the result sets of the
#      queries on the VIEWs. This step could be made more comfortable by
#      1. Edit this file to
#              let $simple_select_result= 1;
#              let $view_select_result=   0;
#         Please execute this test.
#         The script will omit CREATE/DROP/SHOW/SELECT on VIEW.
#         The "reject" file contains only the simple select result sets.
#      2. Edit this file to
#              let $simple_select_result= 0;
#              let $view_select_result=   1;
#         Please execute this test.
#         The script will work with the VIEWs, but omit the simple selects.
#         The "reject" file contains the view select result sets.
#      3. Compare the "reject" files of 1. and 2. within a graphical diff tool.
#
#

# For TIME to DATETIME/TIMESTAMP conversion:
SET timestamp=UNIX_TIMESTAMP('2010-01-01');

--disable_warnings
DROP TABLE IF EXISTS t1_selects, t1_modes, t1_values;
DROP VIEW  IF EXISTS v1;
--enable_warnings

--disable_query_log
# Storage for the SELECTs to be used for the VIEW definition
# Attention: my_select must be no too small because a statement like
#    SELECT LOAD_FILE(< file in MYSQLTEST_VARDIR >)
#    AS my_col,
#    id FROM t1_values';
#    might be a very long
# Bug#38427 "Data too long" ... tests "<ENGINE>_func_view" fail
# CREATE TABLE t1_selects
#   my_select VARCHAR(1000) NOT NULL,
eval CREATE TABLE t1_selects
(
   id BIGINT AUTO_INCREMENT,
   my_select VARCHAR(700) NOT NULL,
   disable_result ENUM('Yes','No') NOT NULL default 'No',
   PRIMARY KEY(id),
   UNIQUE (my_select)
) ENGINE=$type;
# ) ENGINE=MyISAM;

# MODES to be checked
# CREATE TABLE t1_modes
eval CREATE TABLE t1_modes
(
   id BIGINT AUTO_INCREMENT,
   my_mode VARCHAR(200) NOT NULL,
   PRIMARY KEY(id),
   UNIQUE (my_mode)
) ENGINE=$type;
# ) ENGINE=MyISAM;
--enable_query_log

# The table to be used in the FROM parts of the SELECTs
--replace_result $type <engine_to_be_tested>
eval CREATE TABLE t1_values
(
   id BIGINT AUTO_INCREMENT,
   select_id BIGINT,
   PRIMARY KEY(id)
) ENGINE = $type;

##### BEGIN      Basic preparations      #######################################
#
# 1. Extend t1_values with the columns you need
#    - the column name must show the data type
#    - do not add NOT NULL columns
#    - do not worry if the intended column data type is not
#      available for some storage engines
#    Please do not forget to assign values for the new columns (paragraph 2.).
--disable_abort_on_error
ALTER TABLE t1_values ADD my_char_30        CHAR(30);
ALTER TABLE t1_values ADD my_varchar_1000   VARCHAR(1000);
ALTER TABLE t1_values ADD my_binary_30      BINARY(30);
ALTER TABLE t1_values ADD my_varbinary_1000 VARBINARY(1000);
ALTER TABLE t1_values ADD my_datetime       DATETIME;
ALTER TABLE t1_values ADD my_date           DATE;
ALTER TABLE t1_values ADD ts_dummy          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
ALTER TABLE t1_values ADD my_timestamp      TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE t1_values ADD my_time           TIME;
ALTER TABLE t1_values ADD my_year           YEAR;
ALTER TABLE t1_values ADD my_bigint         BIGINT;
ALTER TABLE t1_values ADD my_double         DOUBLE;
ALTER TABLE t1_values ADD my_decimal        DECIMAL(64,30);
SET sql_mode = default;
--enable_abort_on_error

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

#
# 2.  Insert some predefinded rows
#     Predefined rows
#     - t1_values.select_id IS NULL
#     - will be selected by every SELECT with function to be tested
#     - have to be inserted when sql_mode = 'traditional' is valid, because
#       we do not want to start with "illegal/unexpected/..." values.
#       Such experiments should be done in other testcases.
#     Please be careful
#     - modifying column values of predefined rows they might change many
#       result sets
#     - additional predefined rows should be really useful for the majority of
#       all sub testcases, since they blow up all result sets.
SET sql_mode = 'traditional';
#
# 2.1 record -- everything to NULL
INSERT INTO t1_values SET id = 0;
#
# 2.2 record -- everything to "minimum"
#     numbers, date/time types -> minimum of range
#     strings, blobs, binaries -> ''
#     FIXME enum, set ??
INSERT INTO t1_values SET
       my_char_30 = '',
       my_varchar_1000 = '',
       my_binary_30 = '',
       my_varbinary_1000 = '',
       my_datetime = '0001-01-01 00:00:00',
       my_date = '0001-01-01',
       my_timestamp = '1970-01-01 03:00:01',
       my_time = '-838:59:59',
       my_year = '1901',
       my_bigint = -9223372036854775808,
       my_decimal = -9999999999999999999999999999999999.999999999999999999999999999999 ,
       my_double = -1.7976931348623E+308;
# shortened due to bug#32285
#       my_double = -1.7976931348623157E+308;
#
# 2.3 record -- everything to "maximum"
#     numbers, date/time types -> maximum of range
#     strings, blobs, binaries -> '<- full length of used data type>'
#     FIXME enum, set ??
INSERT INTO t1_values SET
       my_char_30 = '<--------30 characters------->',
       my_varchar_1000 = CONCAT('<---------1000 characters',
                                RPAD('',965,'-'),'--------->'),
       my_binary_30 = '<--------30 characters------->',
       my_varbinary_1000 = CONCAT('<---------1000 characters',
                                RPAD('',965,'-'),'--------->'),
       my_datetime = '9999-12-31 23:59:59',
       my_date = '9999-12-31',
       my_timestamp = '2038-01-01 02:59:59',
       my_time = '838:59:59',
       my_year = 2155,
       my_bigint = 9223372036854775807,
       my_decimal = +9999999999999999999999999999999999.999999999999999999999999999999 ,
       my_double = 1.7976931348623E+308;
# shortened due to bug#32285
#       my_double = -1.7976931348623157E+308;
#
# 2.4 record -- everything to "magic" value if available or
#               other interesting value
#     numbers   -> 0
#     strings, blobs, binaries -> not full length of used data type, "exotic"
#                                 characters and preceding and trailing spaces
#     FIXME enum, set ??
INSERT INTO t1_values SET
       my_char_30 = ' ---äÖüß@µ*$-- ',
       my_varchar_1000 = ' ---äÖüß@µ*$-- ',
       my_binary_30 = ' ---äÖüß@µ*$-- ',
       my_varbinary_1000 = ' ---äÖüß@µ*$-- ',
       my_datetime = '2004-02-29 23:59:59',
       my_date = '2004-02-29',
       my_timestamp = '2004-02-29 23:59:59',
       my_time = '13:00:00',
       my_year = 2000,
       my_bigint = 0,  	
       my_decimal = 0.0,
       my_double = 0;
#
# 2.5 record -- everything to "harmless" value if available
#     numbers -> -1 (logical)
#     strings, blobs, binaries -> '-1' useful for numeric functions
#     FIXME enum, set ??
INSERT INTO t1_values SET
       my_char_30 = '-1',
       my_varchar_1000 = '-1',
       my_binary_30 = '-1',
       my_varbinary_1000 = '-1',
       my_datetime = '2005-06-28 10:00:00',
       my_date = '2005-06-28',
       my_timestamp = '2005-06-28 10:00:00',
       my_time = '10:00:00',
       my_year = 2005,
       my_bigint = -1,
       my_decimal = -1.000000000000000000000000000000,
       my_double = -0.1E+1;

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

#
# 3.  Add the SELECTs with function which should be used within VIEWs
#     and
#     records which should be used dedicated to the SELECT above
#     - Please avoid WHERE clauses
#     - Include the PRIMARY KEY ("id") of the base table t1_values into the
#       select column list
#     - Include the base table column used as function parameter into the
#       select column list, because it is much easier to check the results
#     - Do not forget to escape single quotes
#       Example:
#        SET @my_select = 'SELECT sqrt(my_bigint), my_bigint, id FROM t1_values'
#        SET @my_select = 'SELECT CONCAT(\'A\',my_char_30), id FROM t1_values';
#     - Statements, which reveal open crashing bugs MUST be disabled.
#     - Result sets must not contain data, which might differ between boxes
#       executing this test.
#       Example: current time, absolute path to some files ...
#     - Please derive the functions to be checked from the MySQL manual
#       and use the same order. This means copy the the function names as
#       comment into this test and start to implement a testcase for your
#       most preferred function.
#       This method avoids that we forget a function and gives a better
#       overview.
#
#     If you have the time to check the result sets do the insert of the
#     SELECT with function via:
#           eval SET @my_select =
#           '<your SELECT>';
#           --source suite/funcs_1/views/fv1.inc
#     fv1.inc sets t1_selects.disable_result to 'No' and the effect will be,
#     that the result set will be logged.
#
#     If you do not have the time to check the result sets do the insert of the
#     SELECT with function via:
#           eval SET @my_select =
#           '<your SELECT>';
#           --source suite/funcs_1/views/fv2.inc
#     fv2.inc sets t1_selects.disable_result to 'Yes' and the effect will be,
#     that the result set will be not logged.
#     This should be only a temporary solution and it does not remove the
#     need to check the server return codes.
#     That means even when we do not have the time to check the correctness of
#     the result sets, we check if
#        - SELECT <function> or
#        - SELECT * FROM <VIEW with function>
#     crash the server or get suspicious server responses.
#
#     - the SELECTs will be applied to the rows defined here (3.) +
#       all predefined rows (2.)
#     - the rows dedicated to the SELECT should contain especially interesting
#       column values or combinations of column values, which are not covered
#       by the predefined records
#     - The records have to be inserted when sql_mode = 'traditional' is valid.
#     - Please do not insert records with column values where the allowed
#       range is exceeded. The SQL mode 'traditional' will prevent such
#       inserts. Such experiments should be done in other tests, because
#       they inflate the current test without giving an appropriate value.
#
#       Example:
#          The function to be tested is "sqrt".
#          The minimum, maximum, default and NULL value are covered by the
#          predefined rows.
#          A value where sqrt(<value>) = <integer value> in strict mathematics
#          would be of interest.
#          --> Add a record with my_bigint = 4
#          --> Just for fun my_bigint = -25 .
#
# Some internal stuff
PREPARE ins_sel_with_result FROM "INSERT INTO t1_selects SET my_select = @my_select,
disable_result = 'No'" ;
PREPARE ins_sel_no_result   FROM "INSERT INTO t1_selects SET my_select = @my_select,
disable_result = 'Yes'" ;
SET sql_mode = 'traditional';
# --disable_query_log

let $col_type= my_bigint;
# Example:
#    eval SET @my_select = 'SELECT CONCAT(''A'', $col_type), $col_type, id';
eval SET @my_select = 'SELECT sqrt($col_type), $col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
# Content of suite/funcs_1/views/fv1.inc :
#    --disable_query_log
#    EXECUTE ins_sel_with_result;
#    SET @select_id = LAST_INSERT_ID();
#    --enable_query_log

eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = 4;
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = -25;
# SELECT * FROM t1_values;

# 1.    Cast Functions and Operators
# 1.1   CAST
#
# Note(mleich): I guess the CAST routines are used in many other functions.
#               Therefore check also nearly all "ugly" variants like
#               CAST(<string composed of non digits> AS DECIMAL) here.
#
#            suite/funcs_1/views/fv_cast.inc contains
#                SELECT CAST($col_type AS $target_type), ...
#
#
# 1.1.1. CAST --> BINARY
--echo ##### 1.1.1. CAST --> BINARY
let $target_type= BINARY;
#
let $col_type= my_char_30;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_varchar_1000;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_binary_30;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_varbinary_1000;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_bigint;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_decimal;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_double;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_datetime;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_date;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_timestamp;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_time;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_year;
--source suite/funcs_1/views/fv_cast.inc


# 1.1.2. CAST --> CHAR
--echo ##### 1.1.2. CAST --> CHAR
let $target_type= CHAR;
#
let $col_type= my_char_30;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_varchar_1000;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_binary_30;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_varbinary_1000;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_bigint;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_decimal;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_double;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_datetime;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_date;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_timestamp;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_time;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_year;
--source suite/funcs_1/views/fv_cast.inc


# 1.1.3. CAST --> DATE
--echo ##### 1.1.3. CAST --> DATE
let $target_type= DATE;
#
let $col_type= my_char_30;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '2005-06-27';
let $col_type= my_varchar_1000;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '2005-06-27';
let $col_type= my_binary_30;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '2005-06-27';
let $col_type= my_varbinary_1000;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '2005-06-27';
let $col_type= my_bigint;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = 20050627;
let $col_type= my_double;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = +20.050627E+6;
let $col_type= my_datetime;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_date;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_timestamp;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_time;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_year;
--source suite/funcs_1/views/fv_cast.inc


# 1.1.4. CAST --> DATETIME
--echo ##### 1.1.4. CAST --> DATETIME
let $target_type= DATETIME;
#
let $col_type= my_char_30;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '2005-06-27 17:58';
let $col_type= my_varchar_1000;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '2005-06-27 17:58';
let $col_type= my_binary_30;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '2005-06-27 17:58';
let $col_type= my_varbinary_1000;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '2005-06-27 17:58';
let $col_type= my_bigint;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = 200506271758;
let $col_type= my_double;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = +0.0200506271758E+13;
let $col_type= my_datetime;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_date;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_timestamp;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_time;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_year;
--source suite/funcs_1/views/fv_cast.inc


# 1.1.5. CAST --> TIME
--echo ##### 1.1.5. CAST --> TIME
let $target_type= TIME;
#
let $col_type= my_char_30;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '1 17:58';
let $col_type= my_varchar_1000;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '1 17:58';
let $col_type= my_binary_30;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '1 17:58';
let $col_type= my_varbinary_1000;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '1 17:58';
let $col_type= my_bigint;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = 1758;
let $col_type= my_double;
# Bug#12440: CAST(data type DOUBLE AS TIME) strange results;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = +1.758E+3;
let $col_type= my_datetime;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_date;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_timestamp;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_time;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_year;
--source suite/funcs_1/views/fv_cast.inc


# 1.1.6. CAST --> DECIMAL
--echo ##### 1.1.6. CAST --> DECIMAL
# Set the following to (37,2) since the default was changed to (10,0) - OBN
let $target_type= DECIMAL(37,2);
#
let $col_type= my_char_30;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '-3333.3333';
let $col_type= my_varchar_1000;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '-3333.3333';
let $col_type= my_binary_30;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '-3333.3333';
let $col_type= my_varbinary_1000;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = '-3333.3333';
let $col_type= my_bigint;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_decimal;
--source suite/funcs_1/views/fv_cast.inc
# Bug#13349: CAST(1.0E+300 TO DECIMAL) returns wrong result + diff little/big endian;
let $col_type= my_double;
--source suite/funcs_1/views/fv_cast.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            $col_type = -0.33333333E+4;
let $col_type= my_datetime;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_date;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_timestamp;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_time;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_year;
--source suite/funcs_1/views/fv_cast.inc


# 1.1.7. CAST --> SIGNED INTEGER
--echo ##### 1.1.7. CAST --> SIGNED INTEGER
let $target_type= SIGNED INTEGER;
#
let $message=
"Attention: CAST --> SIGNED INTEGER
            Bug#5913 Traditional mode: BIGINT range not correctly delimited
            Status: To be fixed later";
--source include/show_msg80.inc
let $col_type= my_char_30;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_varchar_1000;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_binary_30;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_varbinary_1000;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_bigint;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_decimal;
--source suite/funcs_1/views/fv_cast.inc
# Bug #13344: CAST(1E+300 TO signed int) on little endian CPU, wrong result;
let $col_type= my_double;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_datetime;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_date;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_timestamp;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_time;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_year;
--source suite/funcs_1/views/fv_cast.inc


# 1.1.8. CAST --> UNSIGNED INTEGER
--echo ##### 1.1.8. CAST --> UNSIGNED INTEGER
let $target_type= UNSIGNED INTEGER;
#
let $message=
"Attention: CAST --> UNSIGNED INTEGER
            The file with expected results suffers from Bug 5913";
--source include/show_msg80.inc
let $col_type= my_char_30;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_varchar_1000;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_binary_30;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_varbinary_1000;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_bigint;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_decimal;
--source suite/funcs_1/views/fv_cast.inc
let $message= some statements disabled because of
Bug#5913 Traditional mode: BIGINT range not correctly delimited;
--source include/show_msg80.inc
# Bug#8663 cant use bgint unsigned as input to cast
let $col_type= my_double;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_datetime;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_date;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_timestamp;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_time;
--source suite/funcs_1/views/fv_cast.inc
let $col_type= my_year;
--source suite/funcs_1/views/fv_cast.inc


# 1.2.   BINARY
#        Manual: BINARY str is a shorthand for CAST(str AS BINARY).
#        Therefore we do not test it here in the moment.
#        FIXME: Add testcases for str in CHAR and VARCHAR only.


# 1.3    CONVERT(expr USING transcoding_name)
#
# 1.3.1  CONVERT(expr USING utf8)
let $target_charset= utf8;
#
let $col_type= my_char_30;
eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_varchar_1000;
eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_binary_30;
eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_varbinary_1000;
eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
#
# 1.3.2  CONVERT(expr USING koi8r)
let $target_charset= koi8r;
let $col_type= my_char_30;
eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_varchar_1000;
eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_binary_30;
eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_varbinary_1000;
eval SET @my_select = 'SELECT CONVERT($col_type USING $target_charset),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc


# 2.     Control Flow Functions
# 2.1.   CASE value WHEN [compare-value] THEN result [WHEN ...] [ELSE result]
#        END                 or
#        CASE WHEN [condition] THEN result [WHEN ...] [ELSE result] END
#
#        FIXME: to be implemented
#
# 2.2.   IF(expr1,expr2,expr3)
#           expr1 is TRUE when (expr1 <> 0 and expr1 <> NULL) is fulfilled
#
# 2.2.1  IF(expr1,expr2,expr3) with expr1 = <column>
#
#        Note(mleich): Strings, which do not contain a number -> FALSE
#
#        suite/funcs_1/views/fv_if1.inc contains
#                SELECT IF($col_type, 'IS TRUE', 'IS NOT TRUE'), ...
#
let $col_type= my_char_30;
--source suite/funcs_1/views/fv_if1.inc
#
let $col_type= my_varchar_1000;
--source suite/funcs_1/views/fv_if1.inc
#
let $col_type= my_binary_30;
--source suite/funcs_1/views/fv_if1.inc
#
let $col_type= my_varbinary_1000;
--source suite/funcs_1/views/fv_if1.inc
#
let $col_type= my_bigint;
--source suite/funcs_1/views/fv_if1.inc
#
let $col_type= my_decimal;
--source suite/funcs_1/views/fv_if1.inc
#
let $col_type= my_double;
--source suite/funcs_1/views/fv_if1.inc
#
let $col_type= my_datetime;
--source suite/funcs_1/views/fv_if1.inc
#
let $col_type= my_date;
--source suite/funcs_1/views/fv_if1.inc
#
let $col_type= my_timestamp;
--source suite/funcs_1/views/fv_if1.inc
#
let $col_type= my_time;
--source suite/funcs_1/views/fv_if1.inc
#
let $col_type= my_year;
--source suite/funcs_1/views/fv_if1.inc


# 2.2.2  IF(expr1,expr2,expr3) with expr1 != <column>
#
#        suite/funcs_1/views/fv_if2.inc contains
#                SELECT IF($col_type IS NULL, 'IS     NULL', 'IS NOT NULL'), ...
#
# Note(mleich): July 2005
#               IF($col_type IS NULL, ...) is mapped to a VIEW definition
#                  create ... view ... as
#                  select if(isnull(`test`.`t1`.`f1`),_latin1'IS     NULL',
#                  _latin1'IS NOT NULL'),...
#
# Bug#11689 success on Create view .. IF(col1 IS NULL,...), col2 ; but SELECT fails
let $col_type= my_char_30;
--source suite/funcs_1/views/fv_if2.inc
#
let $col_type= my_varchar_1000;
--source suite/funcs_1/views/fv_if2.inc
#
let $col_type= my_binary_30;
--source suite/funcs_1/views/fv_if2.inc
#
let $col_type= my_varbinary_1000;
--source suite/funcs_1/views/fv_if2.inc
#
let $col_type= my_bigint;
--source suite/funcs_1/views/fv_if2.inc
#
let $col_type= my_decimal;
--source suite/funcs_1/views/fv_if2.inc
#
let $col_type= my_double;
--source suite/funcs_1/views/fv_if2.inc
#
let $col_type= my_datetime;
--source suite/funcs_1/views/fv_if2.inc
#
let $col_type= my_date;
--source suite/funcs_1/views/fv_if2.inc
#
let $col_type= my_timestamp;
--source suite/funcs_1/views/fv_if2.inc
#
let $col_type= my_time;
--source suite/funcs_1/views/fv_if2.inc
#
let $col_type= my_year;
--source suite/funcs_1/views/fv_if2.inc


# 2.3.   IFNULL(expr1,expr2)
#           If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2.
#
#        suite/funcs_1/views/fv_ifnull.inc contains
#                SELECT IFNULL($col_type, 'IS_NULL'), ....
#        FIXME: The mixup of non string column values
#               and the string 'IS    NULL' within the first column of the
#               result table is extreme ugly.
#               CAST(IFNULL($col_type, 'IS_NULL') AS CHAR) looks better, but
#               it has the disadvantage, that it involves CAST as additional
#               function.
#
let $col_type= my_char_30;
--source suite/funcs_1/views/fv_ifnull.inc
#
let $col_type= my_varchar_1000;
--source suite/funcs_1/views/fv_ifnull.inc
#
let $col_type= my_binary_30;
--source suite/funcs_1/views/fv_ifnull.inc
#
let $col_type= my_varbinary_1000;
--source suite/funcs_1/views/fv_ifnull.inc
#
let $col_type= my_bigint;
--source suite/funcs_1/views/fv_ifnull.inc
#
let $col_type= my_decimal;
--source suite/funcs_1/views/fv_ifnull.inc
#
let $col_type= my_double;
--source suite/funcs_1/views/fv_ifnull.inc
#
let $col_type= my_datetime;
--source suite/funcs_1/views/fv_ifnull.inc
#
let $col_type= my_date;
--source suite/funcs_1/views/fv_ifnull.inc
#
let $col_type= my_timestamp;
--source suite/funcs_1/views/fv_ifnull.inc
#
let $col_type= my_time;
--source suite/funcs_1/views/fv_ifnull.inc
#
let $col_type= my_year;
--source suite/funcs_1/views/fv_ifnull.inc


# 2.4.    NULLIF(expr1,expr2)
#            Returns NULL if expr1 = expr2 is true, else returns expr1.
#            This is the same as
#                 CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
#
#         FIXME: to be implemented
#


# 3.      String Functions
# 3.1.    ASCII(str)
# 3.2.    BIN(N)
#         FIXME: to be implemented
#
# 3.3.    BIT_LENGTH(str)
#            Returns the length of the string str in bits.
#
let $col_type= my_char_30;
eval SET @my_select = 'SELECT BIT_LENGTH($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_varchar_1000;
eval SET @my_select = 'SELECT BIT_LENGTH($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_binary_30;
eval SET @my_select = 'SELECT BIT_LENGTH($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_varbinary_1000;
eval SET @my_select = 'SELECT BIT_LENGTH($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc


# 3.4.    CHAR(N,...)
# 3.5.    CHAR_LENGTH(str)
# 3.6     CHARACTER_LENGTH(str)
#            CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().
# 3.7.    COMPRESS(string_to_compress)
# 3.8.    CONCAT(str1,str2,...)
# 3.9.    CONCAT_WS(separator,str1,str2,...)
# 3.10.   CONV(N,from_base,to_base)
# 3.11.   ELT(N,str1,str2,str3,...)
# 3.12.   EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
# 3.13.   FIELD(str,str1,str2,str3,...)
# 3.14.   FIND_IN_SET(str,strlist)
# 3.15.   HEX(N_or_S
# 3.16.   INSERT(str,pos,len,newstr)
# 3.17.   INSTR(str,substr)
#            This is the same as the two-argument form of LOCATE(),
#            except that the arguments are swapped.
#            The majority of the testcases should be made with LOCATE().
#            Therefore test only one example here.
let $col_type= my_char_30;
eval SET @my_select = 'SELECT INSTR($col_type, ''char''),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc


# 3.18.   LCASE(str)
#         LCASE() is a synonym for LOWER().
#            The majority of the testcases should be made with LOWER().
#            Therefore test only one example here.
let $col_type= my_varchar_1000;
eval SET @my_select = 'SELECT LCASE($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc


# 3.19.   LEFT(str,len)
#            Returns the leftmost len characters from the string str.
let $col_type= my_char_30;
eval SET @my_select =
'SELECT LEFT($col_type, 2), $col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_varchar_1000;
eval SET @my_select =
'SELECT LEFT($col_type, 2), $col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_binary_30;
eval SET @my_select =
'SELECT LEFT($col_type, 2), $col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_varbinary_1000;
eval SET @my_select =
'SELECT LEFT($col_type, 2), $col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
# Bug#11728 string function LEFT, strange undocumented behaviour, strict mode
# Bug#10963 LEFT string function returns wrong result with large length
let $col_type= my_bigint;
eval SET @my_select =
'SELECT LEFT(''AaBbCcDdEeFfGgHhIiJjÄäÜüÖö'', $col_type), $col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
let $col_type= my_decimal;
eval SET @my_select =
'SELECT LEFT(''AaBbCcDdEeFfGgHhIiJjÄäÜüÖö'', $col_type), $col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc
# Bug#10963 LEFT string function returns wrong result with large length
let $col_type= my_double;
eval SET @my_select =
'SELECT LEFT(''AaBbCcDdEeFfGgHhIiJjÄäÜüÖö'', $col_type), $col_type, id FROM t1_values';
--source suite/funcs_1/views/fv1.inc

# 3.20.   LENGTH(str)
let $col_type= my_char_30;
eval SET @my_select = 'SELECT LENGTH($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_varchar_1000;
eval SET @my_select = 'SELECT LENGTH($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_binary_30;
eval SET @my_select = 'SELECT LENGTH($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_varbinary_1000;
eval SET @my_select = 'SELECT LENGTH($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc


# 3.21.   LOAD_FILE(file_name)
#            Reads the file and returns the file contents as a string.
#            If the file doesn't exist or cannot be read ... ,
#            the function returns NULL.
# SELECT LOADFILE
--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
eval SET @my_select =
'SELECT LOAD_FILE(''$MYSQLTEST_VARDIR/std_data/funcs_1/load_file.txt'')
           AS my_col,
        id FROM t1_values';
--source suite/funcs_1/views/fv1.inc


# 3.22.    LOCATE(substr,str) , LOCATE(substr,str,pos)
let $col_type= my_char_30;
eval SET @my_select = 'SELECT LOCATE(''char'', $col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_varchar_1000;
eval SET @my_select = 'SELECT LOCATE(''char'', $col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_binary_30;
eval SET @my_select = 'SELECT LOCATE(''char'', $col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_varbinary_1000;
eval SET @my_select = 'SELECT LOCATE(''char'', $col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
#------------------------------------------------------
let $col_type1= my_char_30;
# against all other
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type1 ),
$col_type1, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type2= my_varchar_1000;
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
$col_type1, $col_type2 id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type2= my_binary_30;
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
$col_type1, $col_type2 id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type2= my_varbinary_1000;
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
$col_type1, $col_type2 id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
#------------------------------------------------------
let $col_type1= my_varchar_1000;
# against all other
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type1 ),
$col_type1, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type2= my_char_30;
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
$col_type1, $col_type2 id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type2= my_binary_30;
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
$col_type1, $col_type2 id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type2= my_varbinary_1000;
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
$col_type1, $col_type2 id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
#------------------------------------------------------
let $col_type1= my_binary_30;
# against all other
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type1 ),
$col_type1, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type2= my_char_30;
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
$col_type1, $col_type2 id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type2= my_varchar_1000;
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
$col_type1, $col_type2 id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type2= my_varbinary_1000;
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
$col_type1, $col_type2 id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
#------------------------------------------------------
let $col_type1= my_varbinary_1000;
# against all other
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type1 ),
$col_type1, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type2= my_char_30;
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
$col_type1, $col_type2 id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type2= my_varchar_1000;
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
$col_type1, $col_type2 id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type2= my_binary_30;
eval SET @my_select = 'SELECT LOCATE($col_type1, $col_type2 ),
$col_type1, $col_type2 id FROM t1_values';
--source suite/funcs_1/views/fv2.inc

# FIXME How to test exotic or interesting substr values like NULL, '', ' '
#       without getting too much result rows
# FIXME Testcases with LOCATE(substr,str,pos)
let $col_type= my_char_30;
eval SET @my_select = 'SELECT LOCATE(''-'', $col_type, 3),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_varchar_1000;
eval SET @my_select = 'SELECT LOCATE(''-'', $col_type, 3),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_binary_30;
eval SET @my_select = 'SELECT LOCATE(''-'', $col_type, 3),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_varbinary_1000;
eval SET @my_select = 'SELECT LOCATE(''-'', $col_type, 3),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
#--------------------------------------------------------
let $col_type= my_bigint;
eval SET @my_select = 'SELECT LOCATE(''-'', '' - -ABC'', $col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_double;
eval SET @my_select = 'SELECT LOCATE(''-'', '' - -ABC'', $col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_decimal;
eval SET @my_select = 'SELECT LOCATE(''-'', '' - -ABC'', $col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc


# 3.23.    LOWER(str)
let $col_type= my_char_30;
eval SET @my_select = 'SELECT LOWER($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_varchar_1000;
eval SET @my_select = 'SELECT LOWER($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_binary_30;
eval SET @my_select = 'SELECT LOWER($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_varbinary_1000;
eval SET @my_select = 'SELECT LOWER($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc


# 3.24.    LPAD(str,len,padstr)
# 3.25.    LTRIM(str)
let $col_type= my_char_30;
eval SET @my_select = 'SELECT LTRIM($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_varchar_1000;
eval SET @my_select = 'SELECT LTRIM($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_binary_30;
eval SET @my_select = 'SELECT LTRIM($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc
let $col_type= my_varbinary_1000;
eval SET @my_select = 'SELECT LTRIM($col_type),
$col_type, id FROM t1_values';
--source suite/funcs_1/views/fv2.inc


# 3.26.    MAKE_SET(bits,str1,str2,...)
# .....
#         FIXME: to be implemented

################################################################################
# Please do not add SELECTs and interesting records after this line.           #
# These last SELECTs are mostly for checking the testcase code itself.         #
################################################################################
eval SET @my_select =
       'SELECT CONCAT(''A'',my_char_30), my_char_30, id FROM t1_values';       #
--source suite/funcs_1/views/fv1.inc
#
eval SET @my_select = 'SELECT my_char_30, id FROM t1_values';                  #
--source suite/funcs_1/views/fv2.inc
eval INSERT INTO t1_values SET select_id = @select_id,
            my_char_30 = 'Viana do Castelo';
################################################################################
SET sql_mode = '';                                                             #

##### END        Basic preparations      #######################################


let $message= "# The basic preparations end and the main test starts here";
--source include/show_msg80.inc

--disable_ps_protocol

##### The tests start here #####################################################

# Determine the number of different SELECTs to be checked
--disable_query_log
SELECT COUNT(*) INTO @num_selects FROM t1_selects;
--enable_query_log
# Debug statement
# SELECT @num_selects AS "number of SELECTS:";

--disable_abort_on_error
let $select_id= `SELECT @num_selects`;
while ($select_id)
{
    # Determine the SELECT
    --disable_query_log
    eval SELECT my_select, disable_result INTO @my_select, @disable_result
         FROM t1_selects WHERE id = $select_id;
    let $run_no_result= `SELECT @disable_result = 'Yes'`;
    --enable_query_log
    # Debug statement
    # SELECT @my_select AS "SELECT:";
    let $my_select= `SELECT @my_select`;

    let $run0= 0;
    if ($view_select_result)
    {
       # Create the VIEW
       --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
       eval CREATE VIEW v1 AS $my_select;
       --disable_query_log
       eval set @got_errno= $mysql_errno ;
       let $run0= `SELECT @got_errno = 0`;
       --enable_query_log
       if (!$run0)
       {
          --echo
          --echo Attention: The last CREATE VIEW failed
          --echo
       }
    }

    # FIXME           The loop over the modes will start here.

    if ($simple_select_result)
    {
       # Simple SELECT on the base table of the VIEW for comparison

       if ($run_no_result)
       {
          --disable_result_log
       }
       --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
       eval $my_select
            WHERE select_id = $select_id OR select_id IS NULL order by id;
       if ($run_no_result)
       {
          --enable_result_log
       }
       if ($mysql_errno)
       {
          --echo
          --echo Attention: The last SELECT on the base table failed
          --echo
       }
    }

    # $run0 is 1, if CREATE VIEW was successful.
    # That means SHOW CREATE VIEW/SELECT/DROP should be executed.
    if ($run0)
    {
       # Check the CREATE VIEW statement
       --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
       SHOW CREATE VIEW v1;
       if ($mysql_errno)
       {
          --echo
          --echo Attention: The last SHOW CREATE VIEW failed
          --echo
       }

       # Maybe a Join is faster
       if ($run_no_result)
       {
          --disable_result_log
       }
       eval SELECT v1.* FROM v1
            WHERE v1.id IN (SELECT id FROM t1_values
                            WHERE select_id = $select_id OR select_id IS NULL) order by id;
       if ($run_no_result)
       {
          --enable_result_log
       }
       if ($mysql_errno)
       {
          --echo
          --echo Attention: The last SELECT from VIEW failed
          --echo
       }

       DROP VIEW v1;
    }

    # FIXME         The loop over the modes will end here.

    # Produce two empty lines as separator between different SELECTS
    # to be tested.
    --echo
    --echo

    dec $select_id ;
}

--enable_ps_protocol

DROP TABLE t1_selects, t1_modes, t1_values;