diff options
| author | Sergei Golubchik <serg@mariadb.org> | 2016-08-10 19:19:05 +0200 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2016-08-10 19:19:05 +0200 |
| commit | 309c08c17c56d35e5635a5874fb70719935e5f54 (patch) | |
| tree | db63b0f496364456789f390eb5f693dabf0f94a1 /mysql-test | |
| parent | c6fdb92ca829fed893d9e7324e80b1450de16087 (diff) | |
| parent | 5ad02062d928cccbd29c0a2db6f0f7ceb33195d1 (diff) | |
| download | mariadb-git-309c08c17c56d35e5635a5874fb70719935e5f54.tar.gz | |
Merge branch '5.5' into 10.0
Diffstat (limited to 'mysql-test')
36 files changed, 908 insertions, 61 deletions
diff --git a/mysql-test/include/report-features.test b/mysql-test/include/report-features.test deleted file mode 100644 index 75879f67165..00000000000 --- a/mysql-test/include/report-features.test +++ /dev/null @@ -1,12 +0,0 @@ -# -# show server variables -# - ---disable_query_log ---echo ===== ENGINES ===== -show engines; ---echo ===== VARIABLES ===== -show variables; ---echo ===== STOP ===== ---enable_query_log -exit; diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index f75f65f0644..5af19c8b9b6 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -279,7 +279,6 @@ my $opt_port_base= $ENV{'MTR_PORT_BASE'} || "auto"; my $build_thread= 0; my $opt_record; -my $opt_report_features; our $opt_resfile= $ENV{'MTR_RESULT_FILE'} || 0; @@ -434,21 +433,6 @@ sub main { exit 0; } - if ( $opt_report_features ) { - # Put "report features" as the first test to run - my $tinfo = My::Test->new - ( - name => 'report_features', - # No result_file => Prints result - path => 'include/report-features.test', - template_path => "include/default_my.cnf", - master_opt => [], - slave_opt => [], - suite => 'main', - ); - unshift(@$tests, $tinfo); - } - ####################################################################### my $num_tests= @$tests; if ( $opt_parallel eq "auto" ) { @@ -1214,7 +1198,6 @@ sub command_line_setup { 'client-libdir=s' => \$path_client_libdir, # Misc - 'report-features' => \$opt_report_features, 'comment=s' => \$opt_comment, 'fast' => \$opt_fast, 'force-restart' => \$opt_force_restart, @@ -6634,7 +6617,6 @@ Misc options gprof Collect profiling information using gprof. experimental=<file> Refer to list of tests considered experimental; failures will be marked exp-fail instead of fail. - report-features First run a "test" that reports mysql features timestamp Print timestamp before each test report line timediff With --timestamp, also print time passed since *previous* test started diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index ac076ec4348..38fae2f0a4f 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -2270,3 +2270,42 @@ t2_id GROUP_CONCAT(IF (t6.b, t6.f, t5.f) ORDER BY 1) EXECUTE stmt; t2_id GROUP_CONCAT(IF (t6.b, t6.f, t5.f) ORDER BY 1) DROP TABLE t1,t2,t3,t4,t5,t6; +# +# MDEV-10500 CASE/IF Statement returns multiple values and shifts further result values to the next column +# +CREATE TABLE t1 ( +id int not null AUTO_INCREMENT, +active bool not null, +data1 bigint, +data2 bigint, +data3 bigint, +primary key (id) +); +INSERT INTO t1 (active,data1,data2,data3) VALUES (1,null,100,200); +SELECT +CASE WHEN active THEN SUM(data1) END AS C_1, +SUM(data2) AS C_2, +SUM(data3) AS C_3 +FROM t1; +C_1 C_2 C_3 +NULL 100 200 +SELECT +IF(active, SUM(data1), 5) AS C_1, +SUM(data2) AS C_2, +SUM(data3) AS C_3 +FROM t1; +C_1 C_2 C_3 +NULL 100 200 +DROP TABLE t1; +# +# MDEV-10468 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() +# +SELECT STDDEV_POP(f) FROM (SELECT "1e+309" AS f UNION SELECT "-1e+309" AS f) tbl; +STDDEV_POP(f) +1.7976931348623157e308 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+309' +Warning 1292 Truncated incorrect DOUBLE value: '-1e+309' +SELECT STDDEV(f) FROM (SELECT 1.7976931348623157e+308 AS f UNION SELECT -1.7976931348623157e+308 AS f) tbl; +STDDEV(f) +1.7976931348623157e308 diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 1259661f0b6..32352dddd86 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -777,10 +777,16 @@ select 5.9 div 2, 1.23456789e3 DIV 2, 1.23456789e9 DIV 2, 1.23456789e19 DIV 2; 5.9 div 2 1.23456789e3 DIV 2 1.23456789e9 DIV 2 1.23456789e19 DIV 2 2 617 617283945 6172839450000000000 # -# End of 5.5 tests +# MDEV-10467 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() # +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1),(2); +SELECT STDDEV_SAMP(ROUND('0', 309)) FROM t1; +STDDEV_SAMP(ROUND('0', 309)) +0 +DROP TABLE t1; # -# Start of 10.0 tests +# End of 5.5 tests # # # MDEV-5781 Item_sum_std::val_real(): Assertion `nr >= 0.0' fails on query with STDDEV_POP, ROUND and variable diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index bab360d677d..580982f1ede 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -571,6 +571,13 @@ AND 57813X540X1723 = 'Test'; N AVG 0 NULL drop table t1; +SELECT NAME_CONST('a', -(1 OR 2)) OR 1; +ERROR HY000: Incorrect arguments to NAME_CONST +SELECT NAME_CONST('a', -(1 AND 2)) OR 1; +ERROR HY000: Incorrect arguments to NAME_CONST +SELECT NAME_CONST('a', -(1)) OR 1; +NAME_CONST('a', -(1)) OR 1 +1 # # End of 5.5 tests # diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 2d67d24bedd..2f2a3579eec 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -507,7 +507,7 @@ DROP TABLE t1; # Bug#11765139 58069: LOAD DATA INFILE: VALGRIND REPORTS INVALID MEMORY READS AND WRITES WITH U # CREATE TABLE t1(f1 INT); -SELECT 0xE1BB30 INTO OUTFILE 't1.dat'; +SELECT 0xE1C330 INTO OUTFILE 't1.dat'; LOAD DATA INFILE 't1.dat' IGNORE INTO TABLE t1 CHARACTER SET utf8; DROP TABLE t1; # @@ -532,3 +532,27 @@ FIELDS TERMINATED BY 't' LINES TERMINATED BY ''; Got one of the listed errors SET @@sql_mode= @old_mode; DROP TABLE t1; + +# +# Bug#23080148 - Backport of Bug#20683959. +# Bug#20683959 LOAD DATA INFILE IGNORES A SPECIFIC ROW SILENTLY +# UNDER DB CHARSET IS UTF8. +# +CREATE DATABASE d1 CHARSET latin1; +USE d1; +CREATE TABLE t1 (val TEXT); +LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +SELECT HEX(val) FROM t1; +HEX(val) +C38322525420406E696F757A656368756E3A20E98198E2889AF58081AEE7B99DE4B88AE383A3E7B99DE69690F58087B3E7B9A7EFBDA8E7B99DEFBDB3E7B99DE78999E880B3E7B8BAEFBDAAE7B9A7E89699E296A1E7B8BAE4BBA3EFBD8CE7B8BAEFBDA9E7B8B2E2889AE38184E7B99DEFBDB3E7B99DE4B88AE383A3E7B99DE69690F58087B3E7B9A7EFBDA8E7B99DEFBDB3E7B99DE5B3A8EFBD84E8ABA0EFBDA8E89C89F580948EE599AAE7B8BAEFBDAAE7B8BAE9A198EFBDA9EFBDB1E7B9A7E581B5E289A0E7B8BAEFBDBEE7B9A7E9A194EFBDA9E882B4EFBDA5EFBDB5E980A7F5808B96E28693E99EABE38287E58F99E7B8BAE58AB1E28691E7B8BAF5808B9AE7828AE98095EFBDB1E7B8BAEFBDAFE7B8B2E288ABE6A89FE89EB3E6BA98F58081ADE88EA0EFBDBAE98095E6BA98F58081AEE89D93EFBDBAE8AD9BEFBDACE980A7F5808B96E28693E7B8BAF580918EE288AAE7B8BAE4B88AEFBC9EE7B8BAE4B99DE28691E7B8BAF5808B96EFBCA0E88DB3E6A68AEFBDB9EFBDB3E981B2E5B3A8E296A1E7B8BAE7A4BCE7828AE88DB3E6A68AEFBDB0EFBDBDE7B8BAA0E7B8BAE88B93EFBDBEE5B899EFBC9E +CREATE DATABASE d2 CHARSET utf8; +USE d2; +CREATE TABLE t1 (val TEXT); +LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; +ERROR HY000: Invalid utf8 character string: '"RT @niouzechun: \9058\221A' +DROP TABLE d1.t1, d2.t1; +DROP DATABASE d1; +DROP DATABASE d2; diff --git a/mysql-test/r/myisam_enable_keys-10506.result b/mysql-test/r/myisam_enable_keys-10506.result new file mode 100644 index 00000000000..e7ffba787f0 --- /dev/null +++ b/mysql-test/r/myisam_enable_keys-10506.result @@ -0,0 +1,114 @@ +CREATE TABLE t1 ( +pk INT AUTO_INCREMENT, +i INT, +d DATE, +dt DATETIME, +v VARCHAR(1), +PRIMARY KEY (pk), +KEY (dt) +) ENGINE=MyISAM; +INSERT INTO t1 (i, d, dt, v) VALUES +(9, '2005-07-23', '2004-05-13 01:01:39', 't'), +(2, '2009-11-01', '2003-12-24 07:39:29', 'h'), +(6, NULL, '2008-07-03 05:32:22', 'l'), +(6, '2007-07-16', '2008-08-28 18:46:11', 'j'), +(5, NULL, '2001-07-12 21:27:00', 'h'), +(3, '2007-07-22', '1900-01-01 00:00:00', 'p'), +(2, '2000-11-21', '2007-05-25 11:58:54', 'g'), +(6, '1900-01-01', '2009-06-03 17:11:10', 'i'), +(2, '2008-02-10', '2001-06-15 16:20:07', 'p'), +(3, '2009-06-04', '1900-01-01 00:00:00', 'h'), +(9, '2007-04-25', '1900-01-01 00:00:00', 'e'), +(9, '2006-03-02', '1900-01-01 00:00:00', 'e'), +(1, '1900-01-01', '2002-11-08 09:33:27', 'u'), +(7, '2008-07-13', '2007-08-07 17:35:52', 'j'), +(0, '2004-11-12', '2006-05-01 00:00:00', 'e'), +(0, '1900-01-01', '2003-05-01 00:00:00', 'z'), +(1, '2009-09-02', '2007-02-12 09:30:49', 'w'), +(0, '2004-11-06', '1900-01-01 00:00:00', 't'), +(4, '2003-01-06', '2002-07-03 02:51:11', 'i'), +(6, '2006-01-14', '2008-02-26 04:57:32', 'i'), +(0, '2002-01-19', '2009-02-12 00:00:00', 'i'), +(8, '2007-02-12', '1900-01-01 00:00:00', 'b'), +(4, '1900-01-01', '2001-05-16 05:28:40', 'm'), +(2, '2005-07-16', NULL, 'j'), +(1, '2004-09-04', '2001-01-24 21:45:18', 'v'), +(3, '2009-07-01', NULL, NULL), +(2, '2009-07-21', '2002-07-24 00:00:00', 'h'), +(4, NULL, '2001-11-03 12:22:30', 'q'), +(1, '2002-06-22', '2008-06-17 03:17:59', 'f'), +(7, '2005-06-23', '2005-12-24 00:00:00', 'p'), +(6, '2001-05-20', '2008-10-23 00:00:00', NULL), +(3, '2001-10-01', '2000-10-12 16:32:35', 'o'), +(3, '2001-01-07', '2005-09-11 10:09:54', 'w'), +(6, '2007-11-02', '2009-09-10 01:44:18', 'l'), +(6, NULL, NULL, 'i'), +(9, NULL, '2002-05-18 15:21:55', 'd'), +(4, '2008-12-21', '2004-10-15 10:09:54', 'j'), +(6, '2003-10-05', '2009-07-13 03:51:02', 'e'), +(2, '2001-03-03', '1900-01-01 00:00:00', 'e'), +(2, '2007-04-04', '2001-11-08 21:14:52', 'q'), +(5, NULL, '2006-12-02 00:00:00', 'm'), +(0, '2009-01-04', '1900-01-01 00:00:00', NULL), +(8, '2008-04-03', '2005-01-01 11:55:18', 'q'), +(8, NULL, '2005-02-28 03:44:02', 'w'), +(0, '2003-08-22', NULL, 'c'), +(9, '1900-01-01', NULL, 'y'), +(NULL, NULL, '2006-08-25 16:28:09', 'g'), +(5, '2004-07-04', '2002-08-11 00:00:00', 'z'), +(1, '1900-01-01', '2007-07-22 21:19:18', 'm'), +(2, '2007-02-04', '2006-02-10 18:41:38', 't'), +(2, '1900-01-01', '2009-02-16 14:58:58', 'd'), +(7, '2001-03-14', '2007-08-14 00:00:00', 'h'), +(0, NULL, '1900-01-01 00:00:00', NULL), +(1, '2008-10-05', NULL, 'f'), +(6, '2001-11-25', '2008-12-03 06:59:23', 'l'), +(NULL, '2003-01-27', '2008-10-04 00:00:00', 'g'), +(8, '2008-08-08', '2009-07-07 07:00:21', 'v'), +(8, '2006-07-03', '2001-04-15 00:00:00', NULL), +(5, '2002-11-21', '2007-07-08 04:01:58', 'm'), +(5, '2006-04-08', '2007-09-23 00:01:35', 'i'), +(5, '2001-05-06', '2008-05-15 00:00:00', 'h'), +(7, '1900-01-01', '1900-01-01 00:00:00', 'u'), +(30, '2007-04-16', '2004-03-05 23:35:38', 'o'), +(NULL, '1900-01-01', '2007-08-25 01:32:47', 'z'), +(6, '2004-12-03', '1900-01-01 00:00:00', 'o'), +(8, '2001-06-23', '1900-01-01 00:00:00', 'f'), +(NULL, '2008-12-15', '2001-05-19 08:28:28', 'a'), +(9, '2000-02-15', '2009-09-03 06:07:22', 'd'), +(2, '2001-08-05', '2006-10-08 07:17:27', 'k'), +(5, '2004-01-17', '2003-09-06 20:36:01', 'd'), +(4, '2003-10-01', '2001-02-05 18:10:49', 'u'), +(4, '2003-07-28', '2001-01-07 16:11:37', 'h'), +(0, '1900-01-01', '2008-08-01 05:26:38', 'w'), +(9, '1900-01-01', '2001-05-08 00:00:00', 't'), +(1, '2000-04-17', '2008-07-10 21:26:28', 'i'), +(8, '2002-01-05', '2006-08-06 20:56:35', 'k'), +(9, '2001-04-10', '2003-02-17 00:00:00', 'z'), +(0, '2009-12-04', NULL, 'h'), +(7, NULL, '2004-10-27 00:29:57', 'h'), +(2, '2006-03-07', '2008-03-04 06:14:13', 'b'), +(0, '2001-10-15', '2001-03-17 00:00:00', 'm'), +(5, '1900-01-01', '2009-02-21 11:35:50', 'i'), +(4, NULL, '1900-01-01 00:00:00', 'w'), +(5, '2009-04-05', '1900-01-01 00:00:00', 'm'), +(6, '2001-03-19', '2001-04-12 00:00:00', 'q'), +(NULL, '2009-12-08', '2001-12-04 20:21:01', 'k'), +(2, '2005-02-09', '2001-05-27 08:41:01', 'l'), +(9, '2004-05-25', '2004-09-18 00:00:00', 'c'), +(3, '2005-01-17', '2002-09-12 11:18:48', 'd'), +(0, '2003-08-28', '1900-01-01 00:00:00', 'k'), +(6, '2006-10-11', '2003-10-28 03:31:02', 'a'), +(5, '1900-01-01', '2001-08-22 10:20:09', 'p'), +(8, '1900-01-01', '2008-04-24 00:00:00', 'o'), +(4, '2005-08-18', '2006-11-10 10:08:49', 'e'), +(NULL, '2007-03-12', '2007-10-16 00:00:00', 'n'), +(1, '2000-11-18', '2009-05-27 12:25:07', 't'), +(4, '2001-03-03', NULL, 'u'), +(3, '2003-09-11', '2001-09-10 18:10:10', 'f'), +(4, '2007-06-17', '1900-01-01 00:00:00', 't'), +(NULL, '2008-09-11', '2004-06-07 23:17:09', 'k'); +ALTER TABLE t1 ADD UNIQUE KEY ind1 (pk, d, i, v); +ALTER TABLE t1 ADD UNIQUE KEY ind2 (d, v); +ERROR 23000: Duplicate entry '2008-09-11-k' for key 'ind2' +DROP TABLE t1; diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index b86f3a7e1aa..256b1375b6b 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -343,10 +343,37 @@ DROP TABLE bug47205; # #MDEV-6128:[PATCH] mysqlcheck wrongly escapes '.' in table names # -CREATE TABLE test.`t.1` (id int); +create table `t.1` (id int); +create view `v.1` as select 1; mysqlcheck test t.1 test.t.1 OK -drop table test.`t.1`; +mysqlcheck --all-in-1 test t.1 +test.t.1 OK +mysqlcheck --all-in-1 --databases --process-views test +test.t.1 OK +test.v.1 OK +create table `t.2`(a varchar(20) primary key) default character set utf8 collate utf8_general_ci engine=innodb; +flush table `t.2`; +mysqlcheck --check-upgrade --auto-repair test +test.t.1 OK +test.t.2 +error : Table rebuild required. Please do "ALTER TABLE `t.2` FORCE" or dump/reload to fix it! +test.t.3 Needs upgrade + +Repairing tables +test.t.3 OK +check table `t.1`, `t.2`, `t.3`; +Table Op Msg_type Msg_text +test.t.1 check status OK +test.t.2 check status OK +test.t.3 check status OK +check table `t.1`, `t.2`, `t.3` for upgrade; +Table Op Msg_type Msg_text +test.t.1 check status OK +test.t.2 check status OK +test.t.3 check status OK +drop view `v.1`; +drop table test.`t.1`, `t.2`, `t.3`; # # MDEV-8123 mysqlcheck: new --process-views option conflicts with --quick, --extended and such # @@ -381,6 +408,57 @@ show tables; Tables_in_test t1`1 drop table `t1``1`; +call mtr.add_suppression("ha_myisam"); +call mtr.add_suppression("Checking table"); +create database mysqltest1; +create table mysqltest1.t1 (a int) engine=myisam; +create table t2 (a int); +check table mysqltest1.t1; +Table Op Msg_type Msg_text +mysqltest1.t1 check warning Size of datafile is: 4 Should be: 0 +mysqltest1.t1 check error got error: 0 when reading datafile at record: 0 +mysqltest1.t1 check error Corrupt +mtr.global_suppressions Table is already up to date +mtr.test_suppressions Table is already up to date +mysql.column_stats Table is already up to date +mysql.columns_priv Table is already up to date +mysql.db Table is already up to date +mysql.event Table is already up to date +mysql.func Table is already up to date +mysql.gtid_slave_pos Table is already up to date +mysql.help_category Table is already up to date +mysql.help_keyword Table is already up to date +mysql.help_relation Table is already up to date +mysql.help_topic Table is already up to date +mysql.host Table is already up to date +mysql.index_stats Table is already up to date +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK +mysql.plugin Table is already up to date +mysql.proc Table is already up to date +mysql.procs_priv Table is already up to date +mysql.proxies_priv Table is already up to date +mysql.roles_mapping Table is already up to date +mysql.servers Table is already up to date +mysql.table_stats Table is already up to date +mysql.tables_priv Table is already up to date +mysql.time_zone Table is already up to date +mysql.time_zone_leap_second Table is already up to date +mysql.time_zone_name Table is already up to date +mysql.time_zone_transition Table is already up to date +mysql.time_zone_transition_type Table is already up to date +mysql.user Table is already up to date +mysqltest1.t1 +warning : Table is marked as crashed +warning : Size of datafile is: 4 Should be: 0 +error : got error: 0 when reading datafile at record: 0 +error : Corrupt +test.t2 Table is already up to date + +Repairing tables +mysqltest1.t1 OK +drop table t2; +drop database mysqltest1; # #MDEV-7384 [PATCH] add PERSISENT FOR ALL option to mysqlanalyze/mysqlcheck # diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 89fac898414..51e4e313eac 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -2114,6 +2114,37 @@ a b 1 1 drop table t2; # +# MDEV-10228: Delete missing rows with OR conditions +# (The example uses UPDATE, because UPDATE allows to use index hints +# and so it's possible to make an example that works with any storage +# engine) +# +CREATE TABLE t1 ( +key1varchar varchar(14) NOT NULL, +key2int int(11) NOT NULL DEFAULT '0', +col1 int, +PRIMARY KEY (key1varchar,key2int), +KEY key1varchar (key1varchar), +KEY key2int (key2int) +) DEFAULT CHARSET=utf8; +insert into t1 values +('value1',0, 0), +('value1',1, 0), +('value1',1000685, 0), +('value1',1003560, 0), +('value1',1004807, 0); +update t1 force index (PRIMARY) set col1=12345 +where (key1varchar='value1' AND (key2int <=1 OR key2int > 1)); +# The following must show col1=12345 for all rows: +select * from t1; +key1varchar key2int col1 +value1 0 12345 +value1 1 12345 +value1 1000685 12345 +value1 1003560 12345 +value1 1004807 12345 +drop table t1; +# # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE # CREATE TABLE t1 (pk INT PRIMARY KEY); diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 0350e36eec9..16c0825b2e2 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -2116,6 +2116,37 @@ a b 1 1 drop table t2; # +# MDEV-10228: Delete missing rows with OR conditions +# (The example uses UPDATE, because UPDATE allows to use index hints +# and so it's possible to make an example that works with any storage +# engine) +# +CREATE TABLE t1 ( +key1varchar varchar(14) NOT NULL, +key2int int(11) NOT NULL DEFAULT '0', +col1 int, +PRIMARY KEY (key1varchar,key2int), +KEY key1varchar (key1varchar), +KEY key2int (key2int) +) DEFAULT CHARSET=utf8; +insert into t1 values +('value1',0, 0), +('value1',1, 0), +('value1',1000685, 0), +('value1',1003560, 0), +('value1',1004807, 0); +update t1 force index (PRIMARY) set col1=12345 +where (key1varchar='value1' AND (key2int <=1 OR key2int > 1)); +# The following must show col1=12345 for all rows: +select * from t1; +key1varchar key2int col1 +value1 0 12345 +value1 1 12345 +value1 1000685 12345 +value1 1003560 12345 +value1 1004807 12345 +drop table t1; +# # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE # CREATE TABLE t1 (pk INT PRIMARY KEY); diff --git a/mysql-test/r/sp-prelocking.result b/mysql-test/r/sp-prelocking.result index 9e82a966268..5b594e9ea55 100644 --- a/mysql-test/r/sp-prelocking.result +++ b/mysql-test/r/sp-prelocking.result @@ -320,3 +320,23 @@ c2 DROP TRIGGER t1_ai; DROP TABLE t1, t2; End of 5.0 tests +# +# Bug#21142859: FUNCTION UPDATING A VIEW FAILS TO FIND TABLE THAT ACTUALLY EXISTS +# +CREATE TABLE t1 SELECT 1 AS fld1, 'A' AS fld2; +CREATE TABLE t2 (fld3 INT, fld4 CHAR(1)); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE TRIGGER t1_au AFTER UPDATE ON t1 +FOR EACH ROW INSERT INTO t2 VALUES (new.fld1, new.fld2); +CREATE FUNCTION f1() RETURNS INT +BEGIN +UPDATE v1 SET fld2='B' WHERE fld1=1; +RETURN row_count(); +END ! +# Without the patch, an error was getting reported. +SELECT f1(); +f1() +1 +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE t1,t2; diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index 7e5ac85a552..ea21115a755 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -165,6 +165,7 @@ str_to_date( '', a ) NULL DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT, PRIMARY KEY (a,b)); +SET timestamp=UNIX_TIMESTAMP('2016-07-21 14:48:18'); INSERT INTO t1 VALUES (DATE(NOW()), 1); SELECT COUNT(*) FROM t1 WHERE a = NOW(); COUNT(*) @@ -192,6 +193,7 @@ COUNT(*) EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SET timestamp=DEFAULT; DROP TABLE t1; CREATE TABLE t1 (a DATE); CREATE TABLE t2 (a DATE); diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 96c35d9dcb3..a92ccbdde94 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -5518,6 +5518,21 @@ test.v1 check Error 'test.v1' is not BASE TABLE test.v1 check status Operation failed drop view v1; drop table t1; +# +# MDEV-10419: crash in mariadb 10.1.16-MariaDB-1~trusty +# +CREATE TABLE t1 (c1 CHAR(13)); +CREATE TABLE t2 (c2 CHAR(13)); +CREATE FUNCTION f() RETURNS INT RETURN 0; +CREATE OR REPLACE VIEW v1 AS select f() from t1 where c1 in (select c2 from t2); +DROP FUNCTION f; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `f`() AS `f()` from `t1` where `test`.`t1`.`c1` in (select `test`.`t2`.`c2` from `t2`) latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +drop view v1; +drop table t1,t2; # ----------------------------------------------------------------- # -- End of 5.5 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/r/xtradb_mrr.result b/mysql-test/r/xtradb_mrr.result index 15b750d2fd3..c238d0530af 100644 --- a/mysql-test/r/xtradb_mrr.result +++ b/mysql-test/r/xtradb_mrr.result @@ -311,10 +311,10 @@ concat('c-', 1000 + C.a, '-c'), 'filler' from t1 A, t1 B, t1 C; explain -select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a'; +select count(length(a) + length(filler)) from t2 force index (a) where a>='a-1000-a' and a <'a-1001-a'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range a a 9 NULL 99 Using index condition; Rowid-ordered scan -select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a'; +select count(length(a) + length(filler)) from t2 force index (a) where a>='a-1000-a' and a <'a-1001-a'; count(length(a) + length(filler)) 100 drop table t2; diff --git a/mysql-test/std_data/bug20683959loaddata.txt b/mysql-test/std_data/bug20683959loaddata.txt new file mode 100644 index 00000000000..1878cc78879 --- /dev/null +++ b/mysql-test/std_data/bug20683959loaddata.txt @@ -0,0 +1 @@ +Ã"RT @niouzechun: 遘√繝上ャ繝斐繧ｨ繝ｳ繝牙耳縺ｪ繧薙□縺代ｌ縺ｩ縲√い繝ｳ繝上ャ繝斐繧ｨ繝ｳ繝峨ｄ諠ｨ蜉噪縺ｪ縺願ｩｱ繧偵≠縺ｾ繧顔ｩ肴･ｵ逧↓鞫ょ叙縺励↑縺炊逕ｱ縺ｯ縲∫樟螳溘莠ｺ逕溘蝓ｺ譛ｬ逧↓縺∪縺上＞縺九↑縺＠荳榊ｹｳ遲峨□縺礼炊荳榊ｰｽ縺縺苓ｾ帙＞ diff --git a/mysql-test/suite/binlog/r/binlog_dmls_on_tmp_tables_readonly.result b/mysql-test/suite/binlog/r/binlog_dmls_on_tmp_tables_readonly.result new file mode 100644 index 00000000000..1dfac08e762 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_dmls_on_tmp_tables_readonly.result @@ -0,0 +1,58 @@ +DROP TABLE IF EXISTS t1 ; +# READ_ONLY does nothing to SUPER users +# so we use a non-SUPER one: +GRANT CREATE, SELECT, DROP ON *.* TO test@localhost; +connect con1,localhost,test,,test; +connection default; +SET GLOBAL READ_ONLY=1; +connection con1; +CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB; +# Test INSERTS with autocommit being off and on. +BEGIN; +INSERT INTO t1 VALUES (10); +COMMIT; +INSERT INTO t1 VALUES (20); +# Test UPDATES with autocommit being off and on. +BEGIN; +UPDATE t1 SET a=30 WHERE a=10; +COMMIT; +UPDATE t1 SET a=40 WHERE a=20; +connection default; +SET GLOBAL READ_ONLY=0; +# Test scenario where global read_only is enabled in the middle of transaction. +# Test INSERT operations on temporary tables, INSERTs should be successful even +# when global read_only is enabled. +connection con1; +BEGIN; +INSERT INTO t1 VALUES(50); +connection default; +SET GLOBAL READ_ONLY=1; +connection con1; +SELECT @@GLOBAL.READ_ONLY; +@@GLOBAL.READ_ONLY +1 +COMMIT; +connection default; +SET GLOBAL READ_ONLY=0; +# Test UPDATE operations on temporary tables, UPDATEs should be successful even +# when global read_only is enabled. +connection con1; +BEGIN; +UPDATE t1 SET a=60 WHERE a=50; +connection default; +SET GLOBAL READ_ONLY=1; +connection con1; +SELECT @@GLOBAL.READ_ONLY; +@@GLOBAL.READ_ONLY +1 +COMMIT; +SELECT * FROM t1; +a +30 +40 +60 +# Clean up +connection default; +SET GLOBAL READ_ONLY=0; +disconnect con1; +DROP USER test@localhost; diff --git a/mysql-test/suite/binlog/t/binlog_dmls_on_tmp_tables_readonly.test b/mysql-test/suite/binlog/t/binlog_dmls_on_tmp_tables_readonly.test new file mode 100644 index 00000000000..cf3910eb229 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_dmls_on_tmp_tables_readonly.test @@ -0,0 +1,91 @@ +# ==== Purpose ==== +# +# Check that DMLs are allowed on temporary tables, when server is in read only +# mode and binary log is enabled with binlog-format being stmt/mixed mode. +# +# ==== Implementation ==== +# +# Start the server with binary log being enabled. Mark the server as read only. +# Create a non-SUPER user and let the user to create a temporary table and +# perform DML operations on that temporary table. DMLs should not be blocked +# with a 'server read-only mode' error. +# +# ==== References ==== +# +# Bug#12818255: READ-ONLY OPTION DOES NOT ALLOW INSERTS/UPDATES ON TEMPORARY +# TABLES +# Bug#14294223: CHANGES NOT ALLOWED TO TEMPORARY TABLES ON READ-ONLY SERVERS +############################################################################### +--source include/have_log_bin.inc +--source include/have_innodb.inc +--disable_warnings +DROP TABLE IF EXISTS t1 ; +--enable_warnings + +--enable_connect_log +--echo # READ_ONLY does nothing to SUPER users +--echo # so we use a non-SUPER one: +GRANT CREATE, SELECT, DROP ON *.* TO test@localhost; + +connect (con1,localhost,test,,test); + +connection default; +SET GLOBAL READ_ONLY=1; + +connection con1; +CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB; + +--echo # Test INSERTS with autocommit being off and on. +BEGIN; +INSERT INTO t1 VALUES (10); +COMMIT; +INSERT INTO t1 VALUES (20); + +--echo # Test UPDATES with autocommit being off and on. +BEGIN; +UPDATE t1 SET a=30 WHERE a=10; +COMMIT; +UPDATE t1 SET a=40 WHERE a=20; + +connection default; +SET GLOBAL READ_ONLY=0; + +--echo # Test scenario where global read_only is enabled in the middle of transaction. +--echo # Test INSERT operations on temporary tables, INSERTs should be successful even +--echo # when global read_only is enabled. +connection con1; +BEGIN; +INSERT INTO t1 VALUES(50); + +connection default; +SET GLOBAL READ_ONLY=1; + +connection con1; +SELECT @@GLOBAL.READ_ONLY; +COMMIT; + +connection default; +SET GLOBAL READ_ONLY=0; + +--echo # Test UPDATE operations on temporary tables, UPDATEs should be successful even +--echo # when global read_only is enabled. +connection con1; +BEGIN; +UPDATE t1 SET a=60 WHERE a=50; + +connection default; +SET GLOBAL READ_ONLY=1; + +connection con1; +SELECT @@GLOBAL.READ_ONLY; +COMMIT; + +SELECT * FROM t1; + +--echo # Clean up +connection default; +SET GLOBAL READ_ONLY=0; + +disconnect con1; +DROP USER test@localhost; +--disable_connect_log diff --git a/mysql-test/suite/plugins/r/pam_cleartext.result b/mysql-test/suite/plugins/r/pam_cleartext.result index 00e0e94618e..b9eee74ec3e 100644 --- a/mysql-test/suite/plugins/r/pam_cleartext.result +++ b/mysql-test/suite/plugins/r/pam_cleartext.result @@ -5,6 +5,9 @@ grant proxy on pam_test to test_pam; show variables like 'pam%'; Variable_name Value pam_use_cleartext_plugin ON +# +# same test as in pam.test now fails +# drop user test_pam; drop user pam_test; uninstall plugin pam; diff --git a/mysql-test/suite/plugins/t/pam.test b/mysql-test/suite/plugins/t/pam.test index 1871e5801a3..8a95d6baed2 100644 --- a/mysql-test/suite/plugins/t/pam.test +++ b/mysql-test/suite/plugins/t/pam.test @@ -29,5 +29,6 @@ EOF --remove_file $MYSQLTEST_VARDIR/tmp/pam_bad.txt drop user test_pam; drop user pam_test; +let $count_sessions= 1; +--source include/wait_until_count_sessions.inc uninstall plugin pam; - diff --git a/mysql-test/suite/plugins/t/pam_cleartext.test b/mysql-test/suite/plugins/t/pam_cleartext.test index 6b9bf087ce5..8476c39fd89 100644 --- a/mysql-test/suite/plugins/t/pam_cleartext.test +++ b/mysql-test/suite/plugins/t/pam_cleartext.test @@ -3,11 +3,22 @@ show variables like 'pam%'; +--write_file $MYSQLTEST_VARDIR/tmp/pam_good.txt +not very secret challenge +9225 +select user(), current_user(), database(); +EOF + +--echo # +--echo # same test as in pam.test now fails +--echo # --error 1 ---exec echo FAIL | $MYSQL_TEST -u test_pam --plugin-dir=$plugindir +--exec $MYSQL_TEST -u test_pam --plugin-dir=$plugindir < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +--remove_file $MYSQLTEST_VARDIR/tmp/pam_good.txt drop user test_pam; drop user pam_test; ---disable_warnings +let $count_sessions= 1; +--source include/wait_until_count_sessions.inc uninstall plugin pam; - diff --git a/mysql-test/suite/sys_vars/r/general_log_file_basic.result b/mysql-test/suite/sys_vars/r/general_log_file_basic.result index 369ef7844db..c7c24f155ca 100644 --- a/mysql-test/suite/sys_vars/r/general_log_file_basic.result +++ b/mysql-test/suite/sys_vars/r/general_log_file_basic.result @@ -12,6 +12,16 @@ SET @@global.general_log_file = mytest.log; ERROR 42000: Incorrect argument type to variable 'general_log_file' SET @@global.general_log_file = 12; ERROR 42000: Incorrect argument type to variable 'general_log_file' +SET @@global.general_log_file = 'my.cnf'; +ERROR 42000: Variable 'general_log_file' can't be set to the value of 'my.cnf' +SET @@global.general_log_file = '/tmp/my.cnf'; +ERROR 42000: Variable 'general_log_file' can't be set to the value of '/tmp/my.cnf' +SET @@global.general_log_file = '.my.cnf'; +ERROR 42000: Variable 'general_log_file' can't be set to the value of '.my.cnf' +SET @@global.general_log_file = 'my.cnf\0foo'; +ERROR 42000: Variable 'general_log_file' can't be set to the value of 'my.cnf' +SET @@global.general_log_file = 'my.ini'; +ERROR 42000: Variable 'general_log_file' can't be set to the value of 'my.ini' '#----------------------FN_DYNVARS_004_03------------------------#' SELECT @@global.general_log_file = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES diff --git a/mysql-test/suite/sys_vars/r/slow_query_log_file_basic.result b/mysql-test/suite/sys_vars/r/slow_query_log_file_basic.result index f45c568ff4a..a64666f6298 100644 --- a/mysql-test/suite/sys_vars/r/slow_query_log_file_basic.result +++ b/mysql-test/suite/sys_vars/r/slow_query_log_file_basic.result @@ -9,6 +9,16 @@ SET @@global.slow_query_log_file = mytest.log; ERROR 42000: Incorrect argument type to variable 'slow_query_log_file' SET @@global.slow_query_log_file = 12; ERROR 42000: Incorrect argument type to variable 'slow_query_log_file' +SET @@global.slow_query_log_file = 'my.cnf'; +ERROR 42000: Variable 'slow_query_log_file' can't be set to the value of 'my.cnf' +SET @@global.slow_query_log_file = '/tmp/my.cnf'; +ERROR 42000: Variable 'slow_query_log_file' can't be set to the value of '/tmp/my.cnf' +SET @@global.general_log_file = '.my.cnf'; +ERROR 42000: Variable 'general_log_file' can't be set to the value of '.my.cnf' +SET @@global.general_log_file = 'my.cnf\0foo'; +ERROR 42000: Variable 'general_log_file' can't be set to the value of 'my.cnf' +SET @@global.general_log_file = 'my.ini'; +ERROR 42000: Variable 'general_log_file' can't be set to the value of 'my.ini' '#----------------------FN_DYNVARS_004_03------------------------#' SELECT @@global.slow_query_log_file = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES diff --git a/mysql-test/suite/sys_vars/t/general_log_file_basic.test b/mysql-test/suite/sys_vars/t/general_log_file_basic.test index 12362fa123c..0a169b472e2 100644 --- a/mysql-test/suite/sys_vars/t/general_log_file_basic.test +++ b/mysql-test/suite/sys_vars/t/general_log_file_basic.test @@ -58,6 +58,20 @@ SET @@global.general_log_file = mytest.log; --error ER_WRONG_TYPE_FOR_VAR SET @@global.general_log_file = 12; +# +# MDEV-10465 +# +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.general_log_file = 'my.cnf'; +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.general_log_file = '/tmp/my.cnf'; +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.general_log_file = '.my.cnf'; +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.general_log_file = 'my.cnf\0foo'; +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.general_log_file = 'my.ini'; + --echo '#----------------------FN_DYNVARS_004_03------------------------#' ############################################################################## diff --git a/mysql-test/suite/sys_vars/t/slow_query_log_file_basic.test b/mysql-test/suite/sys_vars/t/slow_query_log_file_basic.test index 28fc17f6077..69ca5f21f62 100644 --- a/mysql-test/suite/sys_vars/t/slow_query_log_file_basic.test +++ b/mysql-test/suite/sys_vars/t/slow_query_log_file_basic.test @@ -56,6 +56,20 @@ SET @@global.slow_query_log_file = mytest.log; --error ER_WRONG_TYPE_FOR_VAR SET @@global.slow_query_log_file = 12; +# +# MDEV-10465 +# +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.slow_query_log_file = 'my.cnf'; +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.slow_query_log_file = '/tmp/my.cnf'; +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.general_log_file = '.my.cnf'; +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.general_log_file = 'my.cnf\0foo'; +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.general_log_file = 'my.ini'; + --echo '#----------------------FN_DYNVARS_004_03------------------------#' ############################################################################## # Check if the value in GLOBAL Tables matches values in variable # diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index bd3ed4ad32d..7013009fae7 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1565,3 +1565,34 @@ EXECUTE stmt; EXECUTE stmt; DROP TABLE t1,t2,t3,t4,t5,t6; + +--echo # +--echo # MDEV-10500 CASE/IF Statement returns multiple values and shifts further result values to the next column +--echo # + +CREATE TABLE t1 ( + id int not null AUTO_INCREMENT, + active bool not null, + data1 bigint, + data2 bigint, + data3 bigint, + primary key (id) +); +INSERT INTO t1 (active,data1,data2,data3) VALUES (1,null,100,200); +SELECT + CASE WHEN active THEN SUM(data1) END AS C_1, + SUM(data2) AS C_2, + SUM(data3) AS C_3 +FROM t1; +SELECT + IF(active, SUM(data1), 5) AS C_1, + SUM(data2) AS C_2, + SUM(data3) AS C_3 +FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-10468 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() +--echo # +SELECT STDDEV_POP(f) FROM (SELECT "1e+309" AS f UNION SELECT "-1e+309" AS f) tbl; +SELECT STDDEV(f) FROM (SELECT 1.7976931348623157e+308 AS f UNION SELECT -1.7976931348623157e+308 AS f) tbl; diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 5d1f87e74a2..904e8700d26 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -578,11 +578,15 @@ select 5 div 2.0; select 5.9 div 2, 1.23456789e3 DIV 2, 1.23456789e9 DIV 2, 1.23456789e19 DIV 2; --echo # ---echo # End of 5.5 tests +--echo # MDEV-10467 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() --echo # +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1),(2); +SELECT STDDEV_SAMP(ROUND('0', 309)) FROM t1; +DROP TABLE t1; --echo # ---echo # Start of 10.0 tests +--echo # End of 5.5 tests --echo # --echo # @@ -590,7 +594,6 @@ select 5.9 div 2, 1.23456789e3 DIV 2, 1.23456789e9 DIV 2, 1.23456789e19 DIV 2; --echo # SELECT STDDEV_POP(ROUND(0,@A:=2009)) FROM (SELECT 1 UNION SELECT 2) fake_table; - --echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 26e379299fc..04fbfb713e2 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -596,6 +596,16 @@ AND 57813X540X1723 = 'Test'; drop table t1; +# +# Bug#12735545 - PARSER STACK OVERFLOW WITH NAME_CONST +# CONTAINING OR EXPRESSION +# +--error ER_WRONG_ARGUMENTS +SELECT NAME_CONST('a', -(1 OR 2)) OR 1; +--error ER_WRONG_ARGUMENTS +SELECT NAME_CONST('a', -(1 AND 2)) OR 1; +SELECT NAME_CONST('a', -(1)) OR 1; + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 35243864c04..7d0f3852b66 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -612,7 +612,7 @@ disconnect con1; --echo # CREATE TABLE t1(f1 INT); -EVAL SELECT 0xE1BB30 INTO OUTFILE 't1.dat'; +EVAL SELECT 0xE1C330 INTO OUTFILE 't1.dat'; --disable_warnings LOAD DATA INFILE 't1.dat' IGNORE INTO TABLE t1 CHARACTER SET utf8; --enable_warnings @@ -658,3 +658,26 @@ SET @@sql_mode= @old_mode; --remove_file $MYSQLTEST_VARDIR/mysql DROP TABLE t1; +--echo +--echo # +--echo # Bug#23080148 - Backport of Bug#20683959. +--echo # Bug#20683959 LOAD DATA INFILE IGNORES A SPECIFIC ROW SILENTLY +--echo # UNDER DB CHARSET IS UTF8. +--echo # + +CREATE DATABASE d1 CHARSET latin1; +USE d1; +CREATE TABLE t1 (val TEXT); +LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; +SELECT COUNT(*) FROM t1; +SELECT HEX(val) FROM t1; + +CREATE DATABASE d2 CHARSET utf8; +USE d2; +CREATE TABLE t1 (val TEXT); +--error ER_INVALID_CHARACTER_STRING +LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; + +DROP TABLE d1.t1, d2.t1; +DROP DATABASE d1; +DROP DATABASE d2; diff --git a/mysql-test/t/myisam_enable_keys-10506.test b/mysql-test/t/myisam_enable_keys-10506.test new file mode 100644 index 00000000000..8e1c058c3f0 --- /dev/null +++ b/mysql-test/t/myisam_enable_keys-10506.test @@ -0,0 +1,117 @@ +# +# MDEV-10506 Protocol::end_statement(): Assertion `0' failed upon ALTER TABLE +# +CREATE TABLE t1 ( + pk INT AUTO_INCREMENT, + i INT, + d DATE, + dt DATETIME, + v VARCHAR(1), + PRIMARY KEY (pk), + KEY (dt) +) ENGINE=MyISAM; +INSERT INTO t1 (i, d, dt, v) VALUES + (9, '2005-07-23', '2004-05-13 01:01:39', 't'), + (2, '2009-11-01', '2003-12-24 07:39:29', 'h'), + (6, NULL, '2008-07-03 05:32:22', 'l'), + (6, '2007-07-16', '2008-08-28 18:46:11', 'j'), + (5, NULL, '2001-07-12 21:27:00', 'h'), + (3, '2007-07-22', '1900-01-01 00:00:00', 'p'), + (2, '2000-11-21', '2007-05-25 11:58:54', 'g'), + (6, '1900-01-01', '2009-06-03 17:11:10', 'i'), + (2, '2008-02-10', '2001-06-15 16:20:07', 'p'), + (3, '2009-06-04', '1900-01-01 00:00:00', 'h'), + (9, '2007-04-25', '1900-01-01 00:00:00', 'e'), + (9, '2006-03-02', '1900-01-01 00:00:00', 'e'), + (1, '1900-01-01', '2002-11-08 09:33:27', 'u'), + (7, '2008-07-13', '2007-08-07 17:35:52', 'j'), + (0, '2004-11-12', '2006-05-01 00:00:00', 'e'), + (0, '1900-01-01', '2003-05-01 00:00:00', 'z'), + (1, '2009-09-02', '2007-02-12 09:30:49', 'w'), + (0, '2004-11-06', '1900-01-01 00:00:00', 't'), + (4, '2003-01-06', '2002-07-03 02:51:11', 'i'), + (6, '2006-01-14', '2008-02-26 04:57:32', 'i'), + (0, '2002-01-19', '2009-02-12 00:00:00', 'i'), + (8, '2007-02-12', '1900-01-01 00:00:00', 'b'), + (4, '1900-01-01', '2001-05-16 05:28:40', 'm'), + (2, '2005-07-16', NULL, 'j'), + (1, '2004-09-04', '2001-01-24 21:45:18', 'v'), + (3, '2009-07-01', NULL, NULL), + (2, '2009-07-21', '2002-07-24 00:00:00', 'h'), + (4, NULL, '2001-11-03 12:22:30', 'q'), + (1, '2002-06-22', '2008-06-17 03:17:59', 'f'), + (7, '2005-06-23', '2005-12-24 00:00:00', 'p'), + (6, '2001-05-20', '2008-10-23 00:00:00', NULL), + (3, '2001-10-01', '2000-10-12 16:32:35', 'o'), + (3, '2001-01-07', '2005-09-11 10:09:54', 'w'), + (6, '2007-11-02', '2009-09-10 01:44:18', 'l'), + (6, NULL, NULL, 'i'), + (9, NULL, '2002-05-18 15:21:55', 'd'), + (4, '2008-12-21', '2004-10-15 10:09:54', 'j'), + (6, '2003-10-05', '2009-07-13 03:51:02', 'e'), + (2, '2001-03-03', '1900-01-01 00:00:00', 'e'), + (2, '2007-04-04', '2001-11-08 21:14:52', 'q'), + (5, NULL, '2006-12-02 00:00:00', 'm'), + (0, '2009-01-04', '1900-01-01 00:00:00', NULL), + (8, '2008-04-03', '2005-01-01 11:55:18', 'q'), + (8, NULL, '2005-02-28 03:44:02', 'w'), + (0, '2003-08-22', NULL, 'c'), + (9, '1900-01-01', NULL, 'y'), + (NULL, NULL, '2006-08-25 16:28:09', 'g'), + (5, '2004-07-04', '2002-08-11 00:00:00', 'z'), + (1, '1900-01-01', '2007-07-22 21:19:18', 'm'), + (2, '2007-02-04', '2006-02-10 18:41:38', 't'), + (2, '1900-01-01', '2009-02-16 14:58:58', 'd'), + (7, '2001-03-14', '2007-08-14 00:00:00', 'h'), + (0, NULL, '1900-01-01 00:00:00', NULL), + (1, '2008-10-05', NULL, 'f'), + (6, '2001-11-25', '2008-12-03 06:59:23', 'l'), + (NULL, '2003-01-27', '2008-10-04 00:00:00', 'g'), + (8, '2008-08-08', '2009-07-07 07:00:21', 'v'), + (8, '2006-07-03', '2001-04-15 00:00:00', NULL), + (5, '2002-11-21', '2007-07-08 04:01:58', 'm'), + (5, '2006-04-08', '2007-09-23 00:01:35', 'i'), + (5, '2001-05-06', '2008-05-15 00:00:00', 'h'), + (7, '1900-01-01', '1900-01-01 00:00:00', 'u'), + (30, '2007-04-16', '2004-03-05 23:35:38', 'o'), + (NULL, '1900-01-01', '2007-08-25 01:32:47', 'z'), + (6, '2004-12-03', '1900-01-01 00:00:00', 'o'), + (8, '2001-06-23', '1900-01-01 00:00:00', 'f'), + (NULL, '2008-12-15', '2001-05-19 08:28:28', 'a'), + (9, '2000-02-15', '2009-09-03 06:07:22', 'd'), + (2, '2001-08-05', '2006-10-08 07:17:27', 'k'), + (5, '2004-01-17', '2003-09-06 20:36:01', 'd'), + (4, '2003-10-01', '2001-02-05 18:10:49', 'u'), + (4, '2003-07-28', '2001-01-07 16:11:37', 'h'), + (0, '1900-01-01', '2008-08-01 05:26:38', 'w'), + (9, '1900-01-01', '2001-05-08 00:00:00', 't'), + (1, '2000-04-17', '2008-07-10 21:26:28', 'i'), + (8, '2002-01-05', '2006-08-06 20:56:35', 'k'), + (9, '2001-04-10', '2003-02-17 00:00:00', 'z'), + (0, '2009-12-04', NULL, 'h'), + (7, NULL, '2004-10-27 00:29:57', 'h'), + (2, '2006-03-07', '2008-03-04 06:14:13', 'b'), + (0, '2001-10-15', '2001-03-17 00:00:00', 'm'), + (5, '1900-01-01', '2009-02-21 11:35:50', 'i'), + (4, NULL, '1900-01-01 00:00:00', 'w'), + (5, '2009-04-05', '1900-01-01 00:00:00', 'm'), + (6, '2001-03-19', '2001-04-12 00:00:00', 'q'), + (NULL, '2009-12-08', '2001-12-04 20:21:01', 'k'), + (2, '2005-02-09', '2001-05-27 08:41:01', 'l'), + (9, '2004-05-25', '2004-09-18 00:00:00', 'c'), + (3, '2005-01-17', '2002-09-12 11:18:48', 'd'), + (0, '2003-08-28', '1900-01-01 00:00:00', 'k'), + (6, '2006-10-11', '2003-10-28 03:31:02', 'a'), + (5, '1900-01-01', '2001-08-22 10:20:09', 'p'), + (8, '1900-01-01', '2008-04-24 00:00:00', 'o'), + (4, '2005-08-18', '2006-11-10 10:08:49', 'e'), + (NULL, '2007-03-12', '2007-10-16 00:00:00', 'n'), + (1, '2000-11-18', '2009-05-27 12:25:07', 't'), + (4, '2001-03-03', NULL, 'u'), + (3, '2003-09-11', '2001-09-10 18:10:10', 'f'), + (4, '2007-06-17', '1900-01-01 00:00:00', 't'), + (NULL, '2008-09-11', '2004-06-07 23:17:09', 'k'); +ALTER TABLE t1 ADD UNIQUE KEY ind1 (pk, d, i, v); +--error ER_DUP_ENTRY +ALTER TABLE t1 ADD UNIQUE KEY ind2 (d, v); +DROP TABLE t1; diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index e479a2232f0..779ea8d13d4 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -309,15 +309,36 @@ CHECK TABLE bug47205 FOR UPGRADE; DROP TABLE bug47205; + --echo # --echo #MDEV-6128:[PATCH] mysqlcheck wrongly escapes '.' in table names --echo # -CREATE TABLE test.`t.1` (id int); +create table `t.1` (id int); +create view `v.1` as select 1; --echo mysqlcheck test t.1 --exec $MYSQL_CHECK test t.1 +--echo mysqlcheck --all-in-1 test t.1 +--exec $MYSQL_CHECK --all-in-1 test t.1 +--echo mysqlcheck --all-in-1 --databases --process-views test +--exec $MYSQL_CHECK --all-in-1 --databases --process-views test + +create table `t.2`(a varchar(20) primary key) default character set utf8 collate utf8_general_ci engine=innodb; +flush table `t.2`; +--remove_file $MYSQLD_DATADIR/test/t@002e2.frm +--copy_file std_data/bug47205.frm $MYSQLD_DATADIR/test/t@002e2.frm + +--copy_file std_data/bug36055.frm $MYSQLD_DATADIR/test/t@002e3.frm +--copy_file std_data/bug36055.MYD $MYSQLD_DATADIR/test/t@002e3.MYD +--copy_file std_data/bug36055.MYI $MYSQLD_DATADIR/test/t@002e3.MYI -drop table test.`t.1`; +--echo mysqlcheck --check-upgrade --auto-repair test +--exec $MYSQL_CHECK --check-upgrade --auto-repair test + +check table `t.1`, `t.2`, `t.3`; +check table `t.1`, `t.2`, `t.3` for upgrade; +drop view `v.1`; +drop table test.`t.1`, `t.2`, `t.3`; --echo # --echo # MDEV-8123 mysqlcheck: new --process-views option conflicts with --quick, --extended and such @@ -355,6 +376,28 @@ create table `#mysql50#t1``1` (a int) engine=myisam; show tables; drop table `t1``1`; +# +# MDEV-9440 mysqlcheck -A --auto-repair selects wrong database when trying to repair broken table +# +call mtr.add_suppression("ha_myisam"); +call mtr.add_suppression("Checking table"); +create database mysqltest1; +create table mysqltest1.t1 (a int) engine=myisam; +create table t2 (a int); + +let $datadir= `select @@datadir`; +remove_file $datadir/mysqltest1/t1.MYD; +write_file $datadir/mysqltest1/t1.MYD; +foo +EOF + +check table mysqltest1.t1; + +--exec $MYSQL_CHECK -A --auto-repair --fast + +drop table t2; +drop database mysqltest1; + --echo # --echo #MDEV-7384 [PATCH] add PERSISENT FOR ALL option to mysqlanalyze/mysqlcheck --echo # diff --git a/mysql-test/t/named_pipe.test b/mysql-test/t/named_pipe.test index 8dcab3329e4..af74c200e96 100644 --- a/mysql-test/t/named_pipe.test +++ b/mysql-test/t/named_pipe.test @@ -22,3 +22,12 @@ connect(pipe_con,localhost,root,,,,,PIPE); connection default; disconnect pipe_con; + +# MDEV-10383 : check that other server cannot 'bind' on the same pipe +let $MYSQLD_DATADIR= `select @@datadir`; +--error 1 +--exec $MYSQLD_CMD --enable-named-pipe --skip-networking --log-error=second-mysqld.err +let SEARCH_FILE=$MYSQLD_DATADIR/second-mysqld.err; +let SEARCH_RANGE= -50; +let SEARCH_PATTERN=\[ERROR\] Create named pipe failed; +source include/search_pattern_in_file.inc; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 5c6dfdc9dd1..7cd1c44ec24 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1691,6 +1691,35 @@ select a, b from t2 where (a, b) in ((0, 0), (1, 1)); drop table t2; --echo # +--echo # MDEV-10228: Delete missing rows with OR conditions +--echo # (The example uses UPDATE, because UPDATE allows to use index hints +--echo # and so it's possible to make an example that works with any storage +--echo # engine) +--echo # + +CREATE TABLE t1 ( + key1varchar varchar(14) NOT NULL, + key2int int(11) NOT NULL DEFAULT '0', + col1 int, + PRIMARY KEY (key1varchar,key2int), + KEY key1varchar (key1varchar), + KEY key2int (key2int) +) DEFAULT CHARSET=utf8; + +insert into t1 values + ('value1',0, 0), + ('value1',1, 0), + ('value1',1000685, 0), + ('value1',1003560, 0), + ('value1',1004807, 0); + +update t1 force index (PRIMARY) set col1=12345 +where (key1varchar='value1' AND (key2int <=1 OR key2int > 1)); +--echo # The following must show col1=12345 for all rows: +select * from t1; +drop table t1; + +--echo # --echo # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE --echo # diff --git a/mysql-test/t/sp-prelocking.test b/mysql-test/t/sp-prelocking.test index 966c59a5789..c1378d59196 100644 --- a/mysql-test/t/sp-prelocking.test +++ b/mysql-test/t/sp-prelocking.test @@ -388,3 +388,29 @@ DROP TABLE t1, t2; --echo End of 5.0 tests +--echo # +--echo # Bug#21142859: FUNCTION UPDATING A VIEW FAILS TO FIND TABLE THAT ACTUALLY EXISTS +--echo # + +CREATE TABLE t1 SELECT 1 AS fld1, 'A' AS fld2; +CREATE TABLE t2 (fld3 INT, fld4 CHAR(1)); + +CREATE VIEW v1 AS SELECT * FROM t1; + +CREATE TRIGGER t1_au AFTER UPDATE ON t1 +FOR EACH ROW INSERT INTO t2 VALUES (new.fld1, new.fld2); + +DELIMITER !; +CREATE FUNCTION f1() RETURNS INT +BEGIN + UPDATE v1 SET fld2='B' WHERE fld1=1; + RETURN row_count(); +END ! +DELIMITER ;! + +--echo # Without the patch, an error was getting reported. +SELECT f1(); + +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE t1,t2; diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index e7e53fb4c37..91f9774969b 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -169,18 +169,8 @@ DROP TABLE t1; # CREATE TABLE t1 (a DATE, b INT, PRIMARY KEY (a,b)); -## The current sub test could fail (difference to expected result) if we -## have just reached midnight. -## (Bug#41776 type_date.test may fail if run around midnight) -## Therefore we sleep a bit if we are too close to midnight. -## The complete test itself needs in average less than 1 second. -## Therefore a time_distance to midnight of 5 seconds should be sufficient. -if (`SELECT CURTIME() > SEC_TO_TIME(24 * 3600 - 5)`) -{ - # We are here when CURTIME() is between '23:59:56' and '23:59:59'. - # So a sleep time of 5 seconds brings us between '00:00:01' and '00:00:04'. - --real_sleep 5 -} + +SET timestamp=UNIX_TIMESTAMP('2016-07-21 14:48:18'); INSERT INTO t1 VALUES (DATE(NOW()), 1); SELECT COUNT(*) FROM t1 WHERE a = NOW(); EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); @@ -192,6 +182,7 @@ EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; ALTER TABLE t1 DROP PRIMARY KEY; SELECT COUNT(*) FROM t1 WHERE a = NOW(); EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +SET timestamp=DEFAULT; DROP TABLE t1; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index b962903d0dc..241b3b414f6 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -5490,6 +5490,21 @@ alter table v1 check partition p1; drop view v1; drop table t1; + +--echo # +--echo # MDEV-10419: crash in mariadb 10.1.16-MariaDB-1~trusty +--echo # +CREATE TABLE t1 (c1 CHAR(13)); +CREATE TABLE t2 (c2 CHAR(13)); + +CREATE FUNCTION f() RETURNS INT RETURN 0; +CREATE OR REPLACE VIEW v1 AS select f() from t1 where c1 in (select c2 from t2); +DROP FUNCTION f; + +SHOW CREATE VIEW v1; + +drop view v1; +drop table t1,t2; --echo # ----------------------------------------------------------------- --echo # -- End of 5.5 tests. --echo # ----------------------------------------------------------------- diff --git a/mysql-test/t/xtradb_mrr.test b/mysql-test/t/xtradb_mrr.test index 260eb9f3955..d994c182ccc 100644 --- a/mysql-test/t/xtradb_mrr.test +++ b/mysql-test/t/xtradb_mrr.test @@ -33,8 +33,8 @@ insert into t2 select from t1 A, t1 B, t1 C; explain -select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a'; -select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a'; +select count(length(a) + length(filler)) from t2 force index (a) where a>='a-1000-a' and a <'a-1001-a'; +select count(length(a) + length(filler)) from t2 force index (a) where a>='a-1000-a' and a <'a-1001-a'; drop table t2; # Try a very big rowid |