diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/views')
| -rw-r--r-- | mysql-test/suite/funcs_1/views/func_view.inc | 109 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/views/views_master.inc | 74 |
2 files changed, 107 insertions, 76 deletions
diff --git a/mysql-test/suite/funcs_1/views/func_view.inc b/mysql-test/suite/funcs_1/views/func_view.inc index 3bf9e96b332..4479db22e70 100644 --- a/mysql-test/suite/funcs_1/views/func_view.inc +++ b/mysql-test/suite/funcs_1/views/func_view.inc @@ -3,8 +3,7 @@ # Functions within VIEWs # # # ################################################### -# 2006-12-08 ML Maintenance + refinements -# 2005-09-14 ML Create this test +# 14.09.2005 ML let $message= ! Attention: The file with the expected results suffers from Bug#10713: mysqldump includes database in create view and referenced tables; @@ -69,7 +68,7 @@ Bug#10713: mysqldump includes database in create view and referenced tables; # 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"; +# The file with expected results suffers from Bug 5083 5913 9809"; # means, the file with expected results contains result sets which # are known to be wrong. # "Attention: The last <whatever> failed" @@ -188,7 +187,6 @@ CREATE TABLE t1_modes --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, @@ -418,8 +416,8 @@ 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 +# 1. Cast Functions and Operators +# 1.1. CAST # # Note(ML): I guess the CAST routines are used in many other functions. # Therefore check also nearly all "ugly" variants like @@ -589,10 +587,15 @@ let $col_type= my_bigint; 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; +let $message= some statements disabled because of +Bug#12440: CAST(data type DOUBLE AS TIME) strange results; +--source include/show_msg80.inc +if (0) +{ --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; @@ -628,11 +631,16 @@ 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 $message= some statements disabled because of +Bug#13349: CAST(1.0E+300 TO DECIMAL) returns wrong result + diff little/big endian; +--source include/show_msg80.inc +if (0) +{ 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; @@ -650,8 +658,11 @@ let $target_type= SIGNED INTEGER; # let $message= "Attention: CAST --> SIGNED INTEGER + The file with expected results suffers from + Bug#5083 Big integer values are inserted as negative into + decimal/string columns Bug#5913 Traditional mode: BIGINT range not correctly delimited - Status: To be fixed later"; + Both have the status: To be fixed later"; --source include/show_msg80.inc let $col_type= my_char_30; --source suite/funcs_1/views/fv_cast.inc @@ -665,9 +676,14 @@ 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 $message= some statements disabled because of +Bug #13344: CAST(1E+300 TO signed int) on little endian CPU, wrong result; +--source include/show_msg80.inc +if (0) +{ 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; @@ -685,7 +701,7 @@ let $target_type= UNSIGNED INTEGER; # let $message= "Attention: CAST --> UNSIGNED INTEGER - The file with expected results suffers from Bug 5913"; + The file with expected results suffers from Bug 5083 5913 9809"; --source include/show_msg80.inc let $col_type= my_char_30; --source suite/funcs_1/views/fv_cast.inc @@ -700,11 +716,10 @@ let $col_type= my_bigint; 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; +Bugs#8663: cant use bgint unsigned as input to cast; --source include/show_msg80.inc if (0) { -# Bugs#8663: cant use bgint unsigned as input to cast let $col_type= my_double; --source suite/funcs_1/views/fv_cast.inc } @@ -832,6 +847,11 @@ let $col_type= my_year; # select if(isnull(`test`.`t1`.`f1`),_latin1'IS NULL', # _latin1'IS NOT NULL'),... # +let $message= +"Attention: IF($col_type IS NULL, ... + The file with expected results suffers from + Bug#11689. successful CREATE VIEW but SELECT on view fails."; +--source include/show_msg80.inc # 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 @@ -1010,7 +1030,7 @@ eval SET @my_select = # let $message= "Attention: LEFT(''AaBbCcDdEeFfGgHhIiJjÄäÜüÖö'', <numeric column>) - The file with expected results suffers from Bug 10963" + The file with expected results suffers from Bug 10963 11728" and the testcases with length = BIGINT or DOUBLE column are deactivated, because there are 32/64 Bit differences; --source include/show_msg80.inc @@ -1064,10 +1084,8 @@ $col_type, id FROM t1_values'; # If the file doesn't exist or cannot be read ... , # the function returns NULL. # SELECT LOADFILE -# Prepare a file: -SELECT 'äÄ@' INTO OUTFILE '../tmp/func_view.dat'; eval SET @my_select = -'SELECT LOAD_FILE(''../tmp/func_view.dat''), id FROM t1_values'; +'SELECT LOAD_FILE(''../log/current_test''), id FROM t1_values'; --source suite/funcs_1/views/fv1.inc @@ -1291,13 +1309,14 @@ while ($select_id) --disable_query_log eval set @got_errno= $mysql_errno ; let $run0= `SELECT @got_errno = 0`; - --enable_query_log - if (!$run0) + let $print_warning= `SELECT @got_errno`; + if ($print_warning) { - --echo - --echo Attention: The last CREATE VIEW failed - --echo + SELECT 'Attention: The last CREATE VIEW failed ' AS "" + UNION + SELECT '' ; } + --enable_query_log } # FIXME The loop over the modes will start here. @@ -1311,17 +1330,21 @@ while ($select_id) --disable_result_log } eval $my_select - WHERE select_id = $select_id OR select_id IS NULL order by id; + WHERE select_id = $select_id OR select_id IS NULL; if ($run_no_result) { --enable_result_log } - if ($mysql_errno) + --disable_query_log + eval set @got_errno= $mysql_errno ; + let $print_warning= `SELECT @got_errno`; + if ($print_warning) { - --echo - --echo Attention: The last SELECT on the base table failed - --echo + SELECT 'Attention: The last SELECT on the base table failed' AS "" + UNION + SELECT '' ; } + --enable_query_log } # $run0 is 1, if CREATE VIEW was successful. @@ -1330,12 +1353,16 @@ while ($select_id) { # Check the CREATE VIEW statement SHOW CREATE VIEW v1; - if ($mysql_errno) + --disable_query_log + eval set @got_errno= $mysql_errno ; + let $print_warning= `SELECT @got_errno`; + if ($print_warning) { - --echo - --echo Attention: The last SHOW CREATE VIEW failed - --echo + SELECT 'Attention: The last SHOW CREATE VIEW failed' AS "" + UNION + SELECT '' ; } + --enable_query_log # Maybe a Join is faster if ($run_no_result) @@ -1344,17 +1371,21 @@ while ($select_id) } 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; + WHERE select_id = $select_id OR select_id IS NULL); if ($run_no_result) { --enable_result_log } - if ($mysql_errno) + --disable_query_log + eval set @got_errno= $mysql_errno ; + let $print_warning= `SELECT @got_errno`; + if ($print_warning) { - --echo - --echo Attention: The last SELECT from VIEW failed - --echo + SELECT 'Attention: The last SELECT from VIEW failed' AS "" + UNION + SELECT '' ; } + --enable_query_log DROP VIEW v1; } @@ -1363,11 +1394,11 @@ while ($select_id) # Produce two empty lines as separator between different SELECTS # to be tested. - --echo - --echo + --disable_query_log + SELECT '' AS ""; + --enable_query_log dec $select_id ; } DROP TABLE t1_selects, t1_modes, t1_values; ---exec rm $MYSQLTEST_VARDIR/tmp/func_view.dat diff --git a/mysql-test/suite/funcs_1/views/views_master.inc b/mysql-test/suite/funcs_1/views/views_master.inc index 0e3371bdb18..b06873af159 100644 --- a/mysql-test/suite/funcs_1/views/views_master.inc +++ b/mysql-test/suite/funcs_1/views/views_master.inc @@ -1772,7 +1772,7 @@ SELECT * FROM v1 ORDER BY f1; --enable_info # 1. The record to be inserted will be within the scope of the view. # But there is already a record with the PRIMARY KEY f1 = 2 . ---error ER_DUP_ENTRY_WITH_KEY_NAME +--error 1062 INSERT INTO v1 VALUES(2,'two'); # 2. The record to be inserted will be within the scope of the view. # There is no already existing record with the PRIMARY KEY f1 = 3 . @@ -1789,7 +1789,7 @@ SELECT * FROM v1 ORDER BY f1; # 1. The record to be updated is within the scope of the view # and will stay inside the scope. # But there is already a record with the PRIMARY KEY f1 = 2 . ---error ER_DUP_ENTRY_WITH_KEY_NAME +--error 1062 UPDATE v1 SET f1 = 2 WHERE f1 = 3; # 2. The record to be updated is within the scope of the view # and will stay inside the scope. @@ -1873,11 +1873,11 @@ DROP VIEW IF EXISTS test.v1; eval CREATE TABLE t1 (f1 ENUM('A', 'B', 'C') NOT NULL, f2 INTEGER) ENGINE = $engine_type; INSERT INTO t1 VALUES ('A', 1); -SELECT * FROM t1 order by f1, f2; +SELECT * FROM t1; CREATE VIEW v1 AS SELECT * FROM t1 WHERE f2 BETWEEN 1 AND 2 WITH CASCADED CHECK OPTION ; -SELECT * FROM v1 order by f1, f2; +SELECT * FROM v1; --enable_info # positive cases UPDATE v1 SET f2 = 2 WHERE f2 = 1; @@ -1885,7 +1885,7 @@ INSERT INTO v1 VALUES('B',2); --disable_info # Bug#11771: View over InnoDB table, wrong result SELECT on VIEW, # field->query_id wrong -SELECT * FROM v1 order by f1, f2; +SELECT * FROM v1; # negative cases --enable_info --error 1369 @@ -1895,7 +1895,7 @@ INSERT INTO v1 VALUES('B',3); --disable_info # Bug#11771: View over InnoDB table, wrong result SELECT on VIEW, # field->query_id wrong -SELECT * FROM v1 order by f1, f2; +SELECT * FROM v1; let $message= Testcase 3.3.1.49 ; @@ -3287,7 +3287,7 @@ DELETE FROM t1; # f1 gets the default 0, because we are in the native sql_mode INSERT INTO v1 SET f2 = 'ABC'; # f1 gets the default 0, but this value is already exists ---error ER_DUP_ENTRY_WITH_KEY_NAME +--error 1062 INSERT INTO v1 SET f2 = 'ABC'; SELECT * from t1; DELETE FROM t1; @@ -3375,7 +3375,7 @@ CREATE VIEW v1 AS SELECT f2, f3 FROM t1; # f1 gets the default 0, because we are in the native sql_mode INSERT INTO v1 SET f2 = 'ABC'; # f1 gets the default 0 and this value is already exists ---error ER_DUP_ENTRY_WITH_KEY_NAME +--error 1062 INSERT INTO v1 SET f2 = 'ABC'; SELECT * from t1; DELETE FROM t1; @@ -3838,8 +3838,8 @@ INSERT INTO v1 SET f1 = -1, f4 = 'ABC', report = 'v1 0'; # 0. Initial state DESCRIBE t1; DESCRIBE v1; -SELECT * FROM t1 order by f1, report; -SELECT * FROM v1 order by f1, report; +SELECT * FROM t1; +SELECT * FROM v1; # # 1. Name of one base table column is altered ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5); @@ -3854,9 +3854,9 @@ DESCRIBE t1; # Bug#12533 crash on DESCRIBE <view> after renaming base table column; --error 1356 DESCRIBE v1; -SELECT * FROM t1 order by f1, report; +SELECT * FROM t1; --error 1356 -SELECT * FROM v1 order by f1, report; +SELECT * FROM v1; ALTER TABLE t1 CHANGE COLUMN f4x f4 CHAR(5); # # 2. Length of one base table column is increased @@ -3865,8 +3865,8 @@ INSERT INTO t1 SET f1 = 2, f4 = '<-- 10 -->', report = 't1 2'; INSERT INTO v1 SET f1 = 2, f4 = '<-- 10 -->', report = 'v1 2'; DESCRIBE t1; DESCRIBE v1; -SELECT * FROM t1 order by f1, report; -SELECT * FROM v1 order by f1, report; +SELECT * FROM t1; +SELECT * FROM v1; # # 3. Length of one base table column is reduced ALTER TABLE t1 CHANGE COLUMN f4 f4 CHAR(8); @@ -3874,8 +3874,8 @@ INSERT INTO t1 SET f1 = 3, f4 = '<-- 10 -->', report = 't1 3'; INSERT INTO v1 SET f1 = 3, f4 = '<-- 10 -->', report = 'v1 3'; DESCRIBE t1; DESCRIBE v1; -SELECT * FROM t1 order by f1, report; -SELECT * FROM v1 order by f1, report; +SELECT * FROM t1; +SELECT * FROM v1; # # 4. Type of one base table column is altered string -> string ALTER TABLE t1 CHANGE COLUMN f4 f4 VARCHAR(20); @@ -3883,8 +3883,8 @@ INSERT INTO t1 SET f1 = 4, f4 = '<------ 20 -------->', report = 't1 4'; INSERT INTO v1 SET f1 = 4, f4 = '<------ 20 -------->', report = 'v1 4'; DESCRIBE t1; DESCRIBE v1; -SELECT * FROM t1 order by f1, report; -SELECT * FROM v1 order by f1, report; +SELECT * FROM t1; +SELECT * FROM v1; # # 5. Type of one base table column altered numeric -> string ALTER TABLE t1 CHANGE COLUMN f1 f1 VARCHAR(30); @@ -3894,8 +3894,8 @@ INSERT INTO v1 SET f1 = '<------------- 30 ----------->', f4 = '<------ 20 -------->', report = 'v1 5'; DESCRIBE t1; DESCRIBE v1; -SELECT * FROM t1 order by f1, report; -SELECT * FROM v1 order by f1, report; +SELECT * FROM t1; +SELECT * FROM v1; # # 6. DROP of one base table column ALTER TABLE t1 DROP COLUMN f2; @@ -3905,9 +3905,9 @@ INSERT INTO v1 SET f1 = 'ABC', f4 = '<------ 20 -------->', report = 'v1 6'; DESCRIBE t1; --error 1356 DESCRIBE v1; -SELECT * FROM t1 order by f1, report; +SELECT * FROM t1; --error 1356 -SELECT * FROM v1 order by f1, report; +SELECT * FROM v1; # # 7. Recreation of dropped base table column with the same data type like before ALTER TABLE t1 ADD COLUMN f2 DATE DEFAULT NULL; @@ -3917,8 +3917,8 @@ INSERT INTO v1 SET f1 = 'ABC', f2 = '1500-12-04', f4 = '<------ 20 -------->', report = 'v1 7'; DESCRIBE t1; DESCRIBE v1; -SELECT * FROM t1 order by f1, report; -SELECT * FROM v1 order by f1, report; +SELECT * FROM t1; +SELECT * FROM v1; # # 8. Recreation of dropped base table column with a different data type # like before @@ -3930,8 +3930,8 @@ INSERT INTO v1 SET f1 = 'ABC', f2 = -3.3E-4, f4 = '<------ 20 -------->', report = 'v1 8'; DESCRIBE t1; DESCRIBE v1; -SELECT * FROM t1 order by f1, report; -SELECT * FROM v1 order by f1, report; +SELECT * FROM t1; +SELECT * FROM v1; # # 9. Add a column to the base table ALTER TABLE t1 ADD COLUMN f3 NUMERIC(7,2); @@ -3944,8 +3944,8 @@ INSERT INTO v1 SET f1 = 'ABC', f2 = -3.3E-4, f4 = '<------ 20 -------->', report = 'v1 9a'; DESCRIBE t1; DESCRIBE v1; -SELECT * FROM t1 order by f1, report; -SELECT * FROM v1 order by f1, report; +SELECT * FROM t1; +SELECT * FROM v1; # # 10. VIEW with numeric function is "victim" of data type change DROP TABLE t1; @@ -3955,32 +3955,32 @@ INSERT INTO t1 SET f1 = 'ABC', f2 = 3; CREATE VIEW v1 AS SELECT f1, SQRT(f2) my_sqrt FROM t1; DESCRIBE t1; DESCRIBE v1; -SELECT * FROM t1 order by f1, f2; -SELECT * FROM v1 order by 2; +SELECT * FROM t1; +SELECT * FROM v1; ALTER TABLE t1 CHANGE COLUMN f2 f2 VARCHAR(30); INSERT INTO t1 SET f1 = 'ABC', f2 = 'DEF'; DESCRIBE t1; DESCRIBE v1; -SELECT * FROM t1 order by f1, f2; -SELECT * FROM v1 order by 2; +SELECT * FROM t1; +SELECT * FROM v1; # Some statements for comparison # - the ugly SQRT('DEF) as constant SELECT SQRT('DEF'); # - Will a VIEW based on the same definition show the same result ? CREATE VIEW v2 AS SELECT SQRT('DEF'); -SELECT * FROM v2 order by 1; +SELECT * FROM v2; # - Will a VIEW v2 created after the base table column recreation show the same # result set like v1 ? CREATE OR REPLACE VIEW v2 AS SELECT f1, SQRT(f2) my_sqrt FROM t1; DESCRIBE v2; -SELECT * FROM v2 order by 2; +SELECT * FROM v2; # - What will be the content of base table created with AS SELECT ? CREATE TABLE t2 AS SELECT f1, SQRT(f2) my_sqrt FROM t1; if ($have_bug_11589) { --disable_ps_protocol } -SELECT * FROM t2 order by 2; +SELECT * FROM t2; --enable_ps_protocol DROP TABLE t2; CREATE TABLE t2 AS SELECT * FROM v1; @@ -3988,7 +3988,7 @@ if ($have_bug_11589) { --disable_ps_protocol } -SELECT * FROM t2 order by 2; +SELECT * FROM t2; --enable_ps_protocol DROP TABLE t2; CREATE TABLE t2 AS SELECT * FROM v2; @@ -3996,7 +3996,7 @@ if ($have_bug_11589) { --disable_ps_protocol } -SELECT * FROM t2 order by 2; +SELECT * FROM t2; --enable_ps_protocol # DROP TABLE t1; |