diff options
Diffstat (limited to 'mysql-test/t')
85 files changed, 1744 insertions, 255 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index cfa6182543b..d2c9926c422 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -372,6 +372,103 @@ alter table t1 add unique ( a(1) ); drop table t1; # +# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table +# +# This problem happens if the data change is compatible. +# Changing to the same type is compatible for example. +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int, key(a)); +show indexes from t1; +--echo "this used not to disable the index" +alter table t1 modify a int, disable keys; +show indexes from t1; + +alter table t1 enable keys; +show indexes from t1; + +alter table t1 modify a bigint, disable keys; +show indexes from t1; + +alter table t1 enable keys; +show indexes from t1; + +alter table t1 add b char(10), disable keys; +show indexes from t1; + +alter table t1 add c decimal(10,2), enable keys; +show indexes from t1; + +--echo "this however did" +alter table t1 disable keys; +show indexes from t1; + +desc t1; + +alter table t1 add d decimal(15,5); +--echo "The key should still be disabled" +show indexes from t1; + +drop table t1; + +--echo "Now will test with one unique index" +create table t1(a int, b char(10), unique(a)); +show indexes from t1; +alter table t1 disable keys; +show indexes from t1; +alter table t1 enable keys; + +--echo "If no copy on noop change, this won't touch the data file" +--echo "Unique index, no change" +alter table t1 modify a int, disable keys; +show indexes from t1; + +--echo "Change the type implying data copy" +--echo "Unique index, no change" +alter table t1 modify a bigint, disable keys; +show indexes from t1; + +alter table t1 modify a bigint; +show indexes from t1; + +alter table t1 modify a int; +show indexes from t1; + +drop table t1; + +--echo "Now will test with one unique and one non-unique index" +create table t1(a int, b char(10), unique(a), key(b)); +show indexes from t1; +alter table t1 disable keys; +show indexes from t1; +alter table t1 enable keys; + + +--echo "If no copy on noop change, this won't touch the data file" +--echo "The non-unique index will be disabled" +alter table t1 modify a int, disable keys; +show indexes from t1; +alter table t1 enable keys; +show indexes from t1; + +--echo "Change the type implying data copy" +--echo "The non-unique index will be disabled" +alter table t1 modify a bigint, disable keys; +show indexes from t1; + +--echo "Change again the type, but leave the indexes as_is" +alter table t1 modify a int; +show indexes from t1; +--echo "Try the same. When data is no copied on similar tables, this is noop" +alter table t1 modify a int; +show indexes from t1; + +drop table t1; + + +# # Bug#11493 - Alter table rename to default database does not work without # db name qualifying # @@ -402,6 +499,35 @@ alter table test.t1 rename test.t1; use test; drop table t1; +# +# BUG#23404 - ROW_FORMAT=FIXED option is lost is an index is added to the +# table +# +CREATE TABLE t1(a INT) ROW_FORMAT=FIXED; +CREATE INDEX i1 ON t1(a); +SHOW CREATE TABLE t1; +DROP INDEX i1 ON t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# +# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash +# +--disable_warnings +DROP TABLE IF EXISTS bug24219; +DROP TABLE IF EXISTS bug24219_2; +--enable_warnings + +CREATE TABLE bug24219 (a INT, INDEX(a)); + +SHOW INDEX FROM bug24219; + +ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS; + +SHOW INDEX FROM bug24219_2; + +DROP TABLE bug24219_2; + # End of 4.1 tests # diff --git a/mysql-test/t/backup.test b/mysql-test/t/backup.test index 053e83528e0..a3339ecce69 100644 --- a/mysql-test/t/backup.test +++ b/mysql-test/t/backup.test @@ -1,6 +1,3 @@ -# This test should work in embedded server after we fix mysqltest --- source include/not_embedded.inc - # # This test is a bit tricky as we can't use backup table to overwrite an old # table diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test index ec05eeb3c34..d017d91bfb1 100644 --- a/mysql-test/t/bdb.test +++ b/mysql-test/t/bdb.test @@ -1019,39 +1019,4 @@ commit; alter table t1 add primary key(a); drop table t1; - -# -# Bug #16206: Superfluous COMMIT event in binlog when updating BDB in autocommit mode -# -set autocommit=1; - -let $VERSION=`select version()`; - -reset master; -create table bug16206 (a int); -insert into bug16206 values(1); -start transaction; -insert into bug16206 values(2); -commit; ---replace_result $VERSION VERSION ---replace_column 1 f 2 n 5 n -show binlog events; -drop table bug16206; - -reset master; -create table bug16206 (a int) engine= bdb; -insert into bug16206 values(0); -insert into bug16206 values(1); -start transaction; -insert into bug16206 values(2); -commit; -insert into bug16206 values(3); ---replace_result $VERSION VERSION ---replace_column 1 f 2 n 5 n -show binlog events; -drop table bug16206; - -set autocommit=0; - - --echo End of 5.0 tests diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index 35cda11646a..6c1229db83f 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -278,4 +278,13 @@ select * from t1 where bigint_col=17666000000000000000; select * from t1 where bigint_col='17666000000000000000'; drop table t1; +--echo +--echo bug 19955 -- mod is signed with bigint + +select cast(10000002383263201056 as unsigned) mod 50 as result; + +create table t1 (c1 bigint unsigned); +insert into t1 values (10000002383263201056); +select c1 mod 50 as result from t1; +drop table t1; diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index a81487d273d..3054ec53faa 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -326,4 +326,12 @@ SELECT TIME_FORMAT("25:00:00", '%l %p'); # SELECT DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896); +# +# Bug #22029: str_to_date returning NULL +# + +select str_to_date('04 /30/2004', '%m /%d/%Y'); +select str_to_date('04/30 /2004', '%m /%d /%Y'); +select str_to_date('04/30/2004 ', '%m/%d/%Y '); + --echo "End of 4.1 tests" diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test index b5522394d2d..225ddbc3ee2 100644 --- a/mysql-test/t/default.test +++ b/mysql-test/t/default.test @@ -82,3 +82,61 @@ SELECT * from t2; drop table t1; drop table t2; + +# +# Bug#20691: DATETIME col (NOT NULL, NO DEFAULT) may insert garbage when specifying DEFAULT +# +# From the docs: +# If the column can take NULL as a value, the column is defined with an +# explicit DEFAULT NULL clause. This is the same as before 5.0.2. +# +# If the column cannot take NULL as the value, MySQL defines the column with +# no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE +# statement includes no value for the column, MySQL handles the column +# according to the SQL mode in effect at the time: +# +# * If strict SQL mode is not enabled, MySQL sets the column to the +# implicit default value for the column data type. +# +# * If strict mode is enabled, an error occurs for transactional tables and +# the statement is rolled back. For non-transactional tables, an error +# occurs, but if this happens for the second or subsequent row of a +# multiple-row statement, the preceding rows will have been inserted. +# +create table bug20691 (i int, d datetime NOT NULL, dn datetime not null default '0000-00-00 00:00:00'); +insert into bug20691 values (1, DEFAULT, DEFAULT), (1, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (1, DEFAULT, DEFAULT); +insert into bug20691 (i) values (2); +desc bug20691; +insert into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (3, DEFAULT, DEFAULT); +insert into bug20691 (i) values (4); +insert into bug20691 values (5, DEFAULT, DEFAULT), (5, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (5, DEFAULT, DEFAULT); +SET sql_mode = 'ALLOW_INVALID_DATES'; +insert into bug20691 values (6, DEFAULT, DEFAULT), (6, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (6, DEFAULT, DEFAULT); +SET sql_mode = 'STRICT_ALL_TABLES'; +--error 1364 +insert into bug20691 values (7, DEFAULT, DEFAULT), (7, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (7, DEFAULT, DEFAULT); +select * from bug20691 order by i asc; +drop table bug20691; + +SET sql_mode = ''; +create table bug20691 ( + a set('one', 'two', 'three') not null, + b enum('small', 'medium', 'large', 'enormous', 'ellisonego') not null, + c time not null, + d date not null, + e int not null, + f long not null, + g blob not null, + h datetime not null, + i decimal not null, + x int); +insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 1); +insert into bug20691 (x) values (2); +insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 3); +insert into bug20691 values (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 4); +select * from bug20691 order by x asc; +drop table bug20691; + +### +--echo End of 5.0 tests. + diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test index 03d8e20dd8f..fe8bc167e0f 100644 --- a/mysql-test/t/delayed.test +++ b/mysql-test/t/delayed.test @@ -1,11 +1,10 @@ +# delayed works differently in embedded server +--source include/not_embedded.inc # # test of DELAYED insert and timestamps # (Can't be tested with purify :( ) # -# This tests not performed with embedded server --- source include/not_embedded.inc - --disable_warnings drop table if exists t1; --enable_warnings diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index df56165950f..3213bd4eb5b 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -10,5 +10,8 @@ # ############################################################################## +im_daemon_life_cycle : Bug#24415 see note: [19 Dec 23:17] Trudy Pelzer ndb_load : Bug#17233 user_limits : Bug#23921 random failure of user_limits.test +flush2 : Bug#24805 Pushbuild can't handle test with --disable-log-bin + diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index e517380ba9b..8734b940241 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -437,3 +437,91 @@ EXPLAIN SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; DROP TABLE t1; +# The test case for bug#20836 should be re-enabled when bug#16861 is resolved +# The results for the test should be the same as in 4.1. +# +#Bug #20836: Selecting into variables results in wrong results being returned +# +#--disable_warnings +#DROP TABLE IF EXISTS t1; +#--enable_warnings +# +#CREATE TABLE t1 (id INT NOT NULL, fruit_id INT NOT NULL, fruit_name varchar(20) +#default NULL); +# +#INSERT INTO t1 VALUES (1,1,'ORANGE'); +#INSERT INTO t1 VALUES (2,2,'APPLE'); +#INSERT INTO t1 VALUES (3,2,'APPLE'); +#INSERT INTO t1 VALUES (4,3,'PEAR'); +# +#SELECT DISTINCT fruit_id, fruit_name INTO @v1, @v2 FROM t1 WHERE fruit_name = +#'APPLE'; +#SELECT @v1, @v2; +# +#SELECT DISTINCT fruit_id, fruit_name INTO @v3, @v4 FROM t1 GROUP BY fruit_id, +#fruit_name HAVING fruit_name = 'APPLE'; +#SELECT @v3, @v4; +# +#SELECT DISTINCT @v5:= fruit_id, @v6:= fruit_name INTO @v7, @v8 FROM t1 WHERE +#fruit_name = 'APPLE'; +#SELECT @v5, @v6, @v7, @v8; +# +#SELECT DISTINCT @v5 + fruit_id, CONCAT(@v6, fruit_name) INTO @v9, @v10 FROM t1 +#WHERE fruit_name = 'APPLE'; +#SELECT @v5, @v6, @v7, @v8, @v9, @v10; +# +#SELECT DISTINCT @v11:= @v5 + fruit_id, @v12:= CONCAT(@v6, fruit_name) INTO +#@v13, @v14 FROM t1 WHERE fruit_name = 'APPLE'; +#SELECT @v11, @v12, @v13, @v14; +# +#SELECT DISTINCT @v13, @v14 INTO @v15, @v16 FROM t1 WHERE fruit_name = 'APPLE'; +#SELECT @v15, @v16; +# +#SELECT DISTINCT 2 + 2, 'Bob' INTO @v17, @v18 FROM t1 WHERE fruit_name = +#'APPLE'; +#SELECT @v17, @v18; +# +#--disable_warnings +#DROP TABLE IF EXISTS t2; +#--enable_warnings +# +#CREATE TABLE t2 (fruit_id INT NOT NULL, fruit_name varchar(20) +#default NULL); +# +#SELECT DISTINCT fruit_id, fruit_name INTO OUTFILE +#'../tmp/data1.tmp' FROM t1 WHERE fruit_name = 'APPLE'; +#LOAD DATA INFILE '../tmp/data1.tmp' INTO TABLE t2; +#--exec rm $MYSQL_TEST_DIR/var/tmp/data1.tmp +# +#SELECT DISTINCT @v19:= fruit_id, @v20:= fruit_name INTO OUTFILE +#'../tmp/data2.tmp' FROM t1 WHERE fruit_name = 'APPLE'; +#LOAD DATA INFILE '../tmp/data2.tmp' INTO TABLE t2; +#--exec rm $MYSQL_TEST_DIR/var/tmp/data2.tmp +# +#SELECT @v19, @v20; +#SELECT * FROM t2; +# +#DROP TABLE t1; +#DROP TABLE t2; + +# +# Bug #15881: cast problems +# +CREATE TABLE t1 (a CHAR(1)); INSERT INTO t1 VALUES('A'), (0); +SELECT a FROM t1 WHERE a=0; +SELECT DISTINCT a FROM t1 WHERE a=0; +DROP TABLE t1; +CREATE TABLE t1 (a DATE); +INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06'); +EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03'); +EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1 + WHERE ADDDATE(a,1) = '2002-08-03'); +CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci); +INSERT INTO t2 VALUES (0xf6); +INSERT INTO t2 VALUES ('oe'); + +SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt; +SELECT COUNT(*) FROM + (SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt; + +DROP TABLE t1, t2; diff --git a/mysql-test/t/execution_constants.test b/mysql-test/t/execution_constants.test index 00967b2eeba..4930164d3fa 100644 --- a/mysql-test/t/execution_constants.test +++ b/mysql-test/t/execution_constants.test @@ -1,3 +1,6 @@ +# In embedded server we don't really have a control over stack usage +-- source include/not_embedded.inc + # # Bug#21476: Lost Database Connection During Query # diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index c2218b3451b..894cd513914 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -1544,4 +1544,36 @@ drop table federated.test1, federated.test2; connection slave; drop table federated.test; +# +# BUG# 17044 Federated Storage Engine not UTF8 clean +# +connection slave; +set names utf8; +create table federated.t1 (a varchar(64)) DEFAULT CHARSET=utf8; + +insert into federated.t1 values (0x6DC3A56E6164); +select hex(a) from federated.t1; + +connection master; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval create table federated.t1 (a varchar(64)) +ENGINE=FEDERATED +connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1' +DEFAULT CHARSET=utf8; +set names utf8; +select hex(a) from federated.t1; +insert into federated.t1 values (0xC3A4C3B6C3BCC39F); +insert into federated.t1 values (0xD18DD184D184D0B5D0BAD182D0B8D0B2D0BDD183D18E); +select hex(a) from federated.t1; + +connection slave; +select hex(a) from federated.t1; + +connection master; +drop table federated.t1; + +connection slave; +drop table federated.t1; + + source include/federated_cleanup.inc; diff --git a/mysql-test/t/fix_priv_tables.test b/mysql-test/t/fix_priv_tables.test new file mode 100644 index 00000000000..3a91f41dfcc --- /dev/null +++ b/mysql-test/t/fix_priv_tables.test @@ -0,0 +1,66 @@ +# Embedded server doesn't support external clients +--source include/not_embedded.inc + +# +# This is the test for mysql_fix_privilege_tables +# It checks that a system tables from mysql 4.1.23 +# can be upgraded to current system table format +# +# Note: If this test fails, don't be confused about the errors reported +# by mysql-test-run This shows warnings generated by +# mysql_fix_system_tables which should be ignored. +# Instead, concentrate on the errors in r/system_mysql_db.reject + +--disable_warnings +drop table if exists t1,t1aa,t2aa; +--enable_warnings + +# +# Bug #20589 Missing some table level privileges after upgrade +# +# Add some grants that should survive the "upgrade" + +--disable_warnings +DROP DATABASE IF EXISTS testdb; +--enable_warnings +CREATE DATABASE testdb; +CREATE TABLE testdb.t1 ( + c1 INT, + c3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); + +CREATE VIEW testdb.v1 AS + SELECT * FROM testdb.t1; + +GRANT CREATE VIEW, SHOW VIEW ON testdb.v1 TO 'show_view_tbl'@'localhost'; +SHOW GRANTS FOR 'show_view_tbl'@'localhost'; +echo; + +# Some extra GRANTS for more complete test +GRANT SELECT(c1) on testdb.v1 to 'select_only_c1'@localhost; +SHOW GRANTS FOR 'select_only_c1'@'localhost'; +echo; + +-- disable_result_log +-- disable_query_log + +# Run the mysql_fix_privilege_tables.sql using "mysql --force" +--exec $MYSQL --force mysql < $MYSQL_FIX_PRIVILEGE_TABLES > $MYSQLTEST_VARDIR/log/fix_priv_tables.log 2>&1 + +-- enable_query_log +-- enable_result_log + +echo "after fix privs"; + +SHOW GRANTS FOR 'show_view_tbl'@'localhost'; +echo; +SHOW GRANTS FOR 'select_only_c1'@'localhost'; +echo; + +DROP USER 'show_view_tbl'@'localhost'; +DROP USER 'select_only_c1'@'localhost'; + +DROP VIEW testdb.v1; +DROP TABLE testdb.t1; +DROP DATABASE testdb; + +# End of 4.1 tests diff --git a/mysql-test/t/flush2-master.opt b/mysql-test/t/flush2-master.opt new file mode 100644 index 00000000000..ccbd01c91d3 --- /dev/null +++ b/mysql-test/t/flush2-master.opt @@ -0,0 +1 @@ +--disable-log-bin diff --git a/mysql-test/t/flush2.test b/mysql-test/t/flush2.test new file mode 100644 index 00000000000..fc9e88e3141 --- /dev/null +++ b/mysql-test/t/flush2.test @@ -0,0 +1,9 @@ +# +# Bug#17733 Flushing logs causes daily server crash +# +flush logs; +set global expire_logs_days = 3; +show variables like 'log%'; +flush logs; +show variables like 'log%'; +set global expire_logs_days = 0; diff --git a/mysql-test/t/flush_block_commit.test b/mysql-test/t/flush_block_commit.test index 4412b2c357c..0c1d2b82df6 100644 --- a/mysql-test/t/flush_block_commit.test +++ b/mysql-test/t/flush_block_commit.test @@ -75,24 +75,3 @@ show create database test; drop table t1; # End of 4.1 tests - -# FLUSH TABLES WITH READ LOCK should block writes to binlog too -connection con1; -create table t1 (a int) engine=innodb; -reset master; -set autocommit=0; -insert t1 values (1); -connection con2; -flush tables with read lock; -show master status; -connection con1; -send commit; -connection con2; -sleep 1; -show master status; -unlock tables; -connection con1; -reap; -drop table t1; -set autocommit=1; - diff --git a/mysql-test/t/flush_block_commit_notembedded.test b/mysql-test/t/flush_block_commit_notembedded.test new file mode 100644 index 00000000000..4650a5a15a8 --- /dev/null +++ b/mysql-test/t/flush_block_commit_notembedded.test @@ -0,0 +1,32 @@ +# Let's see if FLUSH TABLES WITH READ LOCK blocks COMMIT of existing +# transactions. +# We verify that we did not introduce a deadlock. +# This is intended to mimick how mysqldump and innobackup work. + +# And it requires InnoDB +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +# FLUSH TABLES WITH READ LOCK should block writes to binlog too +connection con1; +create table t1 (a int) engine=innodb; +reset master; +set autocommit=0; +insert t1 values (1); +connection con2; +flush tables with read lock; +show master status; +connection con1; +send commit; +connection con2; +sleep 1; +show master status; +unlock tables; +connection con1; +reap; +drop table t1; +set autocommit=1; + diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 610c93dfb0f..3ff4b35873b 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -29,8 +29,10 @@ select grp,group_concat(c order by c) from t1 group by grp; select grp,group_concat(c order by c desc) from t1 group by grp; select grp,group_concat(d order by a) from t1 group by grp; select grp,group_concat(d order by a desc) from t1 group by grp; +--disable_warnings select grp,group_concat(a order by a,d+c-ascii(c)-a) from t1 group by grp; select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp; +--enable_warnings select grp,group_concat(c order by 1) from t1 group by grp; select grp,group_concat(distinct c order by c) from t1 group by grp; select grp,group_concat(distinct c order by c desc) from t1 group by grp; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 089f5ed9911..bccd4d9e762 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -29,7 +29,9 @@ select count(distinct a),count(distinct grp) from t1; select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1; select grp, sum(a),count(a),avg(a),std(a),variance(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp; +--disable_warnings select grp, sum(a)+count(a)+avg(a)+std(a)+variance(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp; +--enable_warnings create table t2 (grp int, a bigint unsigned, c char(10)); insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp; diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 52a5512d070..5cac6c45f65 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -125,4 +125,11 @@ drop table t2; drop table t1; set global query_cache_size=default; +# +# Bug #21466: INET_ATON() returns signed, not unsigned +# + +create table t1 select INET_ATON('255.255.0.1') as `a`; +show create table t1; +drop table t1; --echo End of 5.0 tests diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index af4f8b9a9d2..a55b633b91e 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -991,5 +991,13 @@ SELECT CHAR(0xff,0x8f USING utf8) IS NULL; SET SQL_MODE=@orig_sql_mode; +# +# Bug #24947: problem with some string function with unsigned int parameters +# + +select substring('abc', cast(2 as unsigned int)); +select repeat('a', cast(2 as unsigned int)); +select rpad('abc', cast(5 as unsigned integer), 'x'); +select lpad('abc', cast(5 as unsigned integer), 'x'); --echo End of 5.0 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index a3985998b13..a69cbb67c5b 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -564,6 +564,12 @@ union union (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 HOUR)),'%k') As H); +# +# Bug #23653: crash if last_day('0000-00-00') +# + +select last_day('0000-00-00'); + --echo End of 4.1 tests explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1, @@ -701,3 +707,9 @@ SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868; DROP TABLE testBug8868; SET NAMES DEFAULT; + +# +# Bug #22229: bug in DATE_ADD() +# + +select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE; diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index e34dd14dbfc..1704fe7dc80 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -254,3 +254,20 @@ INSERT INTO t1() VALUES (); --error 1416 INSERT INTO t1(foo) VALUES (''); DROP TABLE t1; + +# +# Bug #23578: Corruption prevents Optimize table from working properly with a +# spatial index +# + +CREATE TABLE t1 (a INT AUTO_INCREMENT, b POINT NOT NULL, KEY (a), SPATIAL KEY (b)); + +INSERT INTO t1 (b) VALUES (GeomFromText('POINT(1 2)')); +INSERT INTO t1 (b) SELECT b FROM t1; +INSERT INTO t1 (b) SELECT b FROM t1; +INSERT INTO t1 (b) SELECT b FROM t1; +INSERT INTO t1 (b) SELECT b FROM t1; +INSERT INTO t1 (b) SELECT b FROM t1; + +OPTIMIZE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 7bba34be3ff..95fdf642b94 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -375,6 +375,20 @@ drop table t1; select (asWKT(geomfromwkb((0x000000000140240000000000004024000000000000)))); select (asWKT(geomfromwkb((0x010100000000000000000024400000000000002440)))); +--enable_metadata +create table t1 (g GEOMETRY); +select * from t1; +select asbinary(g) from t1; +--disable_metadata +drop table t1; + +create table t1 (a TEXT, b GEOMETRY NOT NULL, SPATIAL KEY(b)); +alter table t1 disable keys; +--error 1263 +load data infile '../std_data_ln/bad_gis_data.dat' into table t1; +alter table t1 enable keys; +drop table t1; + # End of 4.1 tests # @@ -410,15 +424,7 @@ alter table t1 add primary key pti(pt); alter table t1 add primary key pti(pt(20)); drop table t1; ---enable_metadata -create table t1 (g GEOMETRY); -select * from t1; -select asbinary(g) from t1; ---disable_metadata -drop table t1; - create table t1 select GeomFromText('point(1 1)'); desc t1; drop table t1; - diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 8f88c98caa7..b3049498f90 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -810,3 +810,63 @@ explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; DROP TABLE t1,t2; + +# +# Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements +# + +CREATE TABLE t1 (a INT, b INT, INDEX (a,b)); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), + (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; +FLUSH STATUS; +SELECT max(b), a FROM t1 GROUP BY a; +SHOW STATUS LIKE 'handler_read__e%'; +EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; +FLUSH STATUS; +CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a; +SHOW STATUS LIKE 'handler_read__e%'; +FLUSH STATUS; +SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b; +SHOW STATUS LIKE 'handler_read__e%'; +FLUSH STATUS; +(SELECT max(b), a FROM t1 GROUP BY a) UNION + (SELECT max(b), a FROM t1 GROUP BY a); +SHOW STATUS LIKE 'handler_read__e%'; +EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION + (SELECT max(b), a FROM t1 GROUP BY a); + +EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x + FROM t1 AS t1_outer; +EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS + (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); +EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE + (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; +EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE + a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); +EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING + a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); +EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 + ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) + AND t1_outer1.b = t1_outer2.b; +EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x + FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2; + +CREATE TABLE t3 LIKE t1; +FLUSH STATUS; +INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a; +SHOW STATUS LIKE 'handler_read__e%'; +DELETE FROM t3; +FLUSH STATUS; +INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) + FROM t1 LIMIT 1; +SHOW STATUS LIKE 'handler_read__e%'; +FLUSH STATUS; +DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000; +SHOW STATUS LIKE 'handler_read__e%'; +FLUSH STATUS; +DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x + FROM t1) > 10000; +SHOW STATUS LIKE 'handler_read__e%'; + +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/index_merge_ror.test b/mysql-test/t/index_merge_ror.test index 48fe5526f11..a9de2f955ee 100644 --- a/mysql-test/t/index_merge_ror.test +++ b/mysql-test/t/index_merge_ror.test @@ -250,3 +250,18 @@ select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA'; insert into t2 values ('ab', 'ab', 'uh', 'oh'); explain select a from t2 where a='ab'; drop table t2; + +# +# BUG#25048 - ERROR 126 : Incorrect key file for table '.XXXX.MYI'; try to +# repair it +# +CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '', +KEY(c1), KEY(c2), KEY(c3)); +INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0), +(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0); +INSERT INTO t1 VALUES(0,0,0); +CREATE TABLE t2(c1 int); +INSERT INTO t2 VALUES(1); +DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0; +SELECT * FROM t1; +DROP TABLE t1,t2; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index dd203add344..623ccee49e4 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -715,7 +715,7 @@ create temporary table schemata(f1 char(10)); # Bug #10708 SP's can use INFORMATION_SCHEMA as ROUTINE_SCHEMA # delimiter |; ---error 1044 +--error ER_BAD_DB_ERROR CREATE PROCEDURE p1 () BEGIN SELECT 'foo' FROM DUAL; diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test index 4dfe1ad56b5..e15e50e8766 100644 --- a/mysql-test/t/information_schema_db.test +++ b/mysql-test/t/information_schema_db.test @@ -1,3 +1,7 @@ +# this test mostly test privilege control (what doesn't work +# in the embedded server by default). So disabled in embedded-server mode +-- source include/not_embedded.inc + -- source include/testdb_only.inc --disable_warnings diff --git a/mysql-test/t/init_connect.test b/mysql-test/t/init_connect.test index 31a98df33df..cf98f608982 100644 --- a/mysql-test/t/init_connect.test +++ b/mysql-test/t/init_connect.test @@ -232,7 +232,9 @@ connection con0; disconnect con1; drop trigger trg1; -set global init_connect=default; +# Set init connect back to the value provided in init_connect-master.opt +# doesn't matter as server will be restarted +set global init_connect="set @a='a\\0c'"; revoke all privileges, grant option from mysqltest1@localhost; drop user mysqltest1@localhost; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 4a1efc9e566..20f07ccdde0 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -2154,6 +2154,7 @@ select * from t1, t2 drop table t1, t2; # bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID" +--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / --error 1005 CREATE TABLE t1 (DB_ROW_ID int) engine=innodb; diff --git a/mysql-test/t/innodb_mysql-master.opt b/mysql-test/t/innodb_mysql-master.opt new file mode 100644 index 00000000000..205c733455d --- /dev/null +++ b/mysql-test/t/innodb_mysql-master.opt @@ -0,0 +1 @@ +--innodb-lock-wait-timeout=2 diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index 06cfe71ef11..45a2ede091b 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -384,4 +384,6 @@ EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; DROP TABLE t1; +--source include/innodb_rollback_on_timeout.inc + --echo End of 5.0 tests diff --git a/mysql-test/t/innodb_timeout_rollback-master.opt b/mysql-test/t/innodb_timeout_rollback-master.opt new file mode 100644 index 00000000000..50921bb4df0 --- /dev/null +++ b/mysql-test/t/innodb_timeout_rollback-master.opt @@ -0,0 +1 @@ +--innodb_lock_wait_timeout=2 --innodb_rollback_on_timeout diff --git a/mysql-test/t/innodb_timeout_rollback.test b/mysql-test/t/innodb_timeout_rollback.test new file mode 100644 index 00000000000..99890971064 --- /dev/null +++ b/mysql-test/t/innodb_timeout_rollback.test @@ -0,0 +1,5 @@ +-- source include/have_innodb.inc + +--source include/innodb_rollback_on_timeout.inc + +--echo End of 5.0 tests diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index ac43d0bc818..029c4d19e63 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -177,18 +177,6 @@ select count(*) from t2; drop table t1,t2,t3; # -# Test for INSERT DELAYED INTO a <view> -# BUG#13683: INSERT DELAYED into a view creates an infinite loop -# - -create table t1 (n int); -create view v1 as select * from t1; ---error 1347 -insert delayed into v1 values (1); -drop table t1; -drop view v1; - -# # Test for values returned by ROW_COUNT() function # (and thus for values returned by mysql_affected_rows()) # for various forms of INSERT diff --git a/mysql-test/t/insert_notembedded.test b/mysql-test/t/insert_notembedded.test new file mode 100644 index 00000000000..bdea72e9eca --- /dev/null +++ b/mysql-test/t/insert_notembedded.test @@ -0,0 +1,154 @@ +-- source include/not_embedded.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# Test for INSERT DELAYED INTO a <view> +# BUG#13683: INSERT DELAYED into a view creates an infinite loop +# + +create table t1 (n int); +create view v1 as select * from t1; +--error 1347 +insert delayed into v1 values (1); +drop table t1; +drop view v1; + +# +# Bug #20989: View '(null).(null)' references invalid table(s)... on +# SQL SECURITY INVOKER +# +# this is really the fact that REPLACE ... SELECT required additional +# INSERT privs (on tables that are part of a view) over the related +# REPLACE, SELECT +# + +CREATE DATABASE meow; + +connect (root,localhost,root,,meow); +connection root; + +CREATE TABLE table_target ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); +CREATE TABLE table_target2 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); +CREATE TABLE table_target3 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); +CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2; +CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3; + +CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES countries (country) ON UPDATE CASCADE); +INSERT INTO table_stations VALUES ('87654321','XXXX','YY'); + +CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country)); +INSERT INTO table_countries VALUES ('YY','Entenhausen'); + +CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country))); + +CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id)); +INSERT INTO table_source VALUES ('XXXX','2006-07-12 07:50:00'); + +GRANT SELECT ON table_source TO user20989@localhost; +GRANT SELECT ON table_countries TO user20989@localhost; +GRANT SELECT ON table_stations TO user20989@localhost; +GRANT SELECT ON view_stations TO user20989@localhost; +GRANT SELECT ON table_target TO user20989@localhost; +GRANT SELECT ON table_target2 TO user20989@localhost; +GRANT INSERT,DELETE,SELECT ON view_target3 TO user20989@localhost; + +connect (user20989,localhost,user20989,,meow); +connection user20989; + +--error 1142 +REPLACE INTO table_target +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN table_target AS old +USING (mexs_id); + +--error 1142 +REPLACE INTO view_target2 +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target2 AS old +USING (mexs_id); + +--error 1356 +REPLACE INTO view_target3 +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target3 AS old +USING (mexs_id); + +connection root; +disconnect user20989; + +GRANT INSERT,DELETE ON table_target TO user20989@localhost; +GRANT INSERT,DELETE,SELECT ON view_target2 TO user20989@localhost; +GRANT INSERT,DELETE,SELECT ON table_target3 TO user20989@localhost; + +connect (user20989,localhost,user20989,,meow); +connection user20989; + +REPLACE INTO table_target +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN table_target AS old +USING (mexs_id); + +--error 1142 +REPLACE INTO table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00'); +REPLACE INTO view_target2 VALUES ('12X45Y78','2006-07-12 07:50:00'); + +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target2 AS old +USING (mexs_id); + +REPLACE INTO view_target2 +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target2 AS old +USING (mexs_id); + +REPLACE INTO view_target3 +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target3 AS old +USING (mexs_id); + +connection root; +disconnect user20989; + +SELECT * FROM table_target; +SELECT * FROM view_target2; +SELECT * FROM view_target3; + +DROP VIEW view_stations; +DROP TABLE table_source; +DROP TABLE table_countries; +DROP TABLE table_stations; +DROP TABLE table_target; +DROP TABLE table_target2; +DROP TABLE table_target3; +DROP VIEW view_target2; +DROP VIEW view_target3; +DROP USER user20989@localhost; + +disconnect root; + +connection default; + +DROP DATABASE meow; diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 6f86ed897ac..404d67390ab 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -266,140 +266,3 @@ INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1; DROP DATABASE bug21774_1; DROP DATABASE bug21774_2; -# -# Bug #20989: View '(null).(null)' references invalid table(s)... on -# SQL SECURITY INVOKER -# -# this is really the fact that REPLACE ... SELECT required additional -# INSERT privs (on tables that are part of a view) over the related -# REPLACE, SELECT -# - -CREATE DATABASE meow; - -connect (root,localhost,root,,meow); -connection root; - -CREATE TABLE table_target ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); -CREATE TABLE table_target2 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); -CREATE TABLE table_target3 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); -CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2; -CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3; - -CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES countries (country) ON UPDATE CASCADE); -INSERT INTO table_stations VALUES ('87654321','XXXX','YY'); - -CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country)); -INSERT INTO table_countries VALUES ('YY','Entenhausen'); - -CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country))); - -CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id)); -INSERT INTO table_source VALUES ('XXXX','2006-07-12 07:50:00'); - -GRANT SELECT ON table_source TO user20989@localhost; -GRANT SELECT ON table_countries TO user20989@localhost; -GRANT SELECT ON table_stations TO user20989@localhost; -GRANT SELECT ON view_stations TO user20989@localhost; -GRANT SELECT ON table_target TO user20989@localhost; -GRANT SELECT ON table_target2 TO user20989@localhost; -GRANT INSERT,DELETE,SELECT ON view_target3 TO user20989@localhost; - -connect (user20989,localhost,user20989,,meow); -connection user20989; - ---error 1142 -REPLACE INTO table_target -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN table_target AS old -USING (mexs_id); - ---error 1142 -REPLACE INTO view_target2 -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN view_target2 AS old -USING (mexs_id); - ---error 1356 -REPLACE INTO view_target3 -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN view_target3 AS old -USING (mexs_id); - -connection root; -disconnect user20989; - -GRANT INSERT,DELETE ON table_target TO user20989@localhost; -GRANT INSERT,DELETE,SELECT ON view_target2 TO user20989@localhost; -GRANT INSERT,DELETE,SELECT ON table_target3 TO user20989@localhost; - -connect (user20989,localhost,user20989,,meow); -connection user20989; - -REPLACE INTO table_target -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN table_target AS old -USING (mexs_id); - ---error 1142 -REPLACE INTO table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00'); -REPLACE INTO view_target2 VALUES ('12X45Y78','2006-07-12 07:50:00'); - -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN view_target2 AS old -USING (mexs_id); - -REPLACE INTO view_target2 -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN view_target2 AS old -USING (mexs_id); - -REPLACE INTO view_target3 -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN view_target3 AS old -USING (mexs_id); - -connection root; -disconnect user20989; - -SELECT * FROM table_target; -SELECT * FROM view_target2; -SELECT * FROM view_target3; - -DROP VIEW view_stations; -DROP TABLE table_source; -DROP TABLE table_countries; -DROP TABLE table_stations; -DROP TABLE table_target; -DROP TABLE table_target2; -DROP TABLE table_target3; -DROP VIEW view_target2; -DROP VIEW view_target3; -DROP USER user20989@localhost; - -disconnect root; - -connection default; - -DROP DATABASE meow; diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 4a07f495a55..99dd21e8ee2 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -520,6 +520,8 @@ select * from v1a join v1b on t1.b = t2.b; # # Bug #17523 natural join and information_schema # +# We mask out the Privileges column because it differs with embedded server +--replace_column 31 # select * from information_schema.statistics join information_schema.columns using(table_name,column_name) where table_name='user'; @@ -589,3 +591,23 @@ select * from ((t3 natural join (t1 natural join t2)) natural join t4) drop table t1, t2, t3, t4, t5; # End of tests for WL#2486 - natural/using join + +# +# BUG#25106: A USING clause in combination with a VIEW results in column +# aliases ignored +# +CREATE TABLE t1 (ID INTEGER, Name VARCHAR(50)); +CREATE TABLE t2 (Test_ID INTEGER); +CREATE VIEW v1 (Test_ID, Description) AS SELECT ID, Name FROM t1; + +CREATE TABLE tv1 SELECT Description AS Name FROM v1 JOIN t2 + USING (Test_ID); +DESCRIBE tv1; +CREATE TABLE tv2 SELECT Description AS Name FROM v1 JOIN t2 + ON v1.Test_ID = t2.Test_ID; +DESCRIBE tv2; + +DROP VIEW v1; +DROP TABLE t1,t2,tv1,tv2; + +--echo End of 5.0 tests. diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index 69886d035bf..e7405418be7 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -994,3 +994,54 @@ SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa DROP TABLE t1,t2,t3,t4,t5; +# +# Test for bug #24345: crash with nested left outer join when outer table is substituted +# for a row that happens to have a null value for the join attribute. +# + +CREATE TABLE t1 ( + id int NOT NULL PRIMARY KEY, + ct int DEFAULT NULL, + pc int DEFAULT NULL, + INDEX idx_ct (ct), + INDEX idx_pc (pc) +); +INSERT INTO t1 VALUES + (1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5,NULL,NULL); + +CREATE TABLE t2 ( + id int NOT NULL PRIMARY KEY, + sr int NOT NULL, + nm varchar(255) NOT NULL, + INDEX idx_sr (sr) +); +INSERT INTO t2 VALUES + (2441905,4308,'LesAbymes'),(2441906,4308,'Anse-Bertrand'); + +CREATE TABLE t3 ( + id int NOT NULL PRIMARY KEY, + ct int NOT NULL, + ln int NOT NULL, + INDEX idx_ct (ct), + INDEX idx_ln (ln) +); + +CREATE TABLE t4 ( + id int NOT NULL PRIMARY KEY, + nm varchar(255) NOT NULL +); + +INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique'); + +SELECT t1.* + FROM t1 LEFT JOIN + (t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id + WHERE t1.id='5'; + +SELECT t1.*, t4.nm + FROM t1 LEFT JOIN + (t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id + LEFT JOIN t4 ON t2.sr=t4.id + WHERE t1.id='5'; + +DROP TABLE t1,t2,t3,t4; diff --git a/mysql-test/t/key_cache.test b/mysql-test/t/key_cache.test index 4001e0df4af..3044964ebc3 100644 --- a/mysql-test/t/key_cache.test +++ b/mysql-test/t/key_cache.test @@ -164,16 +164,19 @@ create table t1 (mytext text, FULLTEXT (mytext)); insert t1 values ('aaabbb'); check table t1; +set @my_key_cache_block_size= @@global.key_cache_block_size; set GLOBAL key_cache_block_size=2048; check table t1; - drop table t1; +# Restore the changed variable value +set global key_cache_block_size= @my_key_cache_block_size; # # Bug #19079: corrupted index when key_cache_block_size is not multiple of # myisam_block_size CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY); +SET @my_key_cache_block_size= @@global.key_cache_block_size; SET GLOBAL key_cache_block_size=1536; INSERT INTO t1 VALUES (1); SELECT @@key_cache_block_size; @@ -206,6 +209,8 @@ SELECT COUNT(*) FROM t1; SELECT @@key_cache_block_size; CHECK TABLE t1; DROP TABLE t1,t2; +# Restore changed variables +set global key_cache_block_size= @my_key_cache_block_size; # # Bug#10473 - Can't set 'key_buffer_size' system variable to ZERO diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test index 2eb4e6cbbb2..bc599b20d36 100644 --- a/mysql-test/t/limit.test +++ b/mysql-test/t/limit.test @@ -69,5 +69,5 @@ explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; - +drop table t1; # End of 4.1 tests diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index d3eeee3b41c..a279b715003 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -1,3 +1,4 @@ +-- source include/not_embedded.inc --disable_warnings drop table if exists t1,t2; --enable_warnings diff --git a/mysql-test/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test index 883315994fe..414027cb485 100644 --- a/mysql-test/t/lowercase_fs_off.test +++ b/mysql-test/t/lowercase_fs_off.test @@ -21,6 +21,7 @@ create database D1; disconnect sample; connection master; +drop user 'sample'@'localhost'; drop database if exists d1; disconnect master; connection default; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index d785002abdd..02f914ae094 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -498,7 +498,7 @@ insert into t1 values (1),(2),(3),(4),(5),(6); insert into t2 values (1,1),(2,1); lock tables t1 read local, t2 read local; select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; -connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock); +connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); insert into t2 values(2,0); disconnect root; connection default; diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index cb4a5593de7..3d022eb27e4 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -241,4 +241,22 @@ drop table t17583; --error 1 --exec $MYSQL test -e "\r test cyrils_superlonghostnameXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" 2>&1 +# +# Bug #21412: mysql cmdline client allows backslash(es) +# as delimiter but can't recognize them +# + +# This should work just fine... +--exec echo "DELIMITER /" > $MYSQLTEST_VARDIR/tmp/bug21412.sql +--exec echo "SELECT 1/" >> $MYSQLTEST_VARDIR/tmp/bug21412.sql +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21412.sql 2>&1 + +# This should give an error... +--exec echo "DELIMITER \\" > $MYSQLTEST_VARDIR/tmp/bug21412.sql +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21412.sql 2>&1 + +# As should this... +--exec echo "DELIMITER \\\\" > $MYSQLTEST_VARDIR/tmp/bug21412.sql +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21412.sql 2>&1 + --echo End of 5.0 tests diff --git a/mysql-test/t/mysql_delimiter.sql b/mysql-test/t/mysql_delimiter.sql index fa80c980b29..db679c3b06b 100644 --- a/mysql-test/t/mysql_delimiter.sql +++ b/mysql-test/t/mysql_delimiter.sql @@ -49,3 +49,13 @@ delimiter ; # Reset delimiter # Bug #11523: \d works differently than delimiter # source t/mysql_delimiter_source.sql +delimiter ; # Reset delimiter + +# +# Bug #19799: delimiter command not working correctly when sourcing a sql file +# with Windows style line endings. +# +source t/mysql_delimiter_19799.sql +use test// +show tables// +delimiter ; # Reset delimiter diff --git a/mysql-test/t/mysql_delimiter_19799.sql b/mysql-test/t/mysql_delimiter_19799.sql new file mode 100755 index 00000000000..2a3d4378492 --- /dev/null +++ b/mysql-test/t/mysql_delimiter_19799.sql @@ -0,0 +1 @@ +delimiter // diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test new file mode 100644 index 00000000000..c6133f8e85f --- /dev/null +++ b/mysql-test/t/mysql_upgrade.test @@ -0,0 +1,23 @@ +# Can't run test of external client with embedded server +-- source include/not_embedded.inc + +# Only run test if "mysql_upgrade" is found +--require r/have_mysql_upgrade.result +--disable_query_log +select LENGTH("$MYSQL_UPGRADE")>0 as have_mysql_upgrade; +--enable_query_log + +# +# Basic test thta we can run mysql_upgrde and that it finds the +# expected binaries it uses. +# +--echo Run mysql_upgrade once +--exec $MYSQL_UPGRADE 2> $MYSQLTEST_VARDIR/log/mysql_upgrade.err + +--echo Run it again - should say already completed +--exec $MYSQL_UPGRADE 2> $MYSQLTEST_VARDIR/log/mysql_upgrade.err + +--echo Force should run it regardless of wheter it's been run before +--exec $MYSQL_UPGRADE --force 2> $MYSQLTEST_VARDIR/log/mysql_upgrade.err + + diff --git a/mysql-test/t/mysqladmin.test b/mysql-test/t/mysqladmin.test new file mode 100644 index 00000000000..3fa03fa910e --- /dev/null +++ b/mysql-test/t/mysqladmin.test @@ -0,0 +1,34 @@ +# Embedded server doesn't support external clients +--source include/not_embedded.inc +# +# Test "mysqladmin ping" +# + +--exec $MYSQLADMIN --no-defaults -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 + + +# +# Bug#10608 mysqladmin breaks on "database" variable in my.cnf +# + +# When mysqladmin finds database in .cnf file it shall fail +--write_file $MYSQLTEST_VARDIR/tmp/bug10608.cnf +[client] +database=db1 +EOF + +--replace_regex /.*mysqladmin.*: unknown/mysqladmin: unknown/ +--error 7 +--exec $MYSQLADMIN --defaults-file=$MYSQLTEST_VARDIR/tmp/bug10608.cnf -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 + + +# When mysqladmin finds "loose-database" in .cnf file it shall print +# a warning and continue +--write_file $MYSQLTEST_VARDIR/tmp/bug10608.cnf +[client] +loose-database=db2 +EOF + +--replace_regex /Warning: .*mysqladmin.*: unknown/Warning: mysqladmin: unknown/ +--exec $MYSQLADMIN --defaults-file=$MYSQLTEST_VARDIR/tmp/bug10608.cnf -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= ping 2>&1 + diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 72aad395ec0..5e604061744 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1341,7 +1341,7 @@ connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; create database mysqldump_myDB; use mysqldump_myDB; -create user myDB_User; +create user myDB_User@localhost; grant create, create view, select, insert on mysqldump_myDB.* to myDB_User@localhost; create table t1 (c1 int); insert into t1 values (3); @@ -1364,7 +1364,7 @@ drop view v1; drop table t1; drop table u1; revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; -drop user myDB_User; +drop user myDB_User@localhost; drop database mysqldump_myDB; flush privileges; @@ -1389,7 +1389,7 @@ drop view v1; drop table t1; drop table u1; revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; -drop user myDB_User; +drop user myDB_User@localhost; drop database mysqldump_myDB; use test; diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index 3c20b38722f..c06d51d9d49 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -1522,6 +1522,46 @@ remove_file $MYSQLTEST_VARDIR/tmp/file2.tmp; --exec echo "copy_file from_file;" | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- +# test for chmod +# ---------------------------------------------------------------------------- +--write_file $MYSQLTEST_VARDIR/tmp/file1.tmp +file1 +EOF + +chmod 0000 $MYSQLTEST_VARDIR/tmp/file1.tmp; +# The below write fails, but --error is not implemented +# for write_file +#--write_file $MYSQLTEST_VARDIR/tmp/file1.tmp +#test should fail +#EOF + +chmod 0777 $MYSQLTEST_VARDIR/tmp/file1.tmp; +--write_file $MYSQLTEST_VARDIR/tmp/file1.tmp +test2 +EOF + +remove_file $MYSQLTEST_VARDIR/tmp/file1.tmp; + +--error 1 +--exec echo "chmod ;" | $MYSQL_TEST 2>&1 + +--error 1 +--exec echo "chmod 0 from_file;" | $MYSQL_TEST 2>&1 + +--error 1 +--exec echo "chmod 08 from_file;" | $MYSQL_TEST 2>&1 + +--error 1 +--exec echo "chmod from_file;" | $MYSQL_TEST 2>&1 + +--error 1 +--exec echo "chmod ABZD from_file;" | $MYSQL_TEST 2>&1 + +--error 1 +--exec echo "chmod 06789 from_file;" | $MYSQL_TEST 2>&1 + + +# ---------------------------------------------------------------------------- # test for perl # ---------------------------------------------------------------------------- --perl diff --git a/mysql-test/t/ndb_index_unique.test b/mysql-test/t/ndb_index_unique.test index 8561b3794c4..18eec046074 100644 --- a/mysql-test/t/ndb_index_unique.test +++ b/mysql-test/t/ndb_index_unique.test @@ -83,9 +83,16 @@ delete from t2 where a = 1; insert into t2 values(8, 2, 3); select * from t2 order by a; +# Bug #24818 CREATE UNIQUE INDEX (...) USING HASH on a NDB table crashes mysqld +create unique index bi using hash on t2(b); +-- error 1062 +insert into t2 values(9, 3, 1); +alter table t2 drop index bi; +insert into t2 values(9, 3, 1); +select * from t2 order by a; + drop table t2; --- error 1121 CREATE TABLE t2 ( a int unsigned NOT NULL PRIMARY KEY, b int unsigned not null, @@ -93,6 +100,20 @@ CREATE TABLE t2 ( UNIQUE USING HASH (b, c) ) engine=ndbcluster; + +insert t2 values(1,1,NULL),(2,2,2),(3,3,NULL),(4,4,4),(5,5,NULL),(6,6,6),(7,7,NULL),(8,3,NULL),(9,3,NULL); + +select * from t2 where c IS NULL order by a; +select * from t2 where b = 3 AND c IS NULL order by a; +select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a; +set @old_ecpd = @@session.engine_condition_pushdown; +set engine_condition_pushdown = true; +explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; +select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a; +set engine_condition_pushdown = @old_ecpd; + +drop table t2; + # # Show use of PRIMARY KEY USING HASH indexes # diff --git a/mysql-test/t/ndb_lock.test b/mysql-test/t/ndb_lock.test index 48a8b77dcd7..8dc02dc20cb 100644 --- a/mysql-test/t/ndb_lock.test +++ b/mysql-test/t/ndb_lock.test @@ -102,16 +102,36 @@ connection con1; commit; # table scan +# +# Note that there are two distinct execution paths in which we unlock +# non-matching rows inspected during table scan - one that is used in +# case of filesort and one that used in rest of cases. Below we cover +# the latter (Bug #20390 "SELECT FOR UPDATE does not release locks of +# untouched rows in full table scans"). connection con1; begin; -select * from t1 where y = 'one' or y = 'three' order by x for update; +# We can't use "order by x" here as it will cause filesort +--replace_column 1 # 2 # 3 # +select * from t1 where y = 'one' or y = 'three' for update; connection con2; begin; # Have to check with pk access here since scans take locks on # all rows and then release them in chunks -# Bug #20390 SELECT FOR UPDATE does not release locks of untouched rows in full table scans -#select * from t1 where x = 2 for update; +select * from t1 where x = 2 for update; +--error 1205 +select * from t1 where x = 1 for update; +rollback; + +connection con1; +commit; + +# And now the test for case with filesort +begin; +select * from t1 where y = 'one' or y = 'three' order by x for update; +connection con2; +begin; +select * from t1 where x = 2 for update; --error 1205 select * from t1 where x = 1 for update; rollback; @@ -157,15 +177,32 @@ commit; # table scan connection con1; begin; -select * from t1 where y = 'one' or y = 'three' order by x lock in share mode; +# We can't use "order by x" here as it will cause filesort +--replace_column 1 # 2 # 3 # +select * from t1 where y = 'one' or y = 'three' lock in share mode; connection con2; begin; select * from t1 where y = 'one' lock in share mode; # Have to check with pk access here since scans take locks on # all rows and then release them in chunks -# Bug #20390 SELECT FOR UPDATE does not release locks of untouched rows in full table scans -#select * from t1 where x = 2 for update; +select * from t1 where x = 2 for update; +--error 1205 +select * from t1 where x = 1 for update; +rollback; + +connection con1; +commit; + +# And the same test for case with filesort +connection con1; +begin; +select * from t1 where y = 'one' or y = 'three' order by x lock in share mode; + +connection con2; +begin; +select * from t1 where y = 'one' lock in share mode; +select * from t1 where x = 2 for update; --error 1205 select * from t1 where x = 1 for update; rollback; diff --git a/mysql-test/t/ndb_update.test b/mysql-test/t/ndb_update.test index 45e3add4639..ebcc6995d74 100644 --- a/mysql-test/t/ndb_update.test +++ b/mysql-test/t/ndb_update.test @@ -23,8 +23,13 @@ UPDATE t1 set b = c; select * from t1 order by pk1; UPDATE t1 set pk1 = 4 where pk1 = 1; select * from t1 order by pk1; +--error 1062 +UPDATE t1 set pk1 = 4 where pk1 = 2; +UPDATE IGNORE t1 set pk1 = 4 where pk1 = 2; +select * from t1 order by pk1; -- error 1062 UPDATE t1 set pk1 = 1, c = 2 where pk1 = 4; +UPDATE IGNORE t1 set pk1 = 1, c = 2 where pk1 = 4; select * from t1 order by pk1; UPDATE t1 set pk1 = pk1 + 10; select * from t1 order by pk1; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index d7cf0e2a375..012b38ff8b7 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -44,7 +44,7 @@ INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif'); INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif'); INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif'); INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif'); -INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif'); +INSERT INTO t2 VALUES (3,'Créer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif'); INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif'); INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif'); INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif'); @@ -588,6 +588,21 @@ SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1; SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a; DROP TABLE t1; +# +# Bug #25427: crash when order by expression contains a name +# that cannot be resolved unambiguously +# + +CREATE TABLE t1 (a int); + +SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1; +--error 1052 +SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val; +--error 1052 +SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1; + +DROP TABLE t1; + # End of 4.1 tests create table t1 (a int not null, b int not null, c int not null); insert t1 values (1,1,1),(1,1,2),(1,2,1); diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 1c8d7b419a3..5f00aa60b11 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1106,10 +1106,94 @@ EXECUTE stmt USING @a; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work +# from stored procedure. +# +# The cause of a bug was that cached LEX::create_list was modified, +# and then together with LEX::key_list was reset. +# +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings ---echo End of 4.1 tests. +CREATE TABLE t1 (i INT); + +PREPARE st_19182 +FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1"; + +EXECUTE st_19182; +DESC t2; + +DROP TABLE t2; + +# Check that on second execution we don't loose 'j' column and the keys +# on 'i' and 'j' columns. +EXECUTE st_19182; +DESC t2; + +DEALLOCATE PREPARE st_19182; +DROP TABLE t2, t1; +# +# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server" +# +# Code which implemented CREATE/ALTER TABLE and CREATE DATABASE +# statement modified HA_CREATE_INFO structure in LEX, making these +# statements PS/SP-unsafe (their re-execution might have resulted +# in incorrect results). +# +--disable_warnings +drop database if exists mysqltest; +drop table if exists t1, t2; +--enable_warnings +# CREATE TABLE and CREATE TABLE ... SELECT +create database mysqltest character set utf8; +prepare stmt1 from "create table mysqltest.t1 (c char(10))"; +prepare stmt2 from "create table mysqltest.t2 select 'test'"; +execute stmt1; +execute stmt2; +show create table mysqltest.t1; +show create table mysqltest.t2; +drop table mysqltest.t1; +drop table mysqltest.t2; +alter database mysqltest character set latin1; +execute stmt1; +execute stmt2; +show create table mysqltest.t1; +show create table mysqltest.t2; +drop database mysqltest; +deallocate prepare stmt1; +deallocate prepare stmt2; +# +# CREATE TABLE with DATA DIRECTORY option +# +# Protect ourselves from data left in tmp/ by a previos possibly failed +# test +--system rm -f $MYSQLTEST_VARDIR/tmp/t1.* +--disable_query_log +eval prepare stmt from "create table t1 (c char(10)) data directory='$MYSQLTEST_VARDIR/tmp'"; +--enable_query_log +execute stmt; +# +# DATA DIRECTORY option does not always work: if the operating +# system does not support symlinks, have_symlinks option is automatically +# disabled. +# In this case DATA DIRECTORY is silently ignored when +# creating a table, and is not output by SHOW CREATE TABLE. +# +--disable_result_log +show create table t1; +--enable_result_log +drop table t1; +execute stmt; +--disable_result_log +show create table t1; +--enable_result_log +drop table t1; +deallocate prepare stmt; +--echo End of 4.1 tests. ############################# 5.0 tests start ################################ # @@ -1508,6 +1592,29 @@ DROP TABLE t1, t2; # +# BUG#20327: Marking of a wrong field leads to a wrong result on select with +# view, prepared statement and subquery. +# +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1; + +INSERT INTO t1 VALUES (1), (2); + +let $query = SELECT t1.i FROM t1 JOIN v1 ON t1.i = v1.i + WHERE EXISTS (SELECT * FROM t1 WHERE v1.i = 1); +eval $query; +eval PREPARE stmt FROM "$query"; +# Statement execution should return '1'. +EXECUTE stmt; +# Check re-execution. +EXECUTE stmt; + +DEALLOCATE PREPARE stmt; +DROP VIEW v1; +DROP TABLE t1; + + +# # BUG#21856: Prepared Statments: crash if bad create # --disable_warnings @@ -1540,4 +1647,98 @@ execute sq; deallocate prepare no_index; deallocate prepare sq; +# +# Bug 25027: query with a single-row non-correlated subquery +# and IS NULL predicate +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (NULL); + +SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL; +PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL'; + +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2 limit ?) IS NULL'; +SET @arg=1; +EXECUTE stmt USING @arg; +DEALLOCATE PREPARE stmt; + +DROP TABLE t1,t2; +# +# Bug#4968 "Stored procedure crash if cursor opened on altered table" +# The bug is not repeatable any more after the fix for +# Bug#15217 "Bug #15217 Using a SP cursor on a table created with PREPARE +# fails with weird error", however ALTER TABLE is not re-execution friendly +# and that caused a valgrind warning. Check that the warning is gone. +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (s1 char(20)); +prepare stmt from "alter table t1 modify s1 int"; +execute stmt; +execute stmt; +drop table t1; +deallocate prepare stmt; + +# +# Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing" +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int, b int); +prepare s_6895 from "alter table t1 drop column b"; +execute s_6895; +show columns from t1; +drop table t1; +create table t1 (a int, b int); +execute s_6895; +show columns from t1; +drop table t1; +create table t1 (a int, b int); +execute s_6895; +show columns from t1; +deallocate prepare s_6895; +drop table t1; + +# +# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server" +# +# 5.0 part of the test. +# + +# ALTER TABLE +create table t1 (i int primary key auto_increment) comment='comment for table t1'; +create table t2 (i int, j int, k int); +prepare stmt from "alter table t1 auto_increment=100"; +execute stmt; +show create table t1; +# Let us trash table-cache's memory +flush tables; +select * from t2; +execute stmt; +show create table t1; +deallocate prepare stmt; +drop table t1, t2; +# 5.1 part of the test. +# CREATE DATABASE +#set @old_character_set_server= @@character_set_server; +#set @@character_set_server= latin1; +#prepare stmt from "create database mysqltest"; +#execute stmt; +#show create database mysqltest; +#drop database mysqltest; +#set @@character_set_server= utf8; +#execute stmt; +#show create database mysqltest; +#drop database mysqltest; +#deallocate prepare stmt; +#set @@character_set_server= @old_character_set_server; + --echo End of 5.0 tests. diff --git a/mysql-test/t/query_cache_notembedded.test b/mysql-test/t/query_cache_notembedded.test index 97be9f9f7ca..a4f4144d9c6 100644 --- a/mysql-test/t/query_cache_notembedded.test +++ b/mysql-test/t/query_cache_notembedded.test @@ -81,12 +81,12 @@ drop table t1, t2, t3, t11, t21; # # do not use QC if tables locked (BUG#12385) # -connect (root,localhost,root,,test,$MASTER_MYPORT,master.sock); +connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY AUTO_INCREMENT ) ENGINE = MyISAM; LOCK TABLE t1 READ LOCAL; -connect (root2,localhost,root,,test,$MASTER_MYPORT,master.sock); +connect (root2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root2; INSERT INTO t1 VALUES (), (), (); connection root; diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test index 175a5bba6fa..8e14b310f4c 100644 --- a/mysql-test/t/read_only.test +++ b/mysql-test/t/read_only.test @@ -101,6 +101,18 @@ drop table t1; --error 1290 insert into t1 values(1); +# +# BUG #22077 "DROP TEMPORARY TABLE fails with wrong error if read_only is set" +# +# check if DROP TEMPORARY on a non-existing temporary table returns the right +# error + +--error ER_BAD_TABLE_ERROR +drop temporary table ttt; + +# check if DROP TEMPORARY TABLE IF EXISTS produces a warning with read_only set +drop temporary table if exists ttt; + connection default; drop table t1,t2; drop user test@localhost; diff --git a/mysql-test/t/rpl000015.test b/mysql-test/t/rpl000015.test index df4bf6f977b..d05e4df66b1 100644 --- a/mysql-test/t/rpl000015.test +++ b/mysql-test/t/rpl000015.test @@ -1,5 +1,5 @@ connect (master,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); -connect (slave,localhost,root,,test,$SLAVE_MYPORT,slave.sock); +connect (slave,localhost,root,,test,$SLAVE_MYPORT,$SLAVE_MYSOCK); connection master; reset master; show master status; @@ -12,7 +12,7 @@ show slave status; change master to master_host='127.0.0.1'; # The following needs to be cleaned up when change master is fixed ---replace_result $MYSQL_TCP_PORT MASTER_PORT +--replace_result $DEFAULT_MASTER_PORT DEFAULT_MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # show slave status; --replace_result $MASTER_MYPORT MASTER_PORT diff --git a/mysql-test/t/rpl000017.test b/mysql-test/t/rpl000017.test index 866b7fd1c25..c1d36d53501 100644 --- a/mysql-test/t/rpl000017.test +++ b/mysql-test/t/rpl000017.test @@ -1,5 +1,5 @@ connect (master,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); -connect (slave,localhost,root,,test,$SLAVE_MYPORT,slave.sock); +connect (slave,localhost,root,,test,$SLAVE_MYPORT,$SLAVE_MYSOCK); connection master; reset master; grant replication slave on *.* to replicate@localhost identified by 'aaaaaaaaaaaaaaab'; diff --git a/mysql-test/t/rpl_deadlock.test b/mysql-test/t/rpl_deadlock.test index 6c5f942cec9..db2a7ec3a8d 100644 --- a/mysql-test/t/rpl_deadlock.test +++ b/mysql-test/t/rpl_deadlock.test @@ -94,7 +94,7 @@ show slave status; # Now we repeat 2), but with BEGIN in the same relay log as # COMMIT (to see if seeking into hot log is ok). - +set @my_max_relay_log_size= @@global.max_relay_log_size; set global max_relay_log_size=0; # This is really copy-paste of 2) of above @@ -119,5 +119,6 @@ show slave status; connection master; drop table t1,t2,t3,t4; sync_slave_with_master; +set global max_relay_log_size= @my_max_relay_log_size; # End of 4.1 tests diff --git a/mysql-test/t/rpl_drop_db.test b/mysql-test/t/rpl_drop_db.test index 3ac0d593fee..f14c9bc0164 100644 --- a/mysql-test/t/rpl_drop_db.test +++ b/mysql-test/t/rpl_drop_db.test @@ -53,5 +53,10 @@ sync_slave_with_master; #cleanup connection slave; stop slave; -system rm -rf $MYSQLTEST_VARDIR/master-data/mysqltest1; +drop database mysqltest1; + +connection master; +# Remove the "extra" file created above +--remove_file $MYSQLTEST_VARDIR/master-data/mysqltest1/f1.txt +drop database mysqltest1; diff --git a/mysql-test/t/rpl_ignore_revoke.test b/mysql-test/t/rpl_ignore_revoke.test index cdeb40df069..00171605a92 100644 --- a/mysql-test/t/rpl_ignore_revoke.test +++ b/mysql-test/t/rpl_ignore_revoke.test @@ -45,3 +45,7 @@ revoke select on *.* FROM 'user_foo'; connection master; delete from mysql.user where user="user_foo"; sync_slave_with_master; + +# Since changes to mysql.* are ignored, the revoke need to +# be done on slave as well +delete from mysql.user where user="user_foo"; diff --git a/mysql-test/t/rpl_init_slave.test b/mysql-test/t/rpl_init_slave.test index cefb04a7b75..139b4902e12 100644 --- a/mysql-test/t/rpl_init_slave.test +++ b/mysql-test/t/rpl_init_slave.test @@ -17,6 +17,8 @@ show variables like 'max_connections'; save_master_pos; connection slave; sync_with_master; +# Save variable value +set @my_global_init_connect= @@global.init_connect; set global init_connect="set @c=1"; show variables like 'init_connect'; connection master; @@ -25,4 +27,8 @@ connection slave; sync_with_master; stop slave; +# Restore changed global variable +set global init_connect= @my_global_init_connect; +set global max_connections= default; + # End of 4.1 tests diff --git a/mysql-test/t/rpl_max_relay_size.test b/mysql-test/t/rpl_max_relay_size.test index be1fbf172fc..e5278ff5727 100644 --- a/mysql-test/t/rpl_max_relay_size.test +++ b/mysql-test/t/rpl_max_relay_size.test @@ -32,6 +32,7 @@ reset slave; --echo # Test 1 --echo # +set @my_max_binlog_size= @@global.max_binlog_size; set global max_binlog_size=8192; set global max_relay_log_size=8192-1; # mapped to 4096 select @@global.max_relay_log_size; @@ -127,6 +128,10 @@ connection master; flush logs; show master status; +# Restore max_binlog_size +connection slave; +set global max_binlog_size= @my_max_binlog_size; + --echo # --echo # End of 4.1 tests --echo # diff --git a/mysql-test/t/rpl_rotate_logs.test b/mysql-test/t/rpl_rotate_logs.test index ee49f92910a..25e54306d6e 100644 --- a/mysql-test/t/rpl_rotate_logs.test +++ b/mysql-test/t/rpl_rotate_logs.test @@ -16,7 +16,7 @@ connect (master,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); --disable_warnings drop table if exists t1, t2, t3, t4; --enable_warnings -connect (slave,localhost,root,,test,$SLAVE_MYPORT,slave.sock); +connect (slave,localhost,root,,test,$SLAVE_MYPORT,$SLAVE_MYSOCK); system cat /dev/null > $MYSQLTEST_VARDIR/slave-data/master.info; system chmod 000 $MYSQLTEST_VARDIR/slave-data/master.info; connection slave; diff --git a/mysql-test/t/rpl_sp.test b/mysql-test/t/rpl_sp.test index 7479794eded..373b895a7c1 100644 --- a/mysql-test/t/rpl_sp.test +++ b/mysql-test/t/rpl_sp.test @@ -519,3 +519,31 @@ DROP FUNCTION f1; connection master; drop table t1; sync_slave_with_master; + +# Restore log_bin_trust_function_creators to original value +set global log_bin_trust_function_creators=0; +connection master; +set global log_bin_trust_function_creators=0; +# +# Bug22043: MySQL don't add "USE <DATABASE>" before "DROP PROCEDURE IF EXISTS" +# +connection master; +reset master; +--disable_warnings +drop database if exists mysqltest; +drop database if exists mysqltest2; +--enable_warnings +create database mysqltest; +create database mysqltest2; +use mysqltest2; +create table t ( t integer ); +create procedure mysqltest.test() begin end; +insert into t values ( 1 ); +show binlog events in 'master-bin.000001' from 98; +--error ER_WRONG_DB_NAME +create procedure `\\`.test() begin end; +# Clean up +drop database mysqltest; +drop database mysqltest2; + + diff --git a/mysql-test/t/rpl_timezone.test b/mysql-test/t/rpl_timezone.test index 0f35c9dc0b6..6ed5b21ace0 100644 --- a/mysql-test/t/rpl_timezone.test +++ b/mysql-test/t/rpl_timezone.test @@ -12,6 +12,9 @@ source include/master-slave.inc; +# Save original timezone +set @my_time_zone= @@global.time_zone; + # Some preparations let $VERSION=`select version()`; set timestamp=100000000; # for fixed output of mysqlbinlog @@ -124,3 +127,7 @@ drop table t1, t2; sync_slave_with_master; # End of 4.1 tests + +# Restore original timezone +connection master; +set global time_zone= @my_time_zone; diff --git a/mysql-test/t/rpl_variables.test b/mysql-test/t/rpl_variables.test index 57ae2b9c3c4..d0801e524e4 100644 --- a/mysql-test/t/rpl_variables.test +++ b/mysql-test/t/rpl_variables.test @@ -1,5 +1,8 @@ source include/master-slave.inc; +# Init for rstore of variable values +set @my_slave_net_timeout =@@global.slave_net_timeout; + set global slave_net_timeout=100; set global sql_slave_skip_counter=100; @@ -12,3 +15,6 @@ show variables like 'slave_load_tmpdir'; # We just set some arbitrary values in variables-master.opt so we can test # that a list of values works correctly show variables like 'slave_skip_errors'; + +# Restore touched values +set global slave_net_timeout =@my_slave_net_timeout; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 0c82cef867f..332389220f6 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3092,3 +3092,23 @@ SELECT t3.a FROM t1,t2,t3 t3.c IN ('bb','ee'); DROP TABLE t1,t2,t3; + +# +# Bug#6298: LIMIT #, -1 no longer works to set start with no end limit +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(a int); +INSERT into t1 values (1), (2), (3); + +# LIMIT N, -1 was accepted by accident in 4.0, but was not intended. +# This test verifies that this illegal construct is now properly detected. + +--error ER_PARSE_ERROR +SELECT * FROM t1 LIMIT 2, -1; + +DROP TABLE t1; + diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index fc6e8714a65..e5d019a215f 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -6587,10 +6587,115 @@ call proc_21462_b(1)| drop procedure proc_21462_a| drop procedure proc_21462_b| + +# +# Bug#19733 "Repeated alter, or repeated create/drop, fails" +# Check that CREATE/DROP INDEX is re-execution friendly. +# +--disable_warnings +drop table if exists t3| +drop procedure if exists proc_bug19733| +--enable_warnings +create table t3 (s1 int)| + +create procedure proc_bug19733() +begin + declare v int default 0; + while v < 100 do + create index i on t3 (s1); + drop index i on t3; + set v = v + 1; + end while; +end| + +call proc_bug19733()| +call proc_bug19733()| +call proc_bug19733()| + +drop procedure proc_bug19733| +drop table t3| + --echo End of 5.0 tests # +# BUG#23760: ROW_COUNT() and store procedure not owrking together +# +--disable_warnings +DROP TABLE IF EXISTS bug23760| +DROP TABLE IF EXISTS bug23760_log| +DROP PROCEDURE IF EXISTS bug23760_update_log| +DROP PROCEDURE IF EXISTS bug23760_test_row_count| +DROP FUNCTION IF EXISTS bug23760_rc_test| +--enable_warnings +CREATE TABLE bug23760 ( + id INT NOT NULL AUTO_INCREMENT , + num INT NOT NULL , + PRIMARY KEY ( id ) +)| + +CREATE TABLE bug23760_log ( + id INT NOT NULL AUTO_INCREMENT , + reason VARCHAR(50)NULL , + ammount INT NOT NULL , + PRIMARY KEY ( id ) +)| + +CREATE PROCEDURE bug23760_update_log(r Varchar(50), a INT) +BEGIN + INSERT INTO bug23760_log (reason, ammount) VALUES(r, a); +END| + +CREATE PROCEDURE bug23760_test_row_count() +BEGIN + UPDATE bug23760 SET num = num + 1; + CALL bug23760_update_log('Test is working', ROW_COUNT()); + UPDATE bug23760 SET num = num - 1; +END| + + +CREATE PROCEDURE bug23760_test_row_count2(level INT) +BEGIN + IF level THEN + UPDATE bug23760 SET num = num + 1; + CALL bug23760_update_log('Test2 is working', ROW_COUNT()); + CALL bug23760_test_row_count2(level - 1); + END IF; +END| + +CREATE FUNCTION bug23760_rc_test(in_var INT) RETURNS INT RETURN in_var| + +INSERT INTO bug23760 (num) VALUES (0), (1), (1), (2), (3), (5), (8)| +SELECT ROW_COUNT()| + +CALL bug23760_test_row_count()| +SELECT * FROM bug23760_log ORDER BY id| + +SET @save_max_sp_recursion= @@max_sp_recursion_depth| +SELECT @save_max_sp_recursion| +SET max_sp_recursion_depth= 5| +SELECT @@max_sp_recursion_depth| +CALL bug23760_test_row_count2(2)| +SELECT ROW_COUNT()| +SELECT * FROM bug23760_log ORDER BY id| +SELECT * FROM bug23760 ORDER by ID| +SET max_sp_recursion_depth= @save_max_sp_recursion| + +SELECT bug23760_rc_test(123)| +INSERT INTO bug23760 (num) VALUES (13), (21), (34), (55)| +SELECT bug23760_rc_test(ROW_COUNT())| + +DROP TABLE bug23760, bug23760_log| +DROP PROCEDURE bug23760_update_log| +DROP PROCEDURE bug23760_test_row_count| +DROP FUNCTION bug23760_rc_test| + +# +# NOTE: The delimiter is `|`, and not `;`. It is changed to `;` +# at the end of the file! +# + +# # BUG#NNNN: New bug synopsis # #--disable_warnings diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 9d5adf57f81..3f7671b18d4 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -1,3 +1,6 @@ +# embedded server causes different stat +-- source include/not_embedded.inc + # PS causes different statistics --disable_ps_protocol diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 0bbbc5a793e..0b658f746a4 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1988,6 +1988,18 @@ SHOW CREATE TABLE t3; DROP TABLE t1,t2,t3; +# +# Bug 24670: subquery witout tables but with a WHERE clause +# + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (2); + +SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0; +SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; +EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL; + +DROP TABLE t1; # End of 4.1 tests # diff --git a/mysql-test/t/system_mysql_db_fix-master.opt b/mysql-test/t/system_mysql_db_fix30020-master.opt index 7e4fa9a3ee8..7e4fa9a3ee8 100644 --- a/mysql-test/t/system_mysql_db_fix-master.opt +++ b/mysql-test/t/system_mysql_db_fix30020-master.opt diff --git a/mysql-test/t/system_mysql_db_fix.test b/mysql-test/t/system_mysql_db_fix30020.test index c50b641b7e2..b71ae6c2204 100644 --- a/mysql-test/t/system_mysql_db_fix.test +++ b/mysql-test/t/system_mysql_db_fix30020.test @@ -6,9 +6,11 @@ # # This is the test for mysql_fix_privilege_tables +# It checks that a system tables from mysql 3.20 +# can be upgraded to current system table format # # Note: If this test fails, don't be confused about the errors reported -# by mysql-test-run; This shows warnings from generated by +# by mysql-test-run This shows warnings generated by # mysql_fix_system_tables which should be ignored. # Instead, concentrate on the errors in r/system_mysql_db.reject @@ -81,7 +83,7 @@ INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y',' INSERT INTO user VALUES ('localhost','', '','N','N','N','N','N','N','N','N','N'); # Call the "shell script" $MYSQL_FIX_SYSTEM_TABLES using system --- system $MYSQL_FIX_SYSTEM_TABLES --database=test > /dev/null +-- system $MYSQL_FIX_SYSTEM_TABLES --database=test > $MYSQLTEST_VARDIR/log/system_mysql_db_fix30020.log 2>&1 -- enable_query_log -- enable_result_log diff --git a/mysql-test/t/system_mysql_db_fix40123-master.opt b/mysql-test/t/system_mysql_db_fix40123-master.opt new file mode 100644 index 00000000000..7e4fa9a3ee8 --- /dev/null +++ b/mysql-test/t/system_mysql_db_fix40123-master.opt @@ -0,0 +1 @@ +--result-file=system_mysql_db diff --git a/mysql-test/t/system_mysql_db_fix40123.test b/mysql-test/t/system_mysql_db_fix40123.test new file mode 100644 index 00000000000..471598625d4 --- /dev/null +++ b/mysql-test/t/system_mysql_db_fix40123.test @@ -0,0 +1,79 @@ +# Embedded server doesn't support external clients +--source include/not_embedded.inc + +# +# This is the test for mysql_fix_privilege_tables +# It checks that a system tables from mysql 4.1.23 +# can be upgraded to current system table format +# +# Note: If this test fails, don't be confused about the errors reported +# by mysql-test-run This shows warnings generated by +# mysql_fix_system_tables which should be ignored. +# Instead, concentrate on the errors in r/system_mysql_db.reject + +--disable_warnings +drop table if exists t1,t1aa,t2aa; +--enable_warnings + +-- disable_result_log +-- disable_query_log + +use test; + +# create system tables as in mysql-4.1.23 +# created by executing "./mysql_create_system_tables real ." + +set table_type=myisam; +CREATE TABLE db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; +INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y'); +INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y'); + +CREATE TABLE host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; + + +CREATE TABLE user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; +INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); + INSERT INTO user VALUES ('localhost','','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0); + +CREATE TABLE func ( name char(64) binary DEFAULT '' NOT NULL, ret tinyint(1) DEFAULT '0' NOT NULL, dl char(128) DEFAULT '' NOT NULL, type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='User defined functions'; + + +CREATE TABLE tables_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Timestamp timestamp(14), Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Table privileges'; +CREATE TABLE columns_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp(14), Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Column privileges'; + +CREATE TABLE help_topic ( help_topic_id int unsigned not null, name varchar(64) not null, help_category_id smallint unsigned not null, description text not null, example text not null, url varchar(128) not null, primary key (help_topic_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help topics'; +CREATE TABLE help_category ( help_category_id smallint unsigned not null, name varchar(64) not null, parent_category_id smallint unsigned null, url varchar(128) not null, primary key (help_category_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help categories'; +CREATE TABLE help_relation ( help_topic_id int unsigned not null references help_topic, help_keyword_id int unsigned not null references help_keyword, primary key (help_keyword_id, help_topic_id) ) engine=MyISAM CHARACTER SET utf8 comment='keyword-topic relation'; +CREATE TABLE help_keyword ( help_keyword_id int unsigned not null, name varchar(64) not null, primary key (help_keyword_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help keywords'; + +CREATE TABLE time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY Name (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; + +CREATE TABLE time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY TzId (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; + +CREATE TABLE time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; + +CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; + +CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; + + +# Run the mysql_fix_privilege_tables.sql using "mysql --force" +--exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES > $MYSQLTEST_VARDIR/log/system_mysql_db_fix40123.log 2>&1 + +-- enable_query_log +-- enable_result_log + +# Dump the tables that should be compared +-- source include/system_db_struct.inc + +-- disable_query_log + +# Drop all tables created by this test +DROP TABLE db, host, user, func, tables_priv, columns_priv, procs_priv, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type; + +-- enable_query_log + +# check that we dropped all system tables +show tables; + +# End of 4.1 tests diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index a9395c12a63..ae01a4b2c3d 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -1140,6 +1140,7 @@ select trigger_schema, trigger_name, event_object_schema, # Trick which makes update of second .TRN file impossible system echo dummy >$MYSQLTEST_VARDIR/master-data/test/t1_ai.TRN~; system chmod 000 $MYSQLTEST_VARDIR/master-data/test/t1_ai.TRN~; +--replace_result $MYSQLTEST_VARDIR . master-data/ '' --error 1 rename table t1 to t2; # 't1' should be still there and triggers should work correctly diff --git a/mysql-test/t/type_binary.test b/mysql-test/t/type_binary.test index 1639aff4711..91eba9b328e 100644 --- a/mysql-test/t/type_binary.test +++ b/mysql-test/t/type_binary.test @@ -91,4 +91,12 @@ insert into t1 values(NULL, 0x412020); drop table t1; set @@sql_mode= @old_sql_mode; +# +# Bug#14171: Wrong default value for a BINARY field +# +create table t1(f1 int, f2 binary(2) not null, f3 char(2) not null); +insert into t1 set f1=1; +select hex(f2), hex(f3) from t1; +drop table t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/type_bit.test b/mysql-test/t/type_bit.test index d46ba667665..48ad24ff6b7 100644 --- a/mysql-test/t/type_bit.test +++ b/mysql-test/t/type_bit.test @@ -261,4 +261,15 @@ insert into t1 (b, a) values ('2', '1'); select hex(a), b from t1; drop table t1; +# +# type was not properly initalized, which caused key_copy to fail +# + +create table t1(bit_field bit(2), int_field int, key a(bit_field)); +insert into t1 values (1,2); +handler t1 open as t1; +handler t1 read a=(1); +handler t1 close; +drop table t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/type_varchar.test b/mysql-test/t/type_varchar.test index 439e98471b2..7b87a388c56 100644 --- a/mysql-test/t/type_varchar.test +++ b/mysql-test/t/type_varchar.test @@ -187,3 +187,13 @@ left join t3 on t1.id=t3.id order by t3.id; --disable_metadata --enable_ps_protocol drop table t1, t2, t3; + +# +# Bug #11927: Warnings shown for CAST( chr as signed) but not (chr + 0) +# +CREATE TABLE t1 (a CHAR(2)); +INSERT INTO t1 VALUES (10), (50), (30), ('1a'), (60), ('t'); +SELECT a,(a + 0) FROM t1 ORDER BY a; +SELECT a,(a DIV 2) FROM t1 ORDER BY a; +SELECT a,CAST(a AS SIGNED) FROM t1 ORDER BY a; +DROP TABLE t1; diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 37358a292be..65cbc7ae3ae 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -187,4 +187,58 @@ DROP FUNCTION lookup; DROP FUNCTION reverse_lookup; DROP FUNCTION avgcost; +# +# Bug #15439: UDF name case handling forces DELETE FROM mysql.func to remove +# the UDF +# +select * from mysql.func; +--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB +eval CREATE FUNCTION is_const RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; + +select IS_const(3); + +drop function IS_const; + +select * from mysql.func; +--error 1305 +select is_const(3); + +# +# Bug#18761: constant expression as UDF parameters not passed in as constant +# +--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB +eval CREATE FUNCTION is_const RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; + +select + is_const(3) as const, + is_const(3.14) as const, + is_const('fnord') as const, + is_const(2+3) as const, + is_const(rand()) as 'nc rand()', + is_const(sin(3.14)) as const, + is_const(upper('test')) as const; + +create table bug18761 (n int); +insert into bug18761 values (null),(2); +select + is_const(3) as const, + is_const(3.14) as const, + is_const('fnord') as const, + is_const(2+3) as const, + is_const(2+n) as 'nc 2+n ', + is_const(sin(n)) as 'nc sin(n)', + is_const(sin(3.14)) as const, + is_const(upper('test')) as const, + is_const(rand()) as 'nc rand()', + is_const(n) as 'nc n ', + is_const(is_const(n)) as 'nc ic?(n)', + is_const(is_const('c')) as const +from + bug18761; +drop table bug18761; + +--error 1241 +select is_const((1,2,3)); + +drop function if exists is_const; diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 65ca1b2c1b7..70f57fdf283 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -210,7 +210,10 @@ create table t1(f1 int, f2 int); insert into t1 values (1,2),(2,3),(3,1); select @var:=f2 from t1 group by f1 order by f2 desc limit 1; select @var; -drop table t1; +create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1; +select * from t2; +select @var; +drop table t1,t2; # # Bug#19024 - SHOW COUNT(*) WARNINGS not return Errors diff --git a/mysql-test/t/varbinary.test b/mysql-test/t/varbinary.test index 5fbd116d7b8..0e45bfb5e1b 100644 --- a/mysql-test/t/varbinary.test +++ b/mysql-test/t/varbinary.test @@ -37,3 +37,46 @@ select x,xx from t1; drop table t1; # End of 4.1 tests + +# +# Bug #19371 VARBINARY() have trailing zeros after upgrade from 4.1 +# + +# Test with a saved table from 4.1 +copy_file std_data/bug19371.frm $MYSQLTEST_VARDIR/master-data/test/t1.frm; +chmod 0777 $MYSQLTEST_VARDIR/master-data/test/t1.frm; +copy_file std_data/bug19371.MYD $MYSQLTEST_VARDIR/master-data/test/t1.MYD; +chmod 0777 $MYSQLTEST_VARDIR/master-data/test/t1.MYD; +copy_file std_data/bug19371.MYI $MYSQLTEST_VARDIR/master-data/test/t1.MYI; +chmod 0777 $MYSQLTEST_VARDIR/master-data/test/t1.MYI; + +# Everything _looks_ fine +show create table t1; + +# But the length of the varbinary columns are too long +select length(a), length(b) from t1; + +# Run CHECK TABLE, it should indicate table need a REPAIR TABLE +CHECK TABLE t1 FOR UPGRADE; + +# Run REPAIR TABLE to alter the table and repair +# the varbinary fields +REPAIR TABLE t1; + +# Now check it's back to normal +show create table t1; +select length(a), length(b) from t1; +insert into t1 values("ccc", "ddd"); +select length(a), length(b) from t1; +select hex(a), hex(b) from t1; +select concat("'", a, "'"), concat("'", b, "'") from t1; + +drop table t1; + +# Check that the fix does not affect table created with current version +create table t1(a varbinary(255)); +insert into t1 values("aaa "); +select length(a) from t1; +alter table t1 modify a varchar(255); +select length(a) from t1; + diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 8473458ae15..a34a1ba117d 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2960,4 +2960,19 @@ DROP VIEW v1, v2; DROP TABLE t1; +# +# BUG#24293: '\Z' token is not handled correctly in views +# + +--disable_warnings +DROP VIEW IF EXISTS v1; +--enable_warnings + +CREATE VIEW v1 AS SELECT 'The\ZEnd'; +SELECT * FROM v1; + +SHOW CREATE VIEW v1; + +DROP VIEW v1; + --echo End of 5.0 tests. diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index ea6dbeb0a4b..0785b74dd47 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -727,13 +727,13 @@ CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tu AS SELECT * FROM mysqltest1.t1; CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tus AS SELECT * FROM mysqltest1.t1; CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_td AS SELECT * FROM mysqltest1.t1; CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tds AS SELECT * FROM mysqltest1.t1; -GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly; -GRANT SELECT ON mysqltest1.v_ts TO readonly; -GRANT INSERT ON mysqltest1.v_ti TO readonly; -GRANT UPDATE ON mysqltest1.v_tu TO readonly; -GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly; -GRANT DELETE ON mysqltest1.v_td TO readonly; -GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly; +GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly@localhost; +GRANT SELECT ON mysqltest1.v_ts TO readonly@localhost; +GRANT INSERT ON mysqltest1.v_ti TO readonly@localhost; +GRANT UPDATE ON mysqltest1.v_tu TO readonly@localhost; +GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly@localhost; +GRANT DELETE ON mysqltest1.v_td TO readonly@localhost; +GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly@localhost; CONNECT (n1,localhost,readonly,,); CONNECTION n1; @@ -791,7 +791,7 @@ DROP VIEW mysqltest1.v_ti; DROP VIEW mysqltest1.v_ts; DROP VIEW mysqltest1.v_t1; DROP TABLE mysqltest1.t1; -DROP USER readonly@localhost; +DROP USER readonly@localhost; DROP DATABASE mysqltest1; # @@ -929,6 +929,44 @@ DROP USER mysqltest_u1@localhost; # +# Bug#17254: Error for DEFINER security on VIEW provides too much info +# +connect (root,localhost,root,,); +connection root; +CREATE DATABASE db17254; +USE db17254; +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (10),(20); +CREATE USER def_17254@localhost; +GRANT SELECT ON db17254.* TO def_17254@localhost; +CREATE USER inv_17254@localhost; +GRANT SELECT ON db17254.t1 TO inv_17254@localhost; +GRANT CREATE VIEW ON db17254.* TO def_17254@localhost; + +connect (def,localhost,def_17254,,db17254); +connection def; +CREATE VIEW v1 AS SELECT * FROM t1; + +connection root; +DROP USER def_17254@localhost; + +connect (inv,localhost,inv_17254,,db17254); +connection inv; +--echo for a user +--error 1142 +SELECT * FROM v1; + +connection root; +--echo for a superuser +--error 1449 +SELECT * FROM v1; +DROP USER inv_17254@localhost; +DROP DATABASE db17254; +disconnect def; +disconnect inv; + + +# # BUG#24404: strange bug with view+permission+prepared statement # --disable_warnings |