From bd414c301c1bbadecf385e1c10aa8a27cd76f61a Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 5 Jun 2003 17:02:00 +0200 Subject: Test for bug 578. And a comment in slave.cc. sql/slave.cc: Comment to remember that size of relay logs on disk is not the total size of the relay log. --- mysql-test/r/lock_tables_lost_commit.result | 8 ++++++++ 1 file changed, 8 insertions(+) create mode 100644 mysql-test/r/lock_tables_lost_commit.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/lock_tables_lost_commit.result b/mysql-test/r/lock_tables_lost_commit.result new file mode 100644 index 00000000000..ccf56793f45 --- /dev/null +++ b/mysql-test/r/lock_tables_lost_commit.result @@ -0,0 +1,8 @@ +drop table if exists t1; +create table t1(a int) type=innodb; +lock tables t1 write; +insert into t1 values(10); +select * from t1; +a +10 +drop table t1; -- cgit v1.2.1 From 0880795793f7c0c468bdb23f65050cc8ffd22d24 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 10 Jun 2003 23:29:49 +0200 Subject: More error messages. This is intended to help debugging; presently I have a support issue with an unclear message which can have N reasons for appearing. This should help us know at which point it failed, and get the errno when my_open was involved (as the reason for the unclear message is often a permission problem). RESET SLAVE resets last_error and last_errno in SHOW SLAVE STATUS (without this, rpl_loaddata.test, which is expected to generate an error in last_error, influenced rpl_log_pos.test). A small test update. Added STOP SLAVE to mysql-test-run to get rid of several stupid error messages which are printed while the master restarts and the slave attempts/manages to connect to it and sends it nonsense binlog requests. mysql-test/mysql-test-run.sh: Before running a test, stop slave threads if they exist (if they don't the script goes on fine). This also works fine with the manager. Before this change, when the master was stopped/restarted (which happened before the slave server was stopped/restarted), the slave threads noticed the stop (so printed an error message in slave.err), then managed to reconnect (to the new master, the one that is running for the _next_ test), and this reconnection had time to produce error messages (because, for example, the binlog the slave thread was asking had been deleted) before the slave server was killed. This change reduces by 10% (40 lines) slave.err in replication tests. mysql-test/r/rpl000018.result: Result update. mysql-test/t/rpl000018.test: This test does "show master logs" so should do "reset master" instead of relying on the previous tests. sql/slave.cc: More error messages. sql/sql_repl.cc: More error messages. RESET SLAVE resets last_error and last_errno in SHOW SLAVE STATUS. --- mysql-test/r/rpl000018.result | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl000018.result b/mysql-test/r/rpl000018.result index ba51406bba0..282c1e492a1 100644 --- a/mysql-test/r/rpl000018.result +++ b/mysql-test/r/rpl000018.result @@ -1,3 +1,4 @@ +reset master; reset slave; slave start; show master logs; -- cgit v1.2.1 From 4885c3ff9d9c8daa93f5c344b6b61ccf9bcbba28 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 11 Jun 2003 22:07:23 +0500 Subject: test case for bug #614 mysql-test/r/func_time.result: result's checking added mysql-test/t/func_time.test: testcase added --- mysql-test/r/func_time.result | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 2941352c776..06c0be86667 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -329,6 +329,9 @@ insert into t1 values ('2001-01-12 12:23:40'); select ctime, hour(ctime) from t1; ctime hour(ctime) 2001-01-12 12:23:40 12 +select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001; +ctime +2001-01-12 12:23:40 drop table t1; create table t1 (id int); create table t2 (id int, date date); -- cgit v1.2.1 From 8fe7e029c211dc3856fd3a8274f5fbbc95f12e3b Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 15 Jun 2003 01:04:28 +0300 Subject: Many files: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/ha_innodb.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/handler.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/sql_lex.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/ha_innodb.cc: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/handler.cc: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/sql_parse.cc: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed sql/sql_yacc.yy: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed include/my_base.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed mysql-test/t/innodb.test: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed mysql-test/r/innodb.result: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/btr/btr0cur.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/btr/btr0pcur.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/buf/buf0buf.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/buf/buf0flu.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/data/data0data.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/data/data0type.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/dict/dict0boot.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/dict/dict0crea.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/dict/dict0dict.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/dict/dict0load.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/dict/dict0mem.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/fil/fil0fil.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/fsp/fsp0fsp.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/ha/ha0ha.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/ibuf/ibuf0ibuf.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/btr0cur.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/buf0buf.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/data0data.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/data0type.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/db0err.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/dict0dict.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/dict0mem.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/fil0fil.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/lock0lock.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/os0file.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/page0page.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/rem0cmp.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/row0row.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/row0sel.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/row0upd.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/srv0srv.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/data0type.ic: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/trx0roll.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/trx0trx.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/trx0types.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/ut0dbg.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/ut0mem.h: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/row0mysql.ic: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/include/trx0sys.ic: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/lock/lock0lock.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/log/log0log.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/log/log0recv.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/mem/mem0pool.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/os/os0file.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/os/os0thread.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/page/page0cur.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/page/page0page.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/pars/pars0opt.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/pars/pars0pars.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/rem/rem0cmp.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0ins.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0mysql.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0row.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0sel.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0umod.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0upd.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/row/row0vers.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/srv/srv0srv.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/srv/srv0start.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/trx/trx0rec.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/trx/trx0roll.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/trx/trx0sys.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/trx/trx0trx.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/ut/ut0mem.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed innobase/ut/ut0ut.c: Merge InnoDB-4.0.14: SAVEPOINT now implemented; InnoDB now accepts also column prefix keys; crashing bug in ON UPDATE CASCADE fixed; page checksum formula fixed --- mysql-test/r/innodb.result | 10 ---------- 1 file changed, 10 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index e2dea324ff2..9d1c232e830 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -787,16 +787,6 @@ id id3 100 2 UNLOCK TABLES; DROP TABLE t1; -create table t1 (a char(20), unique (a(5))) type=innodb; -Incorrect sub part key. The used key part isn't a string, the used length is longer than the key part or the table handler doesn't support unique sub keys -create table t1 (a char(20), index (a(5))) type=innodb; -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` char(20) default NULL, - KEY `a` (`a`) -) TYPE=InnoDB -drop table t1; create temporary table t1 (a int not null auto_increment, primary key(a)) type=innodb; insert into t1 values (NULL),(NULL),(NULL); delete from t1 where a=3; -- cgit v1.2.1 From 292f91e83d954e2bd23c7f15fc26d0823157594f Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 15 Jun 2003 12:01:51 +0200 Subject: Simplified a test. thd->enter_cond() and exit_cond(), so that the I/O thread accepts to stop when it's waiting for relay log space. Reset ignore_log_space_limit to 0 when the SQL thread terminates. mysql-test/r/rpl_relayspace.result: result update mysql-test/t/rpl_relayspace-slave.opt: smaller relay_log_space_limit to speed up things. mysql-test/t/rpl_relayspace.test: Less queries in the test, to make it simpler. Testcase if the IO thread blocked in wait_for_relay_log_space accepts to stop when STOP SLAVE. sql/slave.cc: thd->enter_cond() and exit_cond(), so that the I/O thread accepts to stop when it's waiting on cond (waiting for relay log space). Reset ignore_log_space_limit to 0 when the SQL thread terminates. --- mysql-test/r/rpl_relayspace.result | 10 ++++++++-- 1 file changed, 8 insertions(+), 2 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_relayspace.result b/mysql-test/r/rpl_relayspace.result index 5e552ef7400..721c6a882bd 100644 --- a/mysql-test/r/rpl_relayspace.result +++ b/mysql-test/r/rpl_relayspace.result @@ -6,8 +6,14 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; stop slave; create table t1 (a int); +drop table t1; +create table t1 (a int); +drop table t1; +reset slave; +start slave io_thread; +stop slave io_thread; reset slave; start slave; -select master_pos_wait('master-bin.001',5000,45)=-1; -master_pos_wait('master-bin.001',5000,45)=-1 +select master_pos_wait('master-bin.001',200,6)=-1; +master_pos_wait('master-bin.001',200,6)=-1 0 -- cgit v1.2.1 From 366fd92e8d882737268f354e0a744d576868a043 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 16 Jun 2003 15:49:54 +0200 Subject: Fix for nightly build test failure (test update). More messages. Testcase for bug 651. client/mysqltest.c: More explicit error message if MASTER_POS_WAIT() returns NULL. mysql-test/r/rpl_loaddata.result: result update mysql-test/r/rpl_master_pos_wait.result: result update mysql-test/t/rpl000001.test: sync_with_master (=MASTER_POS_WAIT()) was called when we could expect the SQL slave thread had stopped. As I yesterday changed code so that "SQL thread stops => MASTER_POS_WAIT() returns NULL immediately" (bugfix), sync_with_master received NULL (on build.mysql.com, not on my machine; this is a question of milliseconds, if the slave server will process MASTER_POS_WAIT() before or after the slave SQL thread has stopped), and in mysqltest.c, sync_with_master complained that it could not sync. So I just remove this sync_with_master, which does not make sense anymore: we just wait for the slave SQL thread to stop. mysql-test/t/rpl_loaddata.test: Discovered we had wait_for_slave_to_stop, so used it as it automates things. mysql-test/t/rpl_master_pos_wait.test: Discovered we had 'send' to send a query without waiting for the resultn so could had a testcase for bug 651. Shorter timeouts as there is no risk the position is reached. sql/slave.cc: A longer DBUG_PRINT. --- mysql-test/r/rpl_loaddata.result | 3 --- mysql-test/r/rpl_master_pos_wait.result | 8 ++++++-- 2 files changed, 6 insertions(+), 5 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_loaddata.result b/mysql-test/r/rpl_loaddata.result index 844a9d66cb3..b5154ca95cf 100644 --- a/mysql-test/r/rpl_loaddata.result +++ b/mysql-test/r/rpl_loaddata.result @@ -25,6 +25,3 @@ drop table t3; create table t1(a int, b int, unique(b)); insert into t1 values(1,10); load data infile '../../std_data/rpl_loaddata.dat' into table t1; -show status like 'slave_running'; -Variable_name Value -Slave_running OFF diff --git a/mysql-test/r/rpl_master_pos_wait.result b/mysql-test/r/rpl_master_pos_wait.result index 22c7aef621c..cb6ee31a54d 100644 --- a/mysql-test/r/rpl_master_pos_wait.result +++ b/mysql-test/r/rpl_master_pos_wait.result @@ -4,6 +4,10 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -select master_pos_wait('master-bin.999999',0,10); -master_pos_wait('master-bin.999999',0,10) +select master_pos_wait('master-bin.999999',0,2); +master_pos_wait('master-bin.999999',0,2) -1 + select master_pos_wait('master-bin.999999',0); +stop slave sql_thread; +master_pos_wait('master-bin.999999',0) +NULL -- cgit v1.2.1 From ad5c763e74821465cc8237aa39e2391239a17206 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 16 Jun 2003 23:05:45 +0200 Subject: fulltext and left join bug fixed mysql-test/t/fulltext.test: explain select fulltext test mysql-test/t/fulltext_left_join.test: explain select fulltext test --- mysql-test/r/fulltext.result | 3 +++ mysql-test/r/fulltext_left_join.result | 11 +++++++++++ 2 files changed, 14 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index eaaaf9c8880..6f15b2eb973 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -5,6 +5,9 @@ INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ('Only MyISAM tables','support collections'), ('Function MATCH ... AGAINST()','is used to do a search'), ('Full-text search in MySQL', 'implements vector space model'); +explain select * from t1 where MATCH(a,b) AGAINST ("collections"); +table type possible_keys key key_len ref rows Extra +t1 fulltext a a 0 1 Using where select * from t1 where MATCH(a,b) AGAINST ("collections"); a b Only MyISAM tables support collections diff --git a/mysql-test/r/fulltext_left_join.result b/mysql-test/r/fulltext_left_join.result index abc63358dbe..6875a517718 100644 --- a/mysql-test/r/fulltext_left_join.result +++ b/mysql-test/r/fulltext_left_join.result @@ -31,3 +31,14 @@ match(t1.texte,t1.sujet,t1.motsclefs) against('droit' IN BOOLEAN MODE) 1 0 drop table t1, t2; +create table t1 (venue_id int(11) default null, venue_text varchar(255) default null, dt datetime default null) type=myisam; +insert into t1 (venue_id, venue_text, dt) values (1, 'a1', '2003-05-23 19:30:00'),(null, 'a2', '2003-05-23 19:30:00'); +create table t2 (name varchar(255) not null default '', entity_id int(11) not null auto_increment, primary key (entity_id), fulltext key name (name)) type=myisam; +insert into t2 (name, entity_id) values ('aberdeen town hall', 1), ('glasgow royal concert hall', 2), ('queen\'s hall, edinburgh', 3); +select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen' in boolean mode) and dt = '2003-05-23 19:30:00'; +venue_id venue_text dt name entity_id +1 a1 2003-05-23 19:30:00 aberdeen town hall 1 +select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen') and dt = '2003-05-23 19:30:00'; +venue_id venue_text dt name entity_id +1 a1 2003-05-23 19:30:00 aberdeen town hall 1 +drop table t1,t2; -- cgit v1.2.1 From b518744d16a516c842c4ec0382c8ab44a38aebca Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 17 Jun 2003 23:19:38 +0200 Subject: Changed change_master() to use ER_MASTER_INFO (better display). mysql-test/r/rpl_rotate_logs.result: result update mysql-test/t/rpl_rotate_logs.test: comments and test update with the error code sql/slave.cc: A DBUG_PRINT sql/sql_repl.cc: Use ER_MASTER_INFO instead of custom message and zero error code (which display badly). --- mysql-test/r/rpl_rotate_logs.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_rotate_logs.result b/mysql-test/r/rpl_rotate_logs.result index 741c53fe52b..f49006e8e05 100644 --- a/mysql-test/r/rpl_rotate_logs.result +++ b/mysql-test/r/rpl_rotate_logs.result @@ -5,7 +5,7 @@ Could not initialize master info structure, check permisions on master.info slave start; Could not initialize master info structure, check permisions on master.info change master to master_host='127.0.0.1',master_port=MASTER_PORT, master_user='root'; -Could not initialize master info +Could not initialize master info structure, check permisions on master.info reset slave; change master to master_host='127.0.0.1',master_port=MASTER_PORT, master_user='root'; reset master; -- cgit v1.2.1 From 85d2282e257ba47555fc05ef62dc70ece5b7a69e Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 19 Jun 2003 14:02:19 +0500 Subject: SCRUM Task ID 894: Backport default_week_format variable to 4.0 mysql-test/r/func_time.result: Backport default_week_format variable to 4.0 mysql-test/t/func_time.test: Backport default_week_format variable to 4.0 sql/mysqld.cc: Backport default_week_format variable to 4.0 sql/set_var.cc: Backport default_week_format variable to 4.0 sql/sql_class.h: Backport default_week_format variable to 4.0 sql/sql_yacc.yy: Backport default_week_format variable to 4.0 --- mysql-test/r/func_time.result | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 06c0be86667..58a05a2336f 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -96,6 +96,11 @@ week(19981231,2) week(19981231,3) week(20000101,2) week(20000101,3) select week(20001231,2),week(20001231,3); week(20001231,2) week(20001231,3) 1 52 +set default_week_format = 2; +select week(20001231),week(20001231,2),week(20001231,0); +week(20001231) week(20001231,2) week(20001231,0) +1 1 53 +set default_week_format = 0; select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v'); date_format('1998-12-31','%x-%v') date_format('1999-01-01','%x-%v') 1998-53 1998-53 -- cgit v1.2.1 From 5c9b87ce08a1651c6a26509cd2e65a23a20d2cc7 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 21 Jun 2003 18:10:59 +0200 Subject: Always send a fake Rotate event (this is roughly 70 bytes) when starting binlog_dump. This way 3.23.58 slaves will always detect a 4.0.14 master (and stop) immediately. BUG#198. mysql-test/r/rpl_log.result: result update (the relay log now contains a fake Rotate_log_event). mysql-test/r/rpl_log_pos.result: result update (the relay log now contains a fake Rotate_log_event). --- mysql-test/r/rpl_log.result | 2 +- mysql-test/r/rpl_log_pos.result | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_log.result b/mysql-test/r/rpl_log.result index 425c376af1e..316bf1f3dd0 100644 --- a/mysql-test/r/rpl_log.result +++ b/mysql-test/r/rpl_log.result @@ -93,6 +93,6 @@ slave-bin.002 62 Query 1 168 use test; insert into t1 values (1) slave-bin.002 122 Query 1 228 use test; drop table t1 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.002 276 slave-relay-bin.002 1522 master-bin.002 Yes Yes 0 0 276 1526 +127.0.0.1 root MASTER_PORT 1 master-bin.002 276 slave-relay-bin.002 1563 master-bin.002 Yes Yes 0 0 276 1567 show binlog events in 'slave-bin.005' from 4; Error when executing command SHOW BINLOG EVENTS: Could not find target log diff --git a/mysql-test/r/rpl_log_pos.result b/mysql-test/r/rpl_log_pos.result index e76b565813f..3224e84fa31 100644 --- a/mysql-test/r/rpl_log_pos.result +++ b/mysql-test/r/rpl_log_pos.result @@ -21,7 +21,7 @@ Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Lo slave start; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.001 73 slave-relay-bin.001 4 master-bin.001 No Yes 0 0 73 4 +127.0.0.1 root MASTER_PORT 1 master-bin.001 73 slave-relay-bin.001 45 master-bin.001 No Yes 0 0 73 45 slave stop; change master to master_log_pos=173; slave start; -- cgit v1.2.1 From 35c2b9df7564c95c38f04fc4dbc3bebea81a9a41 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 23 Jun 2003 20:03:59 +0300 Subject: SHOW GRANTS hided real grants when grants on both column and table (Bug 654) mysql-test/r/grant.result: Test of grant BUG mysql-test/t/grant.test: Test of grant BUG sql/sql_acl.cc: SHOW GRANTS hided real grants when grants on both column and table (Bug 654) Code cleanup (Bigger than intended because of editor problem) sql/sql_acl.h: Fixed grant bug --- mysql-test/r/grant.result | 47 ++++++++++++++++++++++++++++++++++++++++++----- 1 file changed, 42 insertions(+), 5 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index c1dcd5c29e9..76080c85511 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1,3 +1,4 @@ +drop table if exists t1; delete from mysql.user where user='mysqltest_1'; delete from mysql.db where user='mysqltest_1'; flush privileges; @@ -64,8 +65,44 @@ GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TE revoke all privileges on mysqltest.* from mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; flush privileges; -grant usage on test.* to user@localhost with grant option; -show grants for user@localhost; -Grants for user@localhost -GRANT USAGE ON *.* TO 'user'@'localhost' -GRANT USAGE ON `test`.* TO 'user'@'localhost' WITH GRANT OPTION +grant usage on test.* to mysqltest_1@localhost with grant option; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT USAGE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +GRANT USAGE ON `test`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +delete from mysql.tables_priv where user='mysqltest_1'; +delete from mysql.columns_priv where user='mysqltest_1'; +flush privileges; +create table t1 (a int); +GRANT select,update,insert on t1 to mysqltest_1@localhost; +GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT SELECT, SELECT (a), INSERT, INSERT (a), UPDATE, UPDATE (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost' +select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; +table_priv column_priv +Select,Insert,Update Select,Insert,Update,References +REVOKE select (a), update on t1 from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT SELECT, INSERT, INSERT (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost' +REVOKE insert,insert (a) on t1 from mysqltest_1@localhost; +GRANT references on t1 to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT SELECT, REFERENCES, REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost' +select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; +table_priv column_priv +Select,References References +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +delete from mysql.tables_priv where user='mysqltest_1'; +delete from mysql.columns_priv where user='mysqltest_1'; +flush privileges; +drop table t1; -- cgit v1.2.1 From 68524c81feac77d7d0e8dca716738b5b59d98e3f Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 23 Jun 2003 20:05:54 +0300 Subject: Simple code cleanup mysql-test/r/innodb.result: Fixed test case after innodb optimize->analyze fix mysys/mf_pack.c: Added comments sql/log_event.cc: Fixed usage of fn_format() sql/slave.cc: Indentation fixes and comments cleanup sql/sql_repl.cc: Comment cleanup --- mysql-test/r/innodb.result | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 9d1c232e830..465d4faf0bc 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -156,7 +156,7 @@ level id parent_id 1 1007 101 optimize table t1; Table Op Msg_type Msg_text -test.t1 optimize error The handler for the table doesn't support optimize +test.t1 optimize status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 id A 87 NULL NULL BTREE @@ -180,7 +180,7 @@ create table t1 (a int) type=innodb; insert into t1 values (1), (2); optimize table t1; Table Op Msg_type Msg_text -test.t1 optimize error The handler for the table doesn't support optimize +test.t1 optimize status OK delete from t1 where a = 1; select * from t1; a @@ -712,7 +712,7 @@ world 2 hello 1 optimize table t1; Table Op Msg_type Msg_text -test.t1 optimize error The handler for the table doesn't support optimize +test.t1 optimize status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 a A 2 NULL NULL BTREE @@ -1240,7 +1240,7 @@ t1 range c c 5 NULL 1 Using where update t1 set c=a; explain select * from t1 where c between 1 and 10000; table type possible_keys key key_len ref rows Extra -t1 ALL c NULL NULL NULL 29537 Using where +t1 ALL c NULL NULL NULL 27682 Using where drop table t1,t2; create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) type=innodb; insert into t1 (id) values (null),(null),(null),(null),(null); -- cgit v1.2.1 From 3148d6f2a55211d02f63e4dc3a45f1d19ba536cd Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 24 Jun 2003 10:53:21 +0500 Subject: Another bugfix for #615 Looks a bit nicer than previous one mysql-test/r/insert.result: Test results added mysql-test/t/insert.test: testcase for the bug added sql/field.h: Field::set_default fixed sql/field_conv.cc: Now we can just replace field->reset() with field->set_default() here --- mysql-test/r/insert.result | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 69b790ff35b..d019cfcbfa9 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -58,6 +58,16 @@ skr 2 test 1 test 2 drop table t1; +create table t1 (id int NOT NULL DEFAULT 8); +insert into t1 values(NULL); +Column 'id' cannot be null +insert into t1 values (1), (NULL), (2); +select * from t1; +id +1 +8 +2 +drop table t1; drop database if exists foo; create database foo; use foo; -- cgit v1.2.1 From 8bed69fa9ec200925d9a9ec36419d2c5eecf9739 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 24 Jun 2003 17:07:43 +0500 Subject: Test case for bug #666 mysql-test/r/func_str.result: test for bug #666 result mysql-test/t/func_str.test: test for bug #666 --- mysql-test/r/func_str.result | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 1a4cb9217e4..09818c14462 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -234,3 +234,16 @@ INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); 1 DROP TABLE t1; +CREATE TABLE t1 ( +wid int(10) unsigned NOT NULL auto_increment, +data_podp date default NULL, +status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy', +PRIMARY KEY(wid), +); +INSERT INTO t1 VALUES (8,NULL,'real'); +INSERT INTO t1 VALUES (9,NULL,'nowy'); +SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid; +elt(status_wnio,data_podp) +NULL +NULL +DROP TABLE t1; -- cgit v1.2.1 From 8abad215650386dedf0001812f44859009de74ef Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 25 Jun 2003 17:15:33 +0500 Subject: Testcase for #674 added mysql-test/r/sel000100.result: Result of the testcase added mysql-test/t/sel000100.test: checking for bug #674 added --- mysql-test/r/sel000100.result | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/sel000100.result b/mysql-test/r/sel000100.result index f9234815a2b..3ffa4004b84 100644 --- a/mysql-test/r/sel000100.result +++ b/mysql-test/r/sel000100.result @@ -26,3 +26,13 @@ ORDER BY link; key_link_id link NULL NULL drop table t1,t2; +CREATE TABLE t1 ( +html varchar(5) default NULL, +rin int(11) default '0', +out int(11) default '0' +) TYPE=MyISAM; +INSERT INTO t1 VALUES ('1',1,0); +SELECT DISTINCT html,SUM(out)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin; +html prod +1 0.00 +drop table t1; -- cgit v1.2.1 From 50159020c8f0f5b1f37ca29ccd81f086b7d32982 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 25 Jun 2003 21:11:23 -0400 Subject: fixed bug with type of user variables (bug #551) mysql-test/r/user_var.result: added tests for user variables comparing (bug #551) mysql-test/t/user_var.test: added tests for user variables comparing (bug #551) BitKeeper/etc/logging_ok: Logging to logging@openlogging.org accepted --- mysql-test/r/user_var.result | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index e06a113bd29..8abdb40399a 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -14,3 +14,11 @@ table type possible_keys key key_len ref rows Extra t1 index NULL i 4 NULL 3 where used; Using index table type possible_keys key key_len ref rows Extra t1 ref i i 4 const 1 where used +@a:=10 @b:=1 @a > @b @a < @b +10 1 1 0 +@a:="10" @b:="1" @a > @b @a < @b +10 1 1 0 +@a:=10 @b:=2 @a > @b @a < @b +10 2 1 0 +@a:="10" @b:="2" @a > @b @a < @b +10 2 0 1 -- cgit v1.2.1 From a3beaaa3af7af26aabadda6836618d32d6c90e51 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 26 Jun 2003 05:38:19 +0300 Subject: LEFT JOIN optimization: Change LEFT JOIN to normal join if possible mysql-test/r/select.result: Added test for LEFT JOIN optimization mysql-test/t/select.test: Added test for LEFT JOIN optimization sql/item.h: LEFT JOIN optimization sql/item_cmpfunc.cc: LEFT JOIN optimization sql/item_cmpfunc.h: LEFT JOIN optimization sql/item_func.cc: LEFT JOIN optimization sql/item_func.h: LEFT JOIN optimization sql/item_strfunc.cc: LEFT JOIN optimization sql/sql_base.cc: Heart of LEFT JOIN optimization --- mysql-test/r/select.result | 34 ++++++++++++++++++++++++++++++++-- 1 file changed, 32 insertions(+), 2 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index a4c2533ec1a..811c396ea67 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2569,16 +2569,46 @@ fld1 fld1 250503 250505 250504 250505 250505 250505 +insert into t2 (fld1, companynr) values (999999,99); select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; companynr companyname +99 NULL +select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; +count(*) +1199 explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; table type possible_keys key key_len ref rows Extra -t2 ALL NULL NULL NULL NULL 1199 +t2 ALL NULL NULL NULL NULL 1200 t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; table type possible_keys key key_len ref rows Extra t4 ALL NULL NULL NULL NULL 12 -t2 ALL NULL NULL NULL NULL 1199 Using where; Not exists +t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists +delete from t2 where fld1=999999; +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; +table type possible_keys key key_len ref rows Extra +t2 ALL NULL NULL NULL NULL 1199 Using where +t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; +table type possible_keys key key_len ref rows Extra +t2 ALL NULL NULL NULL NULL 1199 Using where +t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; +table type possible_keys key key_len ref rows Extra +t2 ALL NULL NULL NULL NULL 1199 Using where +t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; +table type possible_keys key key_len ref rows Extra +t4 ALL NULL NULL NULL NULL 12 +t2 ALL NULL NULL NULL NULL 1199 Using where +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; +table type possible_keys key key_len ref rows Extra +t4 ALL PRIMARY NULL NULL NULL 12 +t2 ALL NULL NULL NULL NULL 1199 Using where +explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; +table type possible_keys key key_len ref rows Extra +t4 ALL NULL NULL NULL NULL 12 +t2 ALL NULL NULL NULL NULL 1199 Using where select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 -- cgit v1.2.1 From ec2c923d7d4259709be35dd606cf1b76088e7bcb Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 26 Jun 2003 21:30:53 +0200 Subject: bug with ranges and not_null_col=can_be_null_expr fixed mysql-test/r/range.result: new test added long lines reformatted mysql-test/t/range.test: new test added long lines reformatted --- mysql-test/r/range.result | 81 +++++++++++++++++++++++++++++------------------ 1 file changed, 50 insertions(+), 31 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index a1d258455dc..26d9d7869c6 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -5,7 +5,24 @@ type int(11) DEFAULT '0' NOT NULL, event_id int(11) DEFAULT '0' NOT NULL, PRIMARY KEY (event_date,type,event_id) ); -INSERT INTO t1 VALUES ('1999-07-10',100100,24),('1999-07-11',100100,25),('1999-07-13',100600,0),('1999-07-13',100600,4),('1999-07-13',100600,26),('1999-07-14',100600,10),('1999-07-15',100600,16),('1999-07-15',100800,45),('1999-07-15',101000,47),('1999-07-16',100800,46),('1999-07-20',100600,5),('1999-07-20',100600,27),('1999-07-21',100600,11),('1999-07-22',100600,17),('1999-07-23',100100,39),('1999-07-24',100100,39),('1999-07-24',100500,40),('1999-07-25',100100,39),('1999-07-27',100600,1),('1999-07-27',100600,6),('1999-07-27',100600,28),('1999-07-28',100600,12),('1999-07-29',100500,41),('1999-07-29',100600,18),('1999-07-30',100500,41),('1999-07-31',100500,41),('1999-08-01',100700,34),('1999-08-03',100600,7),('1999-08-03',100600,29),('1999-08-04',100600,13),('1999-08-05',100500,42),('1999-08-05',100600,19),('1999-08-06',100500,42),('1999-08-07',100500,42),('1999-08-08',100500,42),('1999-08-10',100600,2),('1999-08-10',100600,9),('1999-08-10',100600,30),('1999-08-11',100600,14),('1999-08-12',100600,20),('1999-08-17',100500,8),('1999-08-17',100600,31),('1999-08-18',100600,15),('1999-08-19',100600,22),('1999-08-24',100600,3),('1999-08-24',100600,32),('1999-08-27',100500,43),('1999-08-31',100600,33),('1999-09-17',100100,37),('1999-09-18',100100,37),('1999-09-19',100100,37),('2000-12-18',100700,38); +INSERT INTO t1 VALUES ('1999-07-10',100100,24), ('1999-07-11',100100,25), +('1999-07-13',100600,0), ('1999-07-13',100600,4), ('1999-07-13',100600,26), +('1999-07-14',100600,10), ('1999-07-15',100600,16), ('1999-07-15',100800,45), +('1999-07-15',101000,47), ('1999-07-16',100800,46), ('1999-07-20',100600,5), +('1999-07-20',100600,27), ('1999-07-21',100600,11), ('1999-07-22',100600,17), +('1999-07-23',100100,39), ('1999-07-24',100100,39), ('1999-07-24',100500,40), +('1999-07-25',100100,39), ('1999-07-27',100600,1), ('1999-07-27',100600,6), +('1999-07-27',100600,28), ('1999-07-28',100600,12), ('1999-07-29',100500,41), +('1999-07-29',100600,18), ('1999-07-30',100500,41), ('1999-07-31',100500,41), +('1999-08-01',100700,34), ('1999-08-03',100600,7), ('1999-08-03',100600,29), +('1999-08-04',100600,13), ('1999-08-05',100500,42), ('1999-08-05',100600,19), +('1999-08-06',100500,42), ('1999-08-07',100500,42), ('1999-08-08',100500,42), +('1999-08-10',100600,2), ('1999-08-10',100600,9), ('1999-08-10',100600,30), +('1999-08-11',100600,14), ('1999-08-12',100600,20), ('1999-08-17',100500,8), +('1999-08-17',100600,31), ('1999-08-18',100600,15), ('1999-08-19',100600,22), +('1999-08-24',100600,3), ('1999-08-24',100600,32), ('1999-08-27',100500,43), +('1999-08-31',100600,33), ('1999-09-17',100100,37), ('1999-09-18',100100,37), +('1999-09-19',100100,37), ('2000-12-18',100700,38); select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date; event_date type event_id 1999-07-10 100100 24 @@ -35,35 +52,21 @@ CLOSED tinyint(4) DEFAULT '0' NOT NULL, ISS_DATE date DEFAULT '0000-00-00' NOT NULL, PRIMARY KEY (PAPER_ID,YEAR,ISSUE) ); -INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'); -INSERT INTO t1 VALUES (1,1999,111,0,'1999-03-23'); -INSERT INTO t1 VALUES (1,1999,222,0,'1999-03-23'); -INSERT INTO t1 VALUES (3,1999,33,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,32,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,31,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,30,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,29,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,28,0,'1999-07-12'); -INSERT INTO t1 VALUES (1,1999,40,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,41,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,42,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,46,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,47,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,48,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,49,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,50,0,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,51,0,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,200,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,52,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,53,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,54,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,55,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,56,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,57,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,58,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,59,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,60,0,'1999-07-01'); -INSERT INTO t1 VALUES (3,1999,35,0,'1999-07-12'); +INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'), (1,1999,111,0,'1999-03-23'), +(1,1999,222,0,'1999-03-23'), (3,1999,33,0,'1999-07-12'), +(3,1999,32,0,'1999-07-12'), (3,1999,31,0,'1999-07-12'), +(3,1999,30,0,'1999-07-12'), (3,1999,29,0,'1999-07-12'), +(3,1999,28,0,'1999-07-12'), (1,1999,40,1,'1999-05-01'), +(1,1999,41,1,'1999-05-01'), (1,1999,42,1,'1999-05-01'), +(1,1999,46,1,'1999-05-01'), (1,1999,47,1,'1999-05-01'), +(1,1999,48,1,'1999-05-01'), (1,1999,49,1,'1999-05-01'), +(1,1999,50,0,'1999-05-01'), (1,1999,51,0,'1999-05-01'), +(1,1999,200,0,'1999-06-28'), (1,1999,52,0,'1999-06-28'), +(1,1999,53,0,'1999-06-28'), (1,1999,54,0,'1999-06-28'), +(1,1999,55,0,'1999-06-28'), (1,1999,56,0,'1999-07-01'), +(1,1999,57,0,'1999-07-01'), (1,1999,58,0,'1999-07-01'), +(1,1999,59,0,'1999-07-01'), (1,1999,60,0,'1999-07-01'), +(3,1999,35,0,'1999-07-12'); select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28)) order by YEAR,ISSUE; YEAR ISSUE 1999 29 @@ -88,7 +91,12 @@ PRIMARY KEY (id), KEY parent_id (parent_id), KEY level (level) ); -INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2); +INSERT INTO t1 VALUES (1,0,0), (3,1,1), (4,1,1), (8,2,2), (9,2,2), (17,3,2), +(22,4,2), (24,4,2), (28,5,2), (29,5,2), (30,5,2), (31,6,2), (32,6,2), (33,6,2), +(203,7,2), (202,7,2), (20,3,2), (157,0,0), (193,5,2), (40,7,2), (2,1,1), +(15,2,2), (6,1,1), (34,6,2), (35,6,2), (16,3,2), (7,1,1), (36,7,2), (18,3,2), +(26,5,2), (27,5,2), (183,4,2), (38,7,2), (25,5,2), (37,7,2), (21,4,2), +(19,3,2), (5,1,1), (179,5,2); SELECT * FROM t1 WHERE level = 1 AND parent_id = 1; id parent_id level 3 1 1 @@ -207,3 +215,14 @@ select count(*) from t1 where art = 'J'; count(*) 213 drop table t1; +create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2)); +insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"), +(3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"), +(6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"), +(9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"), +(12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"), +(15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"), +(18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa"); +select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1; +id1 idnull +drop table t1; -- cgit v1.2.1 From c2cb9b97be721d07e9cb2a27076c8beaf22f304b Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 27 Jun 2003 03:04:54 +0300 Subject: Added thread variable max_seeks_for_key Change optimizer to prefer key lookups before table scan Change table scans to be done after tables with constrains on scanned table mysql-test/r/distinct.result: Update results mysql-test/r/group_by.result: Update results mysql-test/r/heap.result: Update results mysql-test/r/join.result: Update results mysql-test/r/key_diff.result: Update results mysql-test/r/myisam.result: Update results mysql-test/r/order_by.result: Update results mysql-test/r/select_safe.result: Update results mysql-test/t/distinct.test: Change test to be repeatable mysql-test/t/join.test: Change test to be repeatable mysql-test/t/select_safe.test: Add tests for MAX_SEEKS_FOR_KEY sql/handler.h: Optimize structure for 64 bit machines (and to avoid problems with gdb) sql/item_cmpfunc.cc: Initialize not_null_tables_cache properly sql/mysqld.cc: Added max_seeks_for_key sql/set_var.cc: Added max_seeks_for_key sql/sql_class.h: Added max_seeks_for_key sql/sql_select.cc: Added max_seeks_for_key Change optimizer to prefer key lookups before table scan. Change table scans to be done after tables with constrains on scanned table --- mysql-test/r/distinct.result | 12 ++++++------ mysql-test/r/group_by.result | 4 ++-- mysql-test/r/heap.result | 2 +- mysql-test/r/join.result | 4 +--- mysql-test/r/key_diff.result | 2 +- mysql-test/r/myisam.result | 10 +++++----- mysql-test/r/order_by.result | 6 +++--- mysql-test/r/select_safe.result | 23 +++++++++++++++++++---- 8 files changed, 38 insertions(+), 25 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 5f4f7cced1e..53a20eeea0b 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -161,7 +161,7 @@ SELECT DISTINCT UserId FROM t1 WHERE UserId=22; UserId drop table t1; CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned); -INSERT INTO t1 VALUES (1,1),(2,1); +INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1); CREATE TABLE t2 (a int(10) unsigned not null, key (A)); INSERT INTO t2 VALUES (1),(2); CREATE TABLE t3 (a int(10) unsigned, key(A), b text); @@ -189,7 +189,7 @@ insert into t4 select * from t3; insert into t3 select * from t4; explain select distinct t1.a from t1,t3 where t1.a=t3.a; table type possible_keys key key_len ref rows Extra -t1 index PRIMARY PRIMARY 4 NULL 2 Using index; Using temporary +t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary t3 ref a a 5 t1.a 10 Using where; Using index; Distinct select distinct t1.a from t1,t3 where t1.a=t3.a; a @@ -200,16 +200,16 @@ select distinct 1 from t1,t3 where t1.a=t3.a; 1 explain SELECT distinct t1.a from t1; table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 2 Using index +t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT distinct t1.a from t1 order by a desc; table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 2 Using index +t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT t1.a from t1 group by a order by a desc; table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 2 Using index +t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT distinct t1.a from t1 order by a desc limit 1; table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 2 Using index +t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT distinct a from t3 order by a desc limit 2; table type possible_keys key key_len ref rows Extra t3 index NULL a 5 NULL 204 Using index diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 94e1ce59585..67e6bec09f6 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -534,11 +534,11 @@ a b explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort -t2 ALL a NULL NULL NULL 4 Using where +t2 ALL a NULL NULL NULL 3 Using where explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 6 Using temporary -t2 ALL a NULL NULL NULL 4 Using where +t2 ALL a NULL NULL NULL 3 Using where drop table t1,t2; create table t1 (a int, b int); insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4); diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result index d8905085e34..a04ddf3f302 100644 --- a/mysql-test/r/heap.result +++ b/mysql-test/r/heap.result @@ -166,7 +166,7 @@ alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop update t1 set new_col=btn; explain select * from t1 where btn="a"; table type possible_keys key key_len ref rows Extra -t1 ALL btn NULL NULL NULL 14 Using where +t1 ALL btn NULL NULL NULL 11 Using where explain select * from t1 where btn="a" and new_col="a"; table type possible_keys key key_len ref rows Extra t1 ref btn btn 11 const,const 10 Using where diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index e063b5c3e02..039b6e1cba3 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -104,9 +104,7 @@ KEY category (category,county,state) INSERT INTO t2 VALUES (3,2,11,12,5400,7800); INSERT INTO t2 VALUES (4,2,25,12,6500,11200); INSERT INTO t2 VALUES (5,1,37,6,10000,12000); -select a.id, b.category as catid, b.state as stateid, b.county as -countyid from t1 a, t2 b where (a.token = -'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id); +select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id); id catid stateid countyid 27 2 12 11 28 2 12 11 diff --git a/mysql-test/r/key_diff.result b/mysql-test/r/key_diff.result index 4eaccc696f9..2d4bc19474f 100644 --- a/mysql-test/r/key_diff.result +++ b/mysql-test/r/key_diff.result @@ -36,7 +36,7 @@ a a a a explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; table type possible_keys key key_len ref rows Extra t1 ALL a NULL NULL NULL 5 -t2 ALL b NULL NULL NULL 5 Using where +t2 ALL b NULL NULL NULL 4 Using where select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a; a b a b A B a a diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 9a653aff99e..c4368384bf8 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -335,12 +335,12 @@ t1 1 c_2 1 c A 5 NULL NULL YES BTREE t1 1 c_2 2 a A 5 NULL NULL BTREE explain select * from t1,t2 where t1.a=t2.a; table type possible_keys key key_len ref rows Extra -t1 ALL a NULL NULL NULL 5 -t2 ALL a NULL NULL NULL 2 Using where +t2 ALL a NULL NULL NULL 2 +t1 ALL a NULL NULL NULL 4 Using where explain select * from t1,t2 force index(a) where t1.a=t2.a; table type possible_keys key key_len ref rows Extra t2 ALL a NULL NULL NULL 2 -t1 ALL a NULL NULL NULL 5 Using where +t1 ALL a NULL NULL NULL 4 Using where explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; table type possible_keys key key_len ref rows Extra t2 ALL a NULL NULL NULL 2 @@ -351,8 +351,8 @@ t2 ALL b NULL NULL NULL 2 t1 ref b b 5 t2.b 1 Using where explain select * from t1,t2 force index(c) where t1.a=t2.a; table type possible_keys key key_len ref rows Extra -t1 ALL a NULL NULL NULL 5 -t2 ALL NULL NULL NULL NULL 2 Using where +t2 ALL NULL NULL NULL NULL 2 +t1 ALL a NULL NULL NULL 4 Using where explain select * from t1 where a=0 or a=2; table type possible_keys key key_len ref rows Extra t1 ALL a NULL NULL NULL 5 Using where diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index ff2dd93311c..64fac8af872 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -307,17 +307,17 @@ table type possible_keys key key_len ref rows Extra t1 range a a 9 NULL 8 Using where; Using index explain select * from t1 where a = 2 and b >0 order by a desc,b desc; table type possible_keys key key_len ref rows Extra -t1 range a a 9 NULL 5 Using where; Using index +t1 range a a 9 NULL 4 Using where; Using index explain select * from t1 where a = 2 and b is null order by a desc,b desc; table type possible_keys key key_len ref rows Extra t1 ref a a 9 const,const 1 Using where; Using index; Using filesort explain select * from t1 where a = 2 and (b is null or b > 0) order by a desc,b desc; table type possible_keys key key_len ref rows Extra -t1 range a a 9 NULL 6 Using where; Using index +t1 range a a 9 NULL 5 Using where; Using index explain select * from t1 where a = 2 and b > 0 order by a desc,b desc; table type possible_keys key key_len ref rows Extra -t1 range a a 9 NULL 5 Using where; Using index +t1 range a a 9 NULL 4 Using where; Using index explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; table type possible_keys key key_len ref rows Extra t1 range a a 9 NULL 2 Using where; Using index diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index 3303f19d9c7..830390ebe3c 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -1,6 +1,6 @@ drop table if exists t1; SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9; -create table t1 (a int auto_increment primary key, b char(20)); +create table t1 (a int auto_increment primary key, b char(20), key(b)); insert into t1 values(1,"test"); SELECT SQL_BUFFER_RESULT * from t1; a b @@ -13,7 +13,7 @@ a b 1 test 2 test2 update t1 set b="a" where a=1; -select 1 from t1,t1 as t2,t1 as t3,t1 as t4; +select 1 from t1,t1 as t2,t1 as t3; 1 1 1 @@ -22,11 +22,9 @@ select 1 from t1,t1 as t2,t1 as t3,t1 as t4; update t1 set b="a"; You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column update t1 set b="a" where b="test"; -You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column delete from t1; You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column delete from t1 where b="test"; -You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column delete from t1 where a+0=1; You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5; @@ -59,5 +57,22 @@ a b 2 test2 4 a 5 a +SELECT @@MAX_SEEKS_FOR_KEY; +@@max_seeks_for_key +4294967295 +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); +explain select * from t1,t1 as t2 where t1.b=t2.b; +table type possible_keys key key_len ref rows Extra +t1 ALL b NULL NULL NULL 21 +t2 ALL b NULL NULL NULL 16 Using where +set MAX_SEEKS_FOR_KEY=1; +explain select * from t1,t1 as t2 where t1.b=t2.b; +table type possible_keys key key_len ref rows Extra +t1 ALL b NULL NULL NULL 21 +t2 ref b b 21 t1.b 6 Using where +SET MAX_SEEKS_FOR_KEY=DEFAULT; drop table t1; SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT; -- cgit v1.2.1 From 5768f4a34ed7218c61ed34cf5b76eee66b715224 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 27 Jun 2003 06:40:24 +0300 Subject: Fixed testcase after last changes --- mysql-test/r/select_safe.result | 13 ++++++++----- 1 file changed, 8 insertions(+), 5 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index 830390ebe3c..ca5c03bdb50 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -1,6 +1,6 @@ drop table if exists t1; SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9; -create table t1 (a int auto_increment primary key, b char(20), key(b)); +create table t1 (a int auto_increment primary key, b char(20)); insert into t1 values(1,"test"); SELECT SQL_BUFFER_RESULT * from t1; a b @@ -22,9 +22,11 @@ select 1 from t1,t1 as t2,t1 as t3; update t1 set b="a"; You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column update t1 set b="a" where b="test"; +You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column delete from t1; You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column delete from t1 where b="test"; +You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column delete from t1 where a+0=1; You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5; @@ -33,18 +35,19 @@ update t1 set b="a" limit 1; update t1 set b="a" where b="b" limit 2; delete from t1 where b="test" limit 1; delete from t1 where a+0=1 limit 2; +alter table t1 add key b (b); SET MAX_JOIN_SIZE=2; SELECT @@MAX_JOIN_SIZE, @@SQL_BIG_SELECTS; @@max_join_size @@sql_big_selects 2 0 insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); -SELECT * from t1; +SELECT * from t1 order by a; The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok SET SQL_BIG_SELECTS=1; -SELECT * from t1; +SELECT * from t1 order by a; a b -3 a 2 test2 +3 a 4 a 5 a SET MAX_JOIN_SIZE=2; @@ -53,8 +56,8 @@ The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET MAX_JOIN_SIZE=DEFAULT; SELECT * from t1; a b -3 a 2 test2 +3 a 4 a 5 a SELECT @@MAX_SEEKS_FOR_KEY; -- cgit v1.2.1 From e224c1238d58c99b10ff6f55f816bee09be146c8 Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 29 Jun 2003 12:58:43 +0300 Subject: SET CHARACTER SET DEFAULT didn't work (Bug #462) BUILD/compile-pentium-debug-max: Removed -DBIG_TABLES because of problems with gdb 5.3 sql/set_var.cc: SET CHARACTER SET DEFAULT didn't work sql/set_var.h: SET CHARACTER SET DEFAULT didn't work --- mysql-test/r/convert.result | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 mysql-test/r/convert.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/convert.result b/mysql-test/r/convert.result new file mode 100644 index 00000000000..f8dad8c69ba --- /dev/null +++ b/mysql-test/r/convert.result @@ -0,0 +1,17 @@ +select @@convert_character_set; +@@convert_character_set + +select @@global.convert_character_set; +@@global.convert_character_set + +show variables like "%convert_character_set%"; +Variable_name Value +convert_character_set +SET CHARACTER SET cp1251_koi8; +select @@convert_character_set; +@@convert_character_set +cp1251_koi8 +SET CHARACTER SET DEFAULT; +select @@convert_character_set; +@@convert_character_set + -- cgit v1.2.1 From caddb5ea53cbde18bf0a915bc4867fecd93c55d5 Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 29 Jun 2003 21:15:51 +0300 Subject: Fixed 'Unknown error' when doing ORDER BY on reference table which was used with NULL value on NOT NULL column. (Bug #479) mysql-test/r/order_by.result: Update of results mysql-test/t/order_by.test: Test of new code sql/sql_select.cc: Fixed problem with lookup on NULL --- mysql-test/r/order_by.result | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 1a292b1203c..d45ae6b6dec 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -142,3 +142,7 @@ t3 eq_ref PRIMARY PRIMARY 2 t1.gid 1 where used table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort t3 eq_ref PRIMARY PRIMARY 2 t1.skr 1 where used +t2_id str +112633 t2 112633 +112634 t2 112634 +t2_id str -- cgit v1.2.1 From bbf8ebe87d7c3d3d07f56457168e8faa943f7d64 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 1 Jul 2003 13:29:55 +0300 Subject: Code/testcase cleanups mysql-test/r/insert_select.result: Fixed results mysql-test/r/join_outer.result: New test case for inner join table dependency mysql-test/t/insert_select.test: Remove not used drop tables. Fixed table names mysql-test/t/join_outer.test: New test case for inner join table dependency sql/slave.cc: Simple optimizations --- mysql-test/r/insert_select.result | 19 ++++++++----------- mysql-test/r/join_outer.result | 8 ++++++++ 2 files changed, 16 insertions(+), 11 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index b49ca3a6c2f..c403ff8532d 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -21,8 +21,7 @@ payoutID 20 22 drop table t1,t2; -DROP TABLE IF EXISTS crash1,crash2; -CREATE TABLE `crash1` ( +CREATE TABLE `t1` ( `numeropost` bigint(20) unsigned NOT NULL default '0', `icone` tinyint(4) unsigned NOT NULL default '0', `numreponse` bigint(20) unsigned NOT NULL auto_increment, @@ -37,7 +36,7 @@ KEY `date` (`date`), KEY `pseudo` (`pseudo`), KEY `numreponse` (`numreponse`) ) TYPE=MyISAM; -CREATE TABLE `crash2` ( +CREATE TABLE `t2` ( `numeropost` bigint(20) unsigned NOT NULL default '0', `icone` tinyint(4) unsigned NOT NULL default '0', `numreponse` bigint(20) unsigned NOT NULL auto_increment, @@ -52,22 +51,20 @@ KEY `date` (`date`), KEY `pseudo` (`pseudo`), KEY `numreponse` (`numreponse`) ) TYPE=MyISAM; -INSERT INTO crash2 +INSERT INTO t2 (numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES (9,1,56,'test','joce','2001-07-25 13:50:53' ,3649052399,0); -INSERT INTO crash1 (numeropost,icone,contenu,pseudo,date,signature,ip) -SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM crash2 +INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip) +SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM t2 WHERE numeropost=9 ORDER BY numreponse ASC; show variables like '%bulk%'; Variable_name Value bulk_insert_buffer_size 8388608 -INSERT INTO crash1 (numeropost,icone,contenu,pseudo,date,signature,ip) -SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM crash2 +INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip) +SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2 WHERE numeropost=9 ORDER BY numreponse ASC; -DROP TABLE IF EXISTS crash1,crash2; -drop table if exists t1; -drop table if exists t2; +DROP TABLE t1,t2; create table t1(a int, unique(a)); insert into t1 values(2); create table t2(a int); diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 81266f6562e..8f3f82201c3 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -659,3 +659,11 @@ i i i 1 NULL NULL 2 2 2 drop table t1,t2,t3; +create table t1 (f1 integer,f2 integer,f3 integer); +create table t2 (f2 integer,f4 integer); +create table t3 (f3 integer,f5 integer); +select * from t1 +left outer join t2 using (f2) +left outer join t3 using (f3); +Unknown column 'test.t2.f3' in 'on clause' +drop table t1,t2,t3; -- cgit v1.2.1 From 23a9a5805cd258c5699e782cebdb5763acb1d85d Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 1 Jul 2003 14:38:38 +0300 Subject: Optimized old patches Don't set field to DEFAULT value when set to NULL mysql-test/r/insert.result: Updated results after patch sql/field_conv.cc: Revert patch: Don't set field to DEFAULT value when set to NULL sql/item_strfunc.cc: Optimized patch for null handling with elt sql/opt_range.cc: Safety fix for range with null patch --- mysql-test/r/insert.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index d019cfcbfa9..ebcb7721822 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -65,7 +65,7 @@ insert into t1 values (1), (NULL), (2); select * from t1; id 1 -8 +0 2 drop table t1; drop database if exists foo; -- cgit v1.2.1 From 46bcc4753acc259a574ecb411de859eb1797bca1 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 1 Jul 2003 15:38:11 +0300 Subject: Updated results after merge --- mysql-test/r/user_var.result | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 0d4f7b401e6..67e55acb29b 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -30,11 +30,15 @@ explain select * from t1 where i=@vv1; table type possible_keys key key_len ref rows Extra t1 ref i i 4 const 1 Using where drop table t1,t2; +select @a:=10, @b:=1, @a > @b, @a < @b; @a:=10 @b:=1 @a > @b @a < @b 10 1 1 0 +select @a:="10", @b:="1", @a > @b, @a < @b; @a:="10" @b:="1" @a > @b @a < @b 10 1 1 0 +select @a:=10, @b:=2, @a > @b, @a < @b; @a:=10 @b:=2 @a > @b @a < @b 10 2 1 0 +select @a:="10", @b:="2", @a > @b, @a < @b; @a:="10" @b:="2" @a > @b @a < @b 10 2 0 1 -- cgit v1.2.1 From 12d879c912543fffa77cecfda799bc20ddd35df5 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 2 Jul 2003 00:10:47 +0300 Subject: Lot's of clean-ups and fixes for 4.0.14. include/my_global.h: CONFIG_SMP mysql-test/r/grant.result: Test cases for bug fixes mysql-test/r/insert_select.result: Test cases for bug fixes mysql-test/r/union.result: Test cases for bug fixes mysql-test/t/grant.test: Test cases for bug fixes mysql-test/t/insert_select.test: Test cases for bug fixes mysql-test/t/union.test: Test cases for bug fixes sql/sql_acl.cc: Fix for granting global privileges on db level sql/sql_parse.cc: Fix for INSERT... SELECT sql/sql_select.cc: removing a fix sql/sql_union.cc: P --- mysql-test/r/grant.result | 2 + mysql-test/r/insert_select.result | 490 ++++++++++++++++++++++++++++++++++++++ mysql-test/r/union.result | 18 ++ 3 files changed, 510 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 76080c85511..fccd3715af7 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -106,3 +106,5 @@ delete from mysql.tables_priv where user='mysqltest_1'; delete from mysql.columns_priv where user='mysqltest_1'; flush privileges; drop table t1; +GRANT FILE on mysqltest.* to mysqltest_1@localhost; +Wrong usage of DB GRANT and GLOBAL PRIVILEGES diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index b49ca3a6c2f..5f5a3fb1172 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -80,3 +80,493 @@ Log_name Pos Event_type Server_id Orig_log_pos Info master-bin.001 4 Start 1 4 Server ver: VERSION, Binlog ver: 3 master-bin.001 79 Query 1 79 use test; insert into t1 select * from t2 drop table t1, t2; +drop table if exists t1, t2; +create table t1 (a int not null); +create table t2 (a int not null); +insert into t1 values (1); +insert into t1 values (a+2); +insert into t1 values (a+3); +insert into t1 values (4),(a+5); +insert into t1 select * from t1; +select * from t1; +a +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +insert into t1 select * from t1 as t2; +select * from t1; +a +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +insert into t2 select * from t1 as t2; +select * from t1; +a +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +insert into t1 select t2.a from t1,t2; +select * from t1; +a +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +2 +3 +4 +5 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +1 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +2 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +3 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +4 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +5 +drop table t1,t2; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 970e83c6752..929c79b542f 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -96,6 +96,13 @@ a b select found_rows(); found_rows() 6 +select sql_calc_found_rows a,b from t1 union all select a,b from t2 limit 2; +a b +1 a +2 b +select found_rows(); +found_rows() +6 explain select a,b from t1 union all select a,b from t2; table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 4 @@ -259,3 +266,14 @@ uid rl g1 cid gg uid rl g1 cid gg 1 NULL V1 NULL 1 drop table t1,t2,t3,t4,t5,t6; +CREATE TABLE t1 (a int not null, b char (10) not null); +insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); +CREATE TABLE t2 (a int not null, b char (10) not null); +insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); +create table t3 select a,b from t1 union select a,b from t2; +create table t4 (select a,b from t1) union (select a,b from t2) limit 2; +insert into t4 select a,b from t1 union select a,b from t2; +insert into t3 (select a,b from t1) union (select a,b from t2) limit 2; +select * from t3; +select * from t4; +drop table t1,t2,t3,t4; -- cgit v1.2.1 From 417c571bb345475bd1794264b18484f874910b85 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 2 Jul 2003 01:24:05 +0300 Subject: Some fixes for CREATE / INSERT FROM UNIO >.. --- mysql-test/r/union.result | 30 ++++++++++++++++++++++++++++-- 1 file changed, 28 insertions(+), 2 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 929c79b542f..2072b045563 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -271,9 +271,35 @@ insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); CREATE TABLE t2 (a int not null, b char (10) not null); insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); create table t3 select a,b from t1 union select a,b from t2; -create table t4 (select a,b from t1) union (select a,b from t2) limit 2; +create table t4 (select a,b from t1) union (select a,b from t2); insert into t4 select a,b from t1 union select a,b from t2; -insert into t3 (select a,b from t1) union (select a,b from t2) limit 2; +insert into t3 (select a,b from t1) union (select a,b from t2); select * from t3; +a b +1 a +2 b +3 c +4 d +5 f +6 e +1 a +2 b +3 c +4 d +5 f +6 e select * from t4; +a b +1 a +2 b +3 c +4 d +5 f +6 e +1 a +2 b +3 c +4 d +5 f +6 e drop table t1,t2,t3,t4; -- cgit v1.2.1 From 7ad2b84907b4bc5d40551f85c8cdf6d3af1fc29a Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 2 Jul 2003 13:15:45 +0300 Subject: innodb.test, innodb.result: Remove nondeterministic EXPLAIN SELECT which gives on 64-bit platforms a slightly different estimate (sometimes we should study why we get different results on 64-bit and 32-bit) mysql-test/r/innodb.result: Remove nondeterministic EXPLAIN SELECT which gives on 64-bit platforms a slightly different estimate (sometimes we should study why we get different results on 64-bit and 32-bit) mysql-test/t/innodb.test: Remove nondeterministic EXPLAIN SELECT which gives on 64-bit platforms a slightly different estimate (sometimes we should study why we get different results on 64-bit and 32-bit) --- mysql-test/r/innodb.result | 3 --- 1 file changed, 3 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 465d4faf0bc..bb237e2e406 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1238,9 +1238,6 @@ explain select * from t1 where c between 1 and 10000; table type possible_keys key key_len ref rows Extra t1 range c c 5 NULL 1 Using where update t1 set c=a; -explain select * from t1 where c between 1 and 10000; -table type possible_keys key key_len ref rows Extra -t1 ALL c NULL NULL NULL 27682 Using where drop table t1,t2; create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) type=innodb; insert into t1 (id) values (null),(null),(null),(null),(null); -- cgit v1.2.1 From 66237f45fb325bbfd3e9b21eaca924920fdc2007 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 2 Jul 2003 14:57:40 +0300 Subject: Fix for a problem with : CREATE / INSERT ... (SELECT ...) UNION (SELECT ...) LIMIT; --- mysql-test/r/union.result | 12 ++---------- 1 file changed, 2 insertions(+), 10 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 2072b045563..51d9f2d17ca 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -271,9 +271,9 @@ insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); CREATE TABLE t2 (a int not null, b char (10) not null); insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); create table t3 select a,b from t1 union select a,b from t2; -create table t4 (select a,b from t1) union (select a,b from t2); +create table t4 (select a,b from t1) union (select a,b from t2) limit 2; insert into t4 select a,b from t1 union select a,b from t2; -insert into t3 (select a,b from t1) union (select a,b from t2); +insert into t3 (select a,b from t1) union (select a,b from t2) limit 2; select * from t3; a b 1 a @@ -284,18 +284,10 @@ a b 6 e 1 a 2 b -3 c -4 d -5 f -6 e select * from t4; a b 1 a 2 b -3 c -4 d -5 f -6 e 1 a 2 b 3 c -- cgit v1.2.1 From 7bb4166a9aa53755b49de865cc639afa9098de7c Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 2 Jul 2003 17:12:50 +0200 Subject: bug #504: in "datetime op int" comparison int wasn't converted to datetime --- mysql-test/r/type_datetime.result | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 4785f790069..5df4f355cfb 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -52,10 +52,17 @@ a 0000-00-00 00:00:00 drop table t1; create table t1 (id int, dt datetime); -insert into t1 values (1,"2001-08-14 00:00:00"),(2,"2001-08-15 00:00:00"),(3,"2001-08-16 00:00:00"); +insert into t1 values (1,"2001-08-14 00:00:00"),(2,"2001-08-15 00:00:00"),(3,"2001-08-16 00:00:00"),(4,"2003-09-15 01:20:30"); select * from t1 where dt='2001-08-14 00:00:00' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15'); id dt 1 2001-08-14 00:00:00 +create index dt on t1 (dt); +select * from t1 where dt > 20021020; +id dt +4 2003-09-15 01:20:30 +select * from t1 ignore index (dt) where dt > 20021020; +id dt +4 2003-09-15 01:20:30 drop table t1; CREATE TABLE `t1` ( `date` datetime NOT NULL default '0000-00-00 00:00:00', -- cgit v1.2.1 From 0b37eae10748dc4399c8bf0abbb0eadc37333744 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 02:08:33 +0300 Subject: innodb.result, innodb.test: Remove all nondeterministic tests from innodb.test; but I have to study why on 64 bits we get different estimates mysql-test/t/innodb.test: Remove all nondeterministic tests from innodb.test; but I have to study why on 64 bits we get different estimates mysql-test/r/innodb.result: Remove all nondeterministic tests from innodb.test; but I have to study why on 64 bits we get different estimates --- mysql-test/r/innodb.result | 51 ---------------------------------------------- 1 file changed, 51 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index bb237e2e406..6ab55bbf924 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -157,11 +157,6 @@ level id parent_id optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK -show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 PRIMARY 1 id A 87 NULL NULL BTREE -t1 1 parent_id 1 parent_id A 43 NULL NULL BTREE -t1 1 level 1 level A 6 NULL NULL BTREE drop table t1; CREATE TABLE t1 ( gesuchnr int(11) DEFAULT '0' NOT NULL, @@ -200,9 +195,6 @@ insert into t1 values (3,""), (4,"testing"); analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK -show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 skr 1 a A 3 NULL NULL YES BTREE drop table t1; create table t1 (a int,b varchar(20),key(a)) type=innodb; insert into t1 values (1,""), (2,"testing"); @@ -343,14 +335,6 @@ user_id name phone ref_email detail drop table t1; CREATE TABLE t1 (a int not null, b int not null,c int not null, key(a),primary key(a,b), unique(c),key(a),unique(b)); -show index from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 PRIMARY 1 a A NULL NULL NULL BTREE -t1 0 PRIMARY 2 b A 0 NULL NULL BTREE -t1 0 c 1 c A 0 NULL NULL BTREE -t1 0 b 1 b A 0 NULL NULL BTREE -t1 1 a 1 a A NULL NULL NULL BTREE -t1 1 a_2 1 a A NULL NULL NULL BTREE drop table t1; create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)); alter table t1 type=innodb; @@ -713,9 +697,6 @@ hello 1 optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK -show keys from t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 PRIMARY 1 a A 2 NULL NULL BTREE drop table t1; create table t1 (i int, j int ) TYPE=innodb; insert into t1 values (1,2); @@ -745,9 +726,6 @@ a DROP TABLE t1; create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) type = innodb; insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); -explain select * from t1 where a > 0 and a < 50; -table type possible_keys key key_len ref rows Extra -t1 range PRIMARY PRIMARY 4 NULL 1 Using where drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) type=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); @@ -835,8 +813,6 @@ create table mysqltest.t3 (a int not null) type= heap; insert into mysqltest.t3 values(1); commit; drop database mysqltest; -show tables from mysqltest; -Got one of the listed errors set autocommit=0; create table t1 (a int not null) type= innodb; insert into t1 values(1),(2); @@ -869,30 +845,6 @@ a drop table t1; create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) type=innodb; insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); -explain select * from t1 order by a; -table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 4 -explain select * from t1 order by b; -table type possible_keys key key_len ref rows Extra -t1 index NULL b 4 NULL 4 -explain select * from t1 order by c; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 Using filesort -explain select a from t1 order by a; -table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 4 Using index -explain select b from t1 order by b; -table type possible_keys key key_len ref rows Extra -t1 index NULL b 4 NULL 4 Using index -explain select a,b from t1 order by b; -table type possible_keys key key_len ref rows Extra -t1 index NULL b 4 NULL 4 Using index -explain select a,b from t1; -table type possible_keys key key_len ref rows Extra -t1 index NULL b 4 NULL 4 Using index -explain select a,b,c from t1; -table type possible_keys key key_len ref rows Extra -t1 ALL NULL NULL NULL NULL 4 drop table t1; create table t1 (t int not null default 1, key (t)) type=innodb; desc t1; @@ -1234,9 +1186,6 @@ insert into t1 (a) select b from t2; select count(*) from t1; count(*) 29267 -explain select * from t1 where c between 1 and 10000; -table type possible_keys key key_len ref rows Extra -t1 range c c 5 NULL 1 Using where update t1 set c=a; drop table t1,t2; create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) type=innodb; -- cgit v1.2.1 From e22a31f173d52e494baa547657ef090d83abd263 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 01:08:36 +0200 Subject: I committed the same changeset in my tree yesterday, but broke the tree since, so I commit again in a fresh tree. Fix for bug#763 (Relay_log_space too big by 4 bytes), plus comments and DBUG_PRINT, and we don't start replication if --bootstrap. mysql-test/r/rpl_log.result: Result update mysql-test/r/rpl_log_pos.result: Result update sql/mysqld.cc: Don't start replication if in bootstrap mode (bootstrap isn't supposed to have several threads). sql/slave.cc: Fix for bug 763 (Relay_log_space too big by 4 bytes). A DBUG_PRINT. sql/sql_acl.cc: Replaced a return by DBUG_RETURN (happened to find this reading a debug log). sql/sql_repl.cc: A comment. --- mysql-test/r/rpl_log.result | 2 +- mysql-test/r/rpl_log_pos.result | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_log.result b/mysql-test/r/rpl_log.result index 316bf1f3dd0..616ad319880 100644 --- a/mysql-test/r/rpl_log.result +++ b/mysql-test/r/rpl_log.result @@ -93,6 +93,6 @@ slave-bin.002 62 Query 1 168 use test; insert into t1 values (1) slave-bin.002 122 Query 1 228 use test; drop table t1 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.002 276 slave-relay-bin.002 1563 master-bin.002 Yes Yes 0 0 276 1567 +127.0.0.1 root MASTER_PORT 1 master-bin.002 276 slave-relay-bin.002 1563 master-bin.002 Yes Yes 0 0 276 1563 show binlog events in 'slave-bin.005' from 4; Error when executing command SHOW BINLOG EVENTS: Could not find target log diff --git a/mysql-test/r/rpl_log_pos.result b/mysql-test/r/rpl_log_pos.result index 3224e84fa31..f7e59e55577 100644 --- a/mysql-test/r/rpl_log_pos.result +++ b/mysql-test/r/rpl_log_pos.result @@ -9,7 +9,7 @@ File Position Binlog_do_db Binlog_ignore_db master-bin.001 79 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.001 79 slave-relay-bin.002 120 master-bin.001 Yes Yes 0 0 79 124 +127.0.0.1 root MASTER_PORT 1 master-bin.001 79 slave-relay-bin.002 120 master-bin.001 Yes Yes 0 0 79 120 slave stop; change master to master_log_pos=73; slave start; -- cgit v1.2.1 From d433fe5580dafbc1fba0ebe9dc76217d1b0e5144 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 10:42:13 +0500 Subject: Proposed bugfix for #717 Current table is placed into read_tables set of the current join_tab->select so get_mm_parts function thinks that current table's record is read and tries to calculate WHERE condition with the fields of the record. Result of these calculations is unpredictable. Looks funny - outcome of the SELECT depends on the queries executed before. Anyway i think we should have testcase on this part of the code. mysql-test/r/sel000001.result: appropriate result added mysql-test/t/sel000001.test: testcase added sql/sql_select.cc: I think we should count current table out of read_tables set --- mysql-test/r/sel000001.result | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/sel000001.result b/mysql-test/r/sel000001.result index 28f6d3d1d5a..6b6b9b7dffc 100644 --- a/mysql-test/r/sel000001.result +++ b/mysql-test/r/sel000001.result @@ -5,3 +5,17 @@ SELECT s, id FROM t1 WHERE s = 'mouse'; s id mouse 3 drop table t1; +CREATE TABLE t1 ( +node int(11) NOT NULL default '0', +maxchild int(11) NOT NULL default '0', +PRIMARY KEY (`node`) +); +INSERT INTO t1 (node, maxchild) VALUES (4,4),(5,5),(1,244); +SELECT * FROM t1 g1, t1 g2 +WHERE g1.node <= g2.node and g2.node <= g1.maxchild and g2.node = g2.maxchild; +node maxchild node maxchild +4 4 4 4 +5 5 5 5 +1 244 4 4 +1 244 5 5 +DROP TABLE t1; -- cgit v1.2.1 From 2373397e13d4004816b61df38a234906eb361e0d Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 11:55:36 +0300 Subject: Cleaner implementation if INSERT ... SELECT with same tables Tests cleanup (put drop database first in tests) client/mysql.cc: Cleanup of code in last pull include/config-win.h: Remove HAVE_CHSIZE on windows as it's not 64 bit clean include/my_global.h: Portability fix mysql-test/r/drop.result: Clean up results mysql-test/r/flush.result: Clean up results mysql-test/r/grant_cache.result: Clean up results mysql-test/r/innodb.result: Clean up results mysql-test/r/insert_select.result: Clean up results mysql-test/r/merge.result: Clean up results mysql-test/r/query_cache.result: Clean up results mysql-test/t/drop.test: Clean up tests mysql-test/t/flush.test: Clean up tests mysql-test/t/grant_cache.test: Clean up tests mysql-test/t/innodb.test: Clean up tests mysql-test/t/insert_select.test: Added more tests mysql-test/t/merge.test: Test of bug 515 mysql-test/t/query_cache.test: Clean up tests mysql-test/t/symlink.test: Clean up tests sql/mysql_priv.h: Cleaner implementation if INSERT ... SELECT with same tables sql/sql_lex.h: Cleaner implementation if INSERT ... SELECT with same tables sql/sql_list.h: Indentation cleanup sql/sql_parse.cc: Cleaner implementation if INSERT ... SELECT with same tables sql/sql_yacc.yy: Cleaner implementation if INSERT ... SELECT with same tables --- mysql-test/r/ctype_cp1251.result | 24 ++++++++++++++++++++++++ mysql-test/r/drop.result | 2 -- mysql-test/r/flush.result | 2 +- mysql-test/r/grant_cache.result | 1 + mysql-test/r/innodb.result | 1 + mysql-test/r/insert_select.result | 20 ++++++++++++++++++++ mysql-test/r/merge.result | 12 ++++++++++++ mysql-test/r/query_cache.result | 3 ++- 8 files changed, 61 insertions(+), 4 deletions(-) create mode 100644 mysql-test/r/ctype_cp1251.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result new file mode 100644 index 00000000000..a1f860e1f83 --- /dev/null +++ b/mysql-test/r/ctype_cp1251.result @@ -0,0 +1,24 @@ +drop table if exists t1; +create table t1 (a varchar(10) not null); +insert into t1 values ("a"),("ab"),("abc"); +select * from t1; +a +a +ab +abc +select a, left(a,1) as b from t1; +a b +a a +ab a +abc a +select a, left(a,1) as b from t1 group by a; +a b +a a +ab a +abc a +SELECT DISTINCT RIGHT(a,1) from t1; +RIGHT(a,1) +a +b +c +drop table t1; diff --git a/mysql-test/r/drop.result b/mysql-test/r/drop.result index 178c3a8cb4f..5c732aebbcc 100644 --- a/mysql-test/r/drop.result +++ b/mysql-test/r/drop.result @@ -1,5 +1,4 @@ drop table if exists t1; -drop table if exists t1; drop table t1; Unknown table 't1' create table t1(n int); @@ -24,7 +23,6 @@ n drop database if exists mysqltest; create database mysqltest; drop database mysqltest; -drop database if exists mysqltest; flush tables with read lock; create database mysqltest; Got one of the listed errors diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result index 7080f2b6145..8dc73e446c7 100644 --- a/mysql-test/r/flush.result +++ b/mysql-test/r/flush.result @@ -1,4 +1,5 @@ drop table if exists t1; +drop database if exists mysqltest; create temporary table t1(n int not null primary key); drop table if exists t2; create table t2(n int); @@ -11,7 +12,6 @@ drop table t2; Table 't2' was locked with a READ lock and can't be updated drop table t2; unlock tables; -drop database if exists mysqltest; create database mysqltest; create table mysqltest.t1(n int); insert into mysqltest.t1 values (23); diff --git a/mysql-test/r/grant_cache.result b/mysql-test/r/grant_cache.result index 3892765f587..fca6864cc63 100644 --- a/mysql-test/r/grant_cache.result +++ b/mysql-test/r/grant_cache.result @@ -1,4 +1,5 @@ drop table if exists test.t1,mysqltest.t1,mysqltest.t2; +drop database if exists mysqltest; reset query cache; flush status; create database if not exists mysqltest; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 465d4faf0bc..cb6c8397c5a 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1,4 +1,5 @@ drop table if exists t1,t2,t3; +drop database if exists mysqltest; create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=innodb; insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); select id, code, name from t1 order by id; diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index dff5d0ff3ca..c007191b562 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -566,4 +566,24 @@ a 5 5 5 +insert into t1 select * from t1,t1; +Not unique table/alias: 't1' +drop table t1,t2; +create table t1 (a int not null primary key, b char(10)); +create table t2 (a int not null, b char(10)); +insert into t1 values (1,"t1:1"),(3,"t1:3"); +insert into t2 values (2,"t2:2"), (3,"t2:3"); +insert into t1 select * from t2; +Duplicate entry '3' for key 1 +select * from t1; +a b +1 t1:1 +3 t1:3 +2 t2:2 +replace into t1 select * from t2; +select * from t1; +a b +1 t1:1 +3 t2:3 +2 t2:2 drop table t1,t2; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 0bffc93c284..cf39faea41b 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -596,3 +596,15 @@ AND file_code = '0000000115' LIMIT 1; table type possible_keys key key_len ref rows Extra t2 const PRIMARY,files PRIMARY 33 const,const 1 DROP TABLE IF EXISTS t1, t2; +create table t1 (x int, y int, index xy(x, y)); +create table t2 (x int, y int, index xy(x, y)); +create table t3 (x int, y int, index xy(x, y)) type=merge union=(t1,t2); +insert into t1 values(1, 2); +insert into t2 values(1, 3); +select * from t3 where x = 1 and y < 5 order by y; +x y +1 2 +1 3 +select * from t3 where x = 1 and y < 5 order by y desc; +x y +drop table t1,t2,t3; diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 6abd572b3d9..0ef0cab423b 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -3,6 +3,7 @@ flush query cache; reset query cache; flush status; drop table if exists t1,t2,t3,t11,t21, mysqltest.t1; +drop database if exists mysqltest; create table t1 (a int not null); insert into t1 values (1),(2),(3); select * from t1; @@ -357,7 +358,7 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 drop table t1,t2; -create database if not exists mysqltest; +create database mysqltest; create table mysqltest.t1 (i int not null auto_increment, a int, primary key (i)); insert into mysqltest.t1 (a) values (1); select * from mysqltest.t1 where i is null; -- cgit v1.2.1 From f7f191904b91136fa1f79bf11bb4ee3400d8a48f Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 16:48:47 +0500 Subject: Testcase for #628 mysql-test/r/func_misc.result: Appropriate result mysql-test/t/func_misc.test: Test case added --- mysql-test/r/func_misc.result | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 8d05adcc1ba..d51bea020ed 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -13,3 +13,6 @@ NULL 255.255.1.255 0.0.1.255 select length(format('nan', 2)) > 0; length(format('nan', 2)) > 0 1 +select concat("$",format(2500,2)); +concat("$",format(2500,2)) +$2,500.00 -- cgit v1.2.1 From 6d1f44e4fdc3eb89ae5a6e5c21660ab5c233819f Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 18:21:59 +0500 Subject: Fix and test-case for the bug #775: SELECT misses rows in indexed HEAP table columns. heap/hp_open.c: Fix for the bug #775: SELECT misses rows in indexed HEAP table columns. mysql-test/r/heap.result: Test-case for the bug #775: SELECT misses rows in indexed HEAP table columns. mysql-test/t/heap.test: Test-case for the bug #775: SELECT misses rows in indexed HEAP table columns. --- mysql-test/r/heap.result | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result index a04ddf3f302..bdf7dc225f3 100644 --- a/mysql-test/r/heap.result +++ b/mysql-test/r/heap.result @@ -197,6 +197,20 @@ a b INSERT INTO t1 VALUES (1,3); Duplicate entry '3' for key 1 DROP TABLE t1; +CREATE TABLE t1 ( +a int default NULL, +key a (a) +) TYPE=HEAP; +INSERT INTO t1 VALUES (10), (10), (10); +EXPLAIN SELECT * FROM t1 WHERE a=10; +table type possible_keys key key_len ref rows Extra +t1 ref a a 5 const 10 Using where +SELECT * FROM t1 WHERE a=10; +a +10 +10 +10 +DROP TABLE t1; CREATE TABLE t1 (a int not null, primary key(a)) type=heap; INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); DELETE from t1 where a < 100; -- cgit v1.2.1 From b170d899e8772ea513a04fe8ae9c1f1ec125ea1c Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 19:01:01 +0500 Subject: Test case for #570 added mysql-test/r/alias.result: Appropriate test result mysql-test/t/alias.test: Testcase for #570 --- mysql-test/r/alias.result | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/alias.result b/mysql-test/r/alias.result index 3954c2d0170..55397705bdf 100644 --- a/mysql-test/r/alias.result +++ b/mysql-test/r/alias.result @@ -59,3 +59,14 @@ SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA', Kundentyp kategorie Privat (Private Nutzung) Mobilfunk drop table t1; +CREATE TABLE t1 ( +AUFNR varchar(12) NOT NULL default '', +PLNFL varchar(6) NOT NULL default '', +VORNR varchar(4) NOT NULL default '', +xstatus_vor smallint(5) unsigned NOT NULL default '0', +); +INSERT INTO t1 VALUES ('40004712','000001','0010',9); +INSERT INTO t1 VALUES ('40004712','000001','0020',0); +UPDATE t1 SET t1.xstatus_vor = Greatest(t1.xstatus_vor,1) WHERE t1.aufnr = +"40004712" AND t1.plnfl = "000001" AND t1.vornr > "0010" ORDER BY t1.vornr +ASC LIMIT 1; -- cgit v1.2.1 From 8d7eb4a0e5b7d71e09c689fcc0318382c780302e Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 20:57:51 +0500 Subject: Proposed fix for #751 Fields in key_expr (mysql_ha_read) wasn't linked to tables. Hmm. How did it work at all? mysql-test/r/alias.result: It's better to delete table after the test mysql-test/r/handler.result: appropriate result mysql-test/t/alias.test: it's better to drop table after test mysql-test/t/handler.test: test case for #751 sql/sql_handler.cc: fix_fields called --- mysql-test/r/alias.result | 1 + mysql-test/r/handler.result | 6 ++++++ 2 files changed, 7 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/alias.result b/mysql-test/r/alias.result index 55397705bdf..5ed10b58929 100644 --- a/mysql-test/r/alias.result +++ b/mysql-test/r/alias.result @@ -70,3 +70,4 @@ INSERT INTO t1 VALUES ('40004712','000001','0020',0); UPDATE t1 SET t1.xstatus_vor = Greatest(t1.xstatus_vor,1) WHERE t1.aufnr = "40004712" AND t1.plnfl = "000001" AND t1.vornr > "0010" ORDER BY t1.vornr ASC LIMIT 1; +drop table t1; diff --git a/mysql-test/r/handler.result b/mysql-test/r/handler.result index 3be24305e09..525fd1a4495 100644 --- a/mysql-test/r/handler.result +++ b/mysql-test/r/handler.result @@ -146,3 +146,9 @@ alter table t1 type=MyISAM; handler t2 read first; Unknown table 't2' in HANDLER drop table t1; +create table t1(a int, index(a)); +insert into t1 values (1), (2), (3); +handler t1 open; +handler t1 read a=(W); +Unknown column 'W' in 'field list' +drop table t1; -- cgit v1.2.1 From abe124e76bc12d3cefe5502ad1799461090c7365 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 19:23:06 +0300 Subject: Fix for UNIXWARE 7 Remove unaligned warnings on Ia64 from client library when using --host Fix for replication when using many file descriptors include/my_global.h: Fix for UNIXWARE 7 libmysql/libmysql.c: Portability fix (removes unaligned warnings on Ia64) mysql-test/r/symlink.result: Updated results sql/mini_client.cc: Ported connect timeout code from libmysql.c --- mysql-test/r/symlink.result | 9 +++++---- 1 file changed, 5 insertions(+), 4 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 5626bc0fe11..ab699ac4c4e 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -1,4 +1,5 @@ drop table if exists t1,t2,t7,t8,t9; +drop database if exists mysqltest; create table t1 (a int not null auto_increment, b char(16) not null, primary key (a)); create table t2 (a int not null auto_increment, b char(16) not null, primary key (a)); insert into t1 (b) values ("test"),("test1"),("test2"),("test3"); @@ -49,11 +50,11 @@ Got one of the listed errors Got one of the listed errors Got one of the listed errors Got one of the listed errors -alter table t9 rename test_mysqltest.t9; -select count(*) from test_mysqltest.t9; +alter table t9 rename mysqltest.t9; +select count(*) from mysqltest.t9; count(*) 16724 -show create table test_mysqltest.t9; +show create table mysqltest.t9; Table Create Table t9 CREATE TABLE `t9` ( `a` int(11) NOT NULL auto_increment, @@ -62,4 +63,4 @@ t9 CREATE TABLE `t9` ( `d` int(11) NOT NULL default '0', PRIMARY KEY (`a`) ) TYPE=MyISAM -drop database test_mysqltest; +drop database mysqltest; -- cgit v1.2.1 From 33c416f67e6ba11ca1e21888be6cd9c0cc647813 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 3 Jul 2003 23:24:23 +0500 Subject: Fix for #739 check for negative count in RPAD added mysql-test/r/func_str.result: Appropriate result mysql-test/t/func_str.test: testcase added sql/item_strfunc.cc: we return NULL if count < 0 --- mysql-test/r/func_str.result | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 09818c14462..0bac5111a05 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -247,3 +247,13 @@ elt(status_wnio,data_podp) NULL NULL DROP TABLE t1; +CREATE TABLE t1 ( +title text +) TYPE=MyISAM; +INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education'); +INSERT INTO t1 VALUES ('House passes the CAREERS bill'); +SELECT CONCAT("",RPAD("",(55 - LENGTH(title)),".")) from t1; +CONCAT("",RPAD("",(55 - LENGTH(title)),".")) +NULL +.......................... +DROP TABLE t1; -- cgit v1.2.1 From 4b50f4f252bf28b628475339e320d7a927c90459 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 4 Jul 2003 12:55:25 +0200 Subject: handler should be used with constant expressions only (or rand) --- mysql-test/r/handler.result | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/handler.result b/mysql-test/r/handler.result index 525fd1a4495..1973797ae79 100644 --- a/mysql-test/r/handler.result +++ b/mysql-test/r/handler.result @@ -151,4 +151,6 @@ insert into t1 values (1), (2), (3); handler t1 open; handler t1 read a=(W); Unknown column 'W' in 'field list' +handler t1 read a=(a); +Wrong arguments to HANDLER ... READ drop table t1; -- cgit v1.2.1 From 30341167c50dd59ecdd11100b0d5e2fec14ca02b Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 4 Jul 2003 17:15:16 +0500 Subject: fix and test case for the bug #787: HANDLER without INDEX doesn't work with deleted rows mysql-test/r/handler.result: test case for the bug #787: HANDLER without INDEX doesn't work with deleted rows mysql-test/t/handler.test: test case for the bug #787: HANDLER without INDEX doesn't work with deleted rows sql/sql_handler.cc: fix for the bug #787: HANDLER without INDEX doesn't work with deleted rows --- mysql-test/r/handler.result | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/handler.result b/mysql-test/r/handler.result index 1973797ae79..4af38807518 100644 --- a/mysql-test/r/handler.result +++ b/mysql-test/r/handler.result @@ -146,6 +146,25 @@ alter table t1 type=MyISAM; handler t2 read first; Unknown table 't2' in HANDLER drop table t1; +create table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6); +delete from t1 limit 2; +handler t1 open; +handler t1 read first; +a +3 +handler t1 read first limit 1,1; +a +4 +handler t1 read first limit 2,2; +a +5 +6 +delete from t1 limit 3; +handler t1 read first; +a +6 +drop table t1; create table t1(a int, index(a)); insert into t1 values (1), (2), (3); handler t1 open; -- cgit v1.2.1 From 2848b0bed9998378b10ac181cd7223c5853549d9 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 4 Jul 2003 14:42:57 +0200 Subject: merge + order by (bug #515) temporary fix properly fixed in 4.1 --- mysql-test/r/merge.result | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index cf39faea41b..650fdb1c77b 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -607,4 +607,6 @@ x y 1 3 select * from t3 where x = 1 and y < 5 order by y desc; x y +1 3 +1 2 drop table t1,t2,t3; -- cgit v1.2.1 From 421a462990e42eca893d902aeb590acdb64b4a51 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 5 Jul 2003 16:29:11 +0300 Subject: missing test case for DISTINCT .. LEFT .. --- mysql-test/r/distinct.result | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 53a20eeea0b..1dbccb65748 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -419,3 +419,12 @@ SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c; a b 1 4 DROP TABLE IF EXISTS t1,t2; +CREATE table t1 ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) TYPE=MyISAM AUTO_INCREMENT=3 ; +INSERT INTO t1 VALUES (1, 'aaaaa'); +INSERT INTO t1 VALUES (3, 'aaaaa'); +INSERT INTO t1 VALUES (2, 'eeeeeee'); +select distinct left(name,1) as name from t1; +name +a +e +drop table t1; -- cgit v1.2.1 From 27601fc5ee2366ddb7926a82aa50058dc9548e0e Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 6 Jul 2003 17:59:54 +0200 Subject: WL#912 (more user control on relay logs): FLUSH LOGS now rotates relay logs, and a new variable max_relay_log_size. Plus a very small bit of code cleaning. libmysqld/lib_sql.cc: open_log has no default arguments anymore. mysql-test/r/rpl_flush_log_loop.result: result update now that FLUSH LOGS rotates relay logs. mysql-test/r/rpl_log.result: result update now that FLUSH LOGS rotates relay logs. mysql-test/r/rpl_rotate_logs.result: result update now that max_binlog_size is 4096. mysql-test/t/rpl_rotate_logs-master.opt: now max_binlog_size must be a multiple of 4096 (see change in mysqld.cc) sql/log.cc: Got rid of default arguments of various MYSQL_LOG methods (the default arguments made code reading uneasy). Set max_size in ::init(). New function set_max_size() to set max_size of a MYSQL_LOG on-the-fly. More DBUG info. sql/mysql_priv.h: no defaults in open_log(). New variables max_relay_log_size. sql/mysqld.cc: New variable and option max_relay_log_size. max_binlog_size and max_relay_log_size are multiples of IO_SIZE. No more default arguments for log functions. sql/set_var.cc: New variable max_relay_log_size. If it is 0, then max_binlog_size will apply to relay logs. When one of these variables is changed, fix_max_%log_size is called to update max_size of the binary and/or relay logs. sql/slave.cc: New function rotate_relay_log(). sql/slave.h: New function rotate_relay_log(). sql/sql_class.h: New member max_size of MYSQL_LOG (for automatic rotation). New method set_max_size() for setting on-the-fly. sql/sql_parse.cc: Flush the relay log in FLUSH LOGS. --- mysql-test/r/rpl_flush_log_loop.result | 2 +- mysql-test/r/rpl_log.result | 2 +- mysql-test/r/rpl_max_relay_size.result | 61 ++++++++++++++++++++++++++++++++++ mysql-test/r/rpl_rotate_logs.result | 6 ++-- 4 files changed, 65 insertions(+), 6 deletions(-) create mode 100644 mysql-test/r/rpl_max_relay_size.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_flush_log_loop.result b/mysql-test/r/rpl_flush_log_loop.result index da2930f30c3..3273ce43a17 100644 --- a/mysql-test/r/rpl_flush_log_loop.result +++ b/mysql-test/r/rpl_flush_log_loop.result @@ -14,4 +14,4 @@ slave start; flush logs; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root SLAVE_PORT 60 slave-bin.001 79 relay-log.001 119 slave-bin.001 Yes Yes 0 0 79 119 +127.0.0.1 root SLAVE_PORT 60 slave-bin.001 79 relay-log.002 119 slave-bin.001 Yes Yes 0 0 79 119 diff --git a/mysql-test/r/rpl_log.result b/mysql-test/r/rpl_log.result index 616ad319880..640e6f02103 100644 --- a/mysql-test/r/rpl_log.result +++ b/mysql-test/r/rpl_log.result @@ -93,6 +93,6 @@ slave-bin.002 62 Query 1 168 use test; insert into t1 values (1) slave-bin.002 122 Query 1 228 use test; drop table t1 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.002 276 slave-relay-bin.002 1563 master-bin.002 Yes Yes 0 0 276 1563 +127.0.0.1 root MASTER_PORT 1 master-bin.002 276 slave-relay-bin.003 211 master-bin.002 Yes Yes 0 0 276 211 show binlog events in 'slave-bin.005' from 4; Error when executing command SHOW BINLOG EVENTS: Could not find target log diff --git a/mysql-test/r/rpl_max_relay_size.result b/mysql-test/r/rpl_max_relay_size.result new file mode 100644 index 00000000000..5c5a9d622e5 --- /dev/null +++ b/mysql-test/r/rpl_max_relay_size.result @@ -0,0 +1,61 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +stop slave; +create table t1 (a int); +drop table t1; +reset slave; +set global max_binlog_size=8192; +set global max_relay_log_size=8192-1; +select @@global.max_relay_log_size; +@@global.max_relay_log_size +4096 +start slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root 9306 1 master-bin.001 50477 slave-relay-bin.014 1221 master-bin.001 Yes Yes 0 0 50477 1221 +stop slave; +reset slave; +set global max_relay_log_size=(5*4096); +select @@global.max_relay_log_size; +@@global.max_relay_log_size +20480 +start slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root 9306 1 master-bin.001 50477 slave-relay-bin.004 9457 master-bin.001 Yes Yes 0 0 50477 9457 +stop slave; +reset slave; +set global max_relay_log_size=0; +select @@global.max_relay_log_size; +@@global.max_relay_log_size +0 +start slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root 9306 1 master-bin.001 50477 slave-relay-bin.008 1283 master-bin.001 Yes Yes 0 0 50477 1283 +stop slave; +reset slave; +flush logs; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root 9306 1 4 slave-relay-bin.001 4 No No 0 0 0 4 +reset slave; +start slave; +flush logs; +create table t1 (a int); +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root 9306 1 master-bin.001 50535 slave-relay-bin.009 62 master-bin.001 Yes Yes 0 0 50535 62 +flush logs; +drop table t1; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root 9306 1 master-bin.001 50583 slave-relay-bin.010 52 master-bin.001 Yes Yes 0 0 50583 52 +flush logs; +show master status; +File Position Binlog_do_db Binlog_ignore_db +master-bin.002 4 diff --git a/mysql-test/r/rpl_rotate_logs.result b/mysql-test/r/rpl_rotate_logs.result index f49006e8e05..07490744d81 100644 --- a/mysql-test/r/rpl_rotate_logs.result +++ b/mysql-test/r/rpl_rotate_logs.result @@ -65,17 +65,15 @@ show master logs; Log_name master-bin.003 master-bin.004 -master-bin.005 -master-bin.006 show master status; File Position Binlog_do_db Binlog_ignore_db -master-bin.006 838 +master-bin.004 2886 select * from t4; a testing temporary tables part 2 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 60 master-bin.006 838 slave-relay-bin.001 8034 master-bin.006 Yes Yes 0 0 838 8034 +127.0.0.1 root MASTER_PORT 60 master-bin.004 2886 slave-relay-bin.001 7870 master-bin.004 Yes Yes 0 0 2886 7870 lock tables t3 read; select count(*) from t3 where n >= 4; count(*) -- cgit v1.2.1 From a4c3f0d7271c6d93b13d2b03b640adb624fd494d Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 7 Jul 2003 19:02:58 +0200 Subject: finally proper recursive descent for parsing expressions with MATCH ... AGAINST in add_ft_keys() --- mysql-test/r/fulltext.result | 43 +++++++++++++++++++++++++++++++++++-------- 1 file changed, 35 insertions(+), 8 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 6f15b2eb973..646c1a7bee2 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -5,9 +5,6 @@ INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ('Only MyISAM tables','support collections'), ('Function MATCH ... AGAINST()','is used to do a search'), ('Full-text search in MySQL', 'implements vector space model'); -explain select * from t1 where MATCH(a,b) AGAINST ("collections"); -table type possible_keys key key_len ref rows Extra -t1 fulltext a a 0 1 Using where select * from t1 where MATCH(a,b) AGAINST ("collections"); a b Only MyISAM tables support collections @@ -19,11 +16,36 @@ select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); a b Full-text indexes are called collections Only MyISAM tables support collections -select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes"); -a b -Only MyISAM tables support collections -Full-text indexes are called collections -Full-text indexes are called collections +explain select * from t1 where MATCH(a,b) AGAINST ("collections"); +table type possible_keys key key_len ref rows Extra +t1 fulltext a a 0 1 Using where +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0; +table type possible_keys key key_len ref rows Extra +t1 fulltext a a 0 1 Using where +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1; +table type possible_keys key key_len ref rows Extra +t1 fulltext a a 0 1 Using where +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 5 Using where +explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1; +table type possible_keys key key_len ref rows Extra +t1 fulltext a a 0 1 Using where +explain select * from t1 where 00 and a like '%ll%'; +table type possible_keys key key_len ref rows Extra +t1 fulltext a a 0 1 Using where select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); a b MySQL has now support for full-text search @@ -98,6 +120,11 @@ select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE); a b MySQL has now support for full-text search Function MATCH ... AGAINST() is used to do a search +select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes"); +a b +Only MyISAM tables support collections +Full-text indexes are called collections +Full-text indexes are called collections delete from t1 where a like "MySQL%"; update t1 set a='some test foobar' where MATCH a,b AGAINST ('model'); delete from t1 where MATCH(a,b) AGAINST ("indexes"); -- cgit v1.2.1 From eed62d993d1355c5e84c0b0b17d417e84086c526 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 8 Jul 2003 15:50:57 +0200 Subject: Fix for BUG#797 "If query ignored on slave (replicate-ignore-table) the slave still checks if the returned error (0) is the same as the one on the master, whereas it shouldn't test this. Plus a new test for BUG#797. include/mysqld_error.h: New error code (can be sent only to the slave thread, not to normal clients). sql/log.cc: removed comment from a previous debugging. sql/log_event.cc: ER_SLAVE_IGNORED_TABLE is an ignorable error. sql/share/czech/errmsg.txt: for BUG#797 sql/share/danish/errmsg.txt: for BUG#797 sql/share/dutch/errmsg.txt: for BUG#797 sql/share/english/errmsg.txt: for BUG#797 sql/share/estonian/errmsg.txt: for BUG#797 sql/share/french/errmsg.txt: for BUG#797 sql/share/german/errmsg.txt: for BUG#797 sql/share/greek/errmsg.txt: for BUG#797 sql/share/hungarian/errmsg.txt: for BUG#797 sql/share/italian/errmsg.txt: for BUG#797 sql/share/japanese/errmsg.txt: for BUG#797 sql/share/korean/errmsg.txt: for BUG#797 sql/share/norwegian-ny/errmsg.txt: for BUG#797 sql/share/norwegian/errmsg.txt: for BUG#797 sql/share/polish/errmsg.txt: for BUG#797 sql/share/portuguese/errmsg.txt: for BUG#797 sql/share/romanian/errmsg.txt: for BUG#797 sql/share/russian/errmsg.txt: for BUG#797 sql/share/slovak/errmsg.txt: for BUG#797 sql/share/spanish/errmsg.txt: for BUG#797 sql/share/swedish/errmsg.txt: for BUG#797 sql/share/ukrainian/errmsg.txt: for BUG#797 sql/sql_parse.cc: when in sql_parse in the slave thread we ignore the query because of replicate-do and replicate-ignore options, we return a specific error to the slave thread, so that it knows that the query has been ignored (which is different from a successful query). A small cleanup (test was done twice). --- mysql-test/r/rpl_error_ignored_table.result | 15 +++++++++++++++ 1 file changed, 15 insertions(+) create mode 100644 mysql-test/r/rpl_error_ignored_table.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_error_ignored_table.result b/mysql-test/r/rpl_error_ignored_table.result new file mode 100644 index 00000000000..826e97a8004 --- /dev/null +++ b/mysql-test/r/rpl_error_ignored_table.result @@ -0,0 +1,15 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +create table t1 (a int primary key); +insert into t1 values (1),(1); +Duplicate entry '1' for key 1 +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root 9306 1 master-bin.001 213 slave-relay-bin.002 254 master-bin.001 Yes Yes 0 0 213 254 +show tables like 't1'; +Tables_in_test (t1) +drop table t1; -- cgit v1.2.1 From e13e857cae1ecaa322a0b4dec76e5b5dfb4f53a0 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 8 Jul 2003 23:58:04 +0300 Subject: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS Removed double my_thread_end() which caused fatal error on windows if mysqld died on startup myisam/mi_extra.c: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS mysql-test/r/alter_table.result: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS mysql-test/t/alter_table.test: Test DISABLE/ENABLE KEY sql/ha_myisam.cc: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS sql/mysqld.cc: Removed double my_thread_end() which caused fatal error on windows if mysqld died on startup sql/sql_table.cc: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS sql/table.cc: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS sql/table.h: Fixed bug in ALTER TABLE ... DISABLE/ENABLE KEYS --- mysql-test/r/alter_table.result | 69 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 69 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index e2d9cc30ad9..7ade3313abe 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -281,3 +281,72 @@ ALTER TABLE t1 DISABLE KEYS; INSERT DELAYED INTO t1 VALUES(1),(2),(3); ALTER TABLE t1 ENABLE KEYS; drop table t1; +CREATE TABLE t1 ( +Host varchar(16) binary NOT NULL default '', +User varchar(16) binary NOT NULL default '', +PRIMARY KEY (Host,User) +) TYPE=MyISAM; +ALTER TABLE t1 DISABLE KEYS; +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty'); +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE +t1 0 PRIMARY 2 User A 3 NULL NULL BTREE +ALTER TABLE t1 ENABLE KEYS; +UNLOCK TABLES; +CHECK TABLES t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +Host varchar(16) binary NOT NULL default '', +User varchar(16) binary NOT NULL default '', +PRIMARY KEY (Host,User), +KEY (Host) +) TYPE=MyISAM; +ALTER TABLE t1 DISABLE KEYS; +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE +t1 0 PRIMARY 2 User A 0 NULL NULL BTREE +t1 1 Host 1 Host A NULL NULL NULL BTREE disabled +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES ('localhost','root'),('localhost',''); +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE +t1 0 PRIMARY 2 User A 2 NULL NULL BTREE +t1 1 Host 1 Host A NULL NULL NULL BTREE disabled +ALTER TABLE t1 ENABLE KEYS; +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE +t1 0 PRIMARY 2 User A 2 NULL NULL BTREE +t1 1 Host 1 Host A 1 NULL NULL BTREE +UNLOCK TABLES; +CHECK TABLES t1; +Table Op Msg_type Msg_text +test.t1 check status OK +LOCK TABLES t1 WRITE; +ALTER TABLE t1 RENAME t2; +UNLOCK TABLES; +select * from t2; +Host User +localhost +localhost root +DROP TABLE t2; +CREATE TABLE t1 ( +Host varchar(16) binary NOT NULL default '', +User varchar(16) binary NOT NULL default '', +PRIMARY KEY (Host,User), +KEY (Host) +) TYPE=MyISAM; +LOCK TABLES t1 WRITE; +ALTER TABLE t1 DISABLE KEYS; +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE +t1 0 PRIMARY 2 User A 0 NULL NULL BTREE +t1 1 Host 1 Host A NULL NULL NULL BTREE disabled +DROP TABLE t1; -- cgit v1.2.1 From 830e53c8b18b78639833ae840ac92163a8d364e7 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 10 Jul 2003 15:27:02 +0500 Subject: Fix for the bug #803. Now INTERVAL(NULL, N1, N2, ...) returns NULL. --- mysql-test/r/func_set.result | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result index 5305c16c5d9..dfd7750adaf 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -25,3 +25,6 @@ find_in_set("","a,b,c") find_in_set("","a,b,c,") find_in_set("",",a,b,c") select find_in_set("abc","abc"),find_in_set("ab","abc"),find_in_set("abcd","abc"); find_in_set("abc","abc") find_in_set("ab","abc") find_in_set("abcd","abc") 1 0 0 +select interval(null, 1, 10, 100); +interval(null, 1, 10, 100) +NULL -- cgit v1.2.1 From 1a45c8397258d992bdc526d3463b92ad448a3754 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 11 Jul 2003 14:17:18 +0500 Subject: Fix for the bug #801: create table t1 select x'41'; doesn't work --- mysql-test/r/create.result | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 2e2aa41c671..e6192eb6ccb 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -176,3 +176,5 @@ Column 'k1' cannot be null insert into t1 values (NULL, NULL); Column 'k1' cannot be null drop table t1; +create table t1 select x'4132'; +drop table t1; -- cgit v1.2.1 From 1c5773add420572a012d9f2d8e382dc15e5e1107 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 11 Jul 2003 22:18:53 +0200 Subject: - fixed an error in the rpl_error_ignored_table test suite: the master port number can be different from 3306 when doing the release builds with Do-compile, therefore it has to be replaced with the correct value during the test run using the "--replace_result" function. mysql-test/r/rpl_error_ignored_table.result: - replaced hard-coded master port number with MASTER_PORT variable, since the port number is different when running the test suite during the release builds with "Do-compile". mysql-test/t/rpl_error_ignored_table.test: - replaced hard-coded master port number with MASTER_PORT variable, since the port number is different when running the test suite during the release builds with "Do-compile". --- mysql-test/r/rpl_error_ignored_table.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_error_ignored_table.result b/mysql-test/r/rpl_error_ignored_table.result index 826e97a8004..e1486220542 100644 --- a/mysql-test/r/rpl_error_ignored_table.result +++ b/mysql-test/r/rpl_error_ignored_table.result @@ -9,7 +9,7 @@ insert into t1 values (1),(1); Duplicate entry '1' for key 1 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root 9306 1 master-bin.001 213 slave-relay-bin.002 254 master-bin.001 Yes Yes 0 0 213 254 +127.0.0.1 root MASTER_PORT 1 master-bin.001 213 slave-relay-bin.002 254 master-bin.001 Yes Yes 0 0 213 254 show tables like 't1'; Tables_in_test (t1) drop table t1; -- cgit v1.2.1 From e1a30696034b70e3bf78036aa75a6e8389ebb2c4 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 14 Jul 2003 10:12:05 +0300 Subject: Safety and speedup fixes: Changed is_open() to work as before. Added back inited argument to LOG mysql-test/r/rpl_flush_log_loop.result: Fixed results (probably bug in previous rpatch) sql/handler.cc: Changed is_open() to work as before sql/item_func.cc: Changed is_open() to work as before sql/log.cc: Part revert of previous patch. The reason for adding back 'inited' is that is that we can't be 100 % sure that init_pthread_objects() is called before mysqld dies (for example on windows) I removed mutex lock handling in is_open() as the new code didn't have ANY affect except beeing slower. Added back checking of is_open() to some functions as we don't want to do a mutex lock when we are not using logging. Indentation/comment fixes sql/log_event.cc: Changed is_open() to work as before sql/repl_failsafe.cc: Changed is_open() to work as before sql/sql_base.cc: Changed is_open() to work as before sql/sql_class.h: Changed is_open() to work as before. Added back 'inited' variable sql/sql_db.cc: Changed is_open() to work as before sql/sql_delete.cc: Changed is_open() to work as before sql/sql_insert.cc: Changed is_open() to work as before sql/sql_load.cc: Changed is_open() to work as before sql/sql_parse.cc: Changed is_open() to work as before sql/sql_rename.cc: Changed is_open() to work as before sql/sql_repl.cc: Changed is_open() to work as before sql/sql_table.cc: Changed is_open() to work as before sql/sql_update.cc: Changed is_open() to work as before --- mysql-test/r/rpl_flush_log_loop.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_flush_log_loop.result b/mysql-test/r/rpl_flush_log_loop.result index 3273ce43a17..18786b1931b 100644 --- a/mysql-test/r/rpl_flush_log_loop.result +++ b/mysql-test/r/rpl_flush_log_loop.result @@ -14,4 +14,4 @@ slave start; flush logs; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root SLAVE_PORT 60 slave-bin.001 79 relay-log.002 119 slave-bin.001 Yes Yes 0 0 79 119 +127.0.0.1 root SLAVE_PORT 60 slave-bin.001 79 relay-log.002 4 slave-bin.001 Yes Yes 0 0 79 4 -- cgit v1.2.1 From e45b9ab481b26a8da6aa508437325ef7f00fb55f Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 14 Jul 2003 12:39:10 +0500 Subject: backport fix for the bug #803: INTERVAL(NULL, ....) --- mysql-test/r/func_set.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result index dfd7750adaf..eeeb216d142 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -27,4 +27,4 @@ find_in_set("abc","abc") find_in_set("ab","abc") find_in_set("abcd","abc") 1 0 0 select interval(null, 1, 10, 100); interval(null, 1, 10, 100) -NULL +-1 -- cgit v1.2.1 From 45db29dbc21488cca50ce60e58996ce57cd29616 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 14 Jul 2003 22:02:26 +0200 Subject: Added missing --replace_result for ports in SHOW SLAVE STATUS. mysql-test/r/rpl_max_relay_size.result: result update mysql-test/t/rpl_max_relay_size.test: port-independent test --- mysql-test/r/rpl_max_relay_size.result | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_max_relay_size.result b/mysql-test/r/rpl_max_relay_size.result index 5c5a9d622e5..1fa3fcd1adb 100644 --- a/mysql-test/r/rpl_max_relay_size.result +++ b/mysql-test/r/rpl_max_relay_size.result @@ -16,7 +16,7 @@ select @@global.max_relay_log_size; start slave; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root 9306 1 master-bin.001 50477 slave-relay-bin.014 1221 master-bin.001 Yes Yes 0 0 50477 1221 +127.0.0.1 root MASTER_PORT 1 master-bin.001 50477 slave-relay-bin.014 1221 master-bin.001 Yes Yes 0 0 50477 1221 stop slave; reset slave; set global max_relay_log_size=(5*4096); @@ -26,7 +26,7 @@ select @@global.max_relay_log_size; start slave; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root 9306 1 master-bin.001 50477 slave-relay-bin.004 9457 master-bin.001 Yes Yes 0 0 50477 9457 +127.0.0.1 root MASTER_PORT 1 master-bin.001 50477 slave-relay-bin.004 9457 master-bin.001 Yes Yes 0 0 50477 9457 stop slave; reset slave; set global max_relay_log_size=0; @@ -36,25 +36,25 @@ select @@global.max_relay_log_size; start slave; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root 9306 1 master-bin.001 50477 slave-relay-bin.008 1283 master-bin.001 Yes Yes 0 0 50477 1283 +127.0.0.1 root MASTER_PORT 1 master-bin.001 50477 slave-relay-bin.008 1283 master-bin.001 Yes Yes 0 0 50477 1283 stop slave; reset slave; flush logs; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root 9306 1 4 slave-relay-bin.001 4 No No 0 0 0 4 +127.0.0.1 root MASTER_PORT 1 4 slave-relay-bin.001 4 No No 0 0 0 4 reset slave; start slave; flush logs; create table t1 (a int); show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root 9306 1 master-bin.001 50535 slave-relay-bin.009 62 master-bin.001 Yes Yes 0 0 50535 62 +127.0.0.1 root MASTER_PORT 1 master-bin.001 50535 slave-relay-bin.009 62 master-bin.001 Yes Yes 0 0 50535 62 flush logs; drop table t1; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root 9306 1 master-bin.001 50583 slave-relay-bin.010 52 master-bin.001 Yes Yes 0 0 50583 52 +127.0.0.1 root MASTER_PORT 1 master-bin.001 50583 slave-relay-bin.010 52 master-bin.001 Yes Yes 0 0 50583 52 flush logs; show master status; File Position Binlog_do_db Binlog_ignore_db -- cgit v1.2.1 From 9942e0c7c65151d36b3ea93622d8a33b04860434 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 17 Jul 2003 20:17:46 +0200 Subject: my_strnncoll_latin1_de rewritten from scratch. Bug with overlapping combos went away. mysql-test/r/ctype_latin1_de.result: testing overlapping combos mysql-test/t/ctype_latin1_de.test: testing overlapping combos --- mysql-test/r/ctype_latin1_de.result | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index 630fef9b679..28394d9533a 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -196,6 +196,9 @@ strcmp(' select strcmp('u','öa'),strcmp('u','ö'); strcmp('u','öa') strcmp('u','ö') 1 1 +select strcmp('sä', 'ßa'), strcmp('aä', 'äx'); +strcmp('sä', 'ßa') strcmp('aä', 'äx') +-1 -1 create table t1 (a varchar(10), key(a), fulltext (a)); insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); select * from t1 where a like "abc%"; -- cgit v1.2.1 From 06517dfed3505981105d892e036e36d32c23c9aa Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 18 Jul 2003 04:04:24 +0300 Subject: Fixed memory overrun when doing REPAIR on table with multi-part auto_increment key where one part was a packed CHAR myisam/mi_check.c: Fixed memory overrun in _mi_put_key_in_record myisam/mi_key.c: Fixed unnecessary memory allocation mysql-test/r/insert_select.result: Added test case for memory corruption mysql-test/t/insert_select.test: Added test case for memory corruption mysys/safemalloc.c: Removed compiler warnings sql/sql_acl.cc: Fixed core dump when running with --debug --- mysql-test/r/insert_select.result | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index c007191b562..0142ef68993 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -587,3 +587,8 @@ a b 3 t2:3 2 t2:2 drop table t1,t2; +CREATE TABLE t1 ( USID INTEGER UNSIGNED, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User CHAR(32) NOT NULL DEFAULT '', NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL); +CREATE TABLE t2 ( USID INTEGER UNSIGNED AUTO_INCREMENT, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User TEXT NOT NULL, NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL, INDEX(USID,ServerID,NASAddr,SessionID), INDEX(AssignedAddr)); +INSERT INTO t1 VALUES (39,42,'Access-Granted','46','491721000045',2130706433,17690,NULL,NULL,'Localnet','491721000045','49172200000',754974766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-07-18 00:11:21',NULL,NULL,20030718001121); +INSERT INTO t2 SELECT USID, ServerID, State, SessionID, User, NASAddr, NASPort, NASPortType, ConnectSpeed, CarrierType, CallingStationID, CalledStationID, AssignedAddr, SessionTime, PacketsIn, OctetsIn, PacketsOut, OctetsOut, TerminateCause, UnauthTime, AccessRequestTime, AcctStartTime, AcctLastTime, LastModification from t1 LIMIT 1; +drop table t1,t2; -- cgit v1.2.1 From 2763b52d0a0ad85818f5c8259b4dd1d060e568e7 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 25 Jul 2003 14:32:03 +0200 Subject: outer join, impossible on condition, where, and usable key for range bug#926 --- mysql-test/r/select.result | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 811c396ea67..bde527b9b44 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3508,3 +3508,23 @@ aa id t2_id id 5 8303 2520 2520 6 8304 2521 2521 drop table t1,t2; +create table t1 (id1 int NOT NULL); +create table t2 (id2 int NOT NULL); +create table t3 (id3 int NOT NULL); +create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4)); +insert into t1 values (1); +insert into t1 values (2); +insert into t2 values (1); +insert into t4 values (1,1); +explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 +left join t4 on id3 = id4 where id2 = 1 or id4 = 1; +table type possible_keys key key_len ref rows Extra +t3 system NULL NULL NULL NULL 0 const row not found +t1 ALL NULL NULL NULL NULL 2 +t2 ALL NULL NULL NULL NULL 1 +t4 ALL id4 NULL NULL NULL 1 Using where +select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 +left join t4 on id3 = id4 where id2 = 1 or id4 = 1; +id1 id2 id3 id4 id44 +1 1 NULL NULL NULL +drop table t1,t2,t3,t4; -- cgit v1.2.1 From f3a847eceb41fe4a9ebf765a27a13269f6016f09 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 26 Jul 2003 17:59:05 +0200 Subject: Make rpl_insert_id.test use InnoDB, and added foreign keys to the table, so that it really tests replication of SET FOREIGN_KEY_CHECKS (previously it used MyISAM). mysql-test/r/rpl_insert_id.result: result update mysql-test/t/rpl_insert_id.test: Make test use InnoDB, and added foreign keys to the table, so that it really tests replication of FOREIGN_KEY_CHECKS. --disable_warnings because 4.1 prints a warning when the table type is not available. --- mysql-test/r/rpl_insert_id.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index 0065d83f169..889e7891770 100644 --- a/mysql-test/r/rpl_insert_id.result +++ b/mysql-test/r/rpl_insert_id.result @@ -21,8 +21,8 @@ b c 1 4 drop table t1; drop table t2; -create table t1(a int auto_increment, key(a)); -create table t2(b int auto_increment, c int, key(b)); +create table t1(a int auto_increment, key(a)) type=innodb; +create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) type=innodb; SET FOREIGN_KEY_CHECKS=0; insert into t1 values (10); insert into t1 values (null),(null),(null); -- cgit v1.2.1 From 9b9a914c5dd75834b43b049df6a32975b2b9d4d8 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 30 Jul 2003 14:59:56 +0300 Subject: support of more then 255 tables dependence in query cache (fixed BUG#930) mysql-test/r/query_cache.result: test of many merged tales mysql-test/t/query_cache.test: test of many merged tales sql/sql_cache.h: query with merged tables can depend of more then 255 tables --- mysql-test/r/query_cache.result | 535 +++++++++++++++++++++++++++++++++++++++- 1 file changed, 534 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 0ef0cab423b..e1fd5443580 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -2,7 +2,7 @@ flush query cache; flush query cache; reset query cache; flush status; -drop table if exists t1,t2,t3,t11,t21, mysqltest.t1; +drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t00,mysqltest.t1; drop database if exists mysqltest; create table t1 (a int not null); insert into t1 values (1),(2),(3); @@ -640,3 +640,536 @@ Variable_name Value Qcache_queries_in_cache 2 SET OPTION SQL_SELECT_LIMIT=DEFAULT; drop table t1; +flush status; +create table t0(a int); +create table t1(a int); +create table t2(a int); +create table t3(a int); +create table t4(a int); +create table t5(a int); +create table t6(a int); +create table t7(a int); +create table t8(a int); +create table t9(a int); +create table t10(a int); +create table t11(a int); +create table t12(a int); +create table t13(a int); +create table t14(a int); +create table t15(a int); +create table t16(a int); +create table t17(a int); +create table t18(a int); +create table t19(a int); +create table t20(a int); +create table t21(a int); +create table t22(a int); +create table t23(a int); +create table t24(a int); +create table t25(a int); +create table t26(a int); +create table t27(a int); +create table t28(a int); +create table t29(a int); +create table t30(a int); +create table t31(a int); +create table t32(a int); +create table t33(a int); +create table t34(a int); +create table t35(a int); +create table t36(a int); +create table t37(a int); +create table t38(a int); +create table t39(a int); +create table t40(a int); +create table t41(a int); +create table t42(a int); +create table t43(a int); +create table t44(a int); +create table t45(a int); +create table t46(a int); +create table t47(a int); +create table t48(a int); +create table t49(a int); +create table t50(a int); +create table t51(a int); +create table t52(a int); +create table t53(a int); +create table t54(a int); +create table t55(a int); +create table t56(a int); +create table t57(a int); +create table t58(a int); +create table t59(a int); +create table t60(a int); +create table t61(a int); +create table t62(a int); +create table t63(a int); +create table t64(a int); +create table t65(a int); +create table t66(a int); +create table t67(a int); +create table t68(a int); +create table t69(a int); +create table t70(a int); +create table t71(a int); +create table t72(a int); +create table t73(a int); +create table t74(a int); +create table t75(a int); +create table t76(a int); +create table t77(a int); +create table t78(a int); +create table t79(a int); +create table t80(a int); +create table t81(a int); +create table t82(a int); +create table t83(a int); +create table t84(a int); +create table t85(a int); +create table t86(a int); +create table t87(a int); +create table t88(a int); +create table t89(a int); +create table t90(a int); +create table t91(a int); +create table t92(a int); +create table t93(a int); +create table t94(a int); +create table t95(a int); +create table t96(a int); +create table t97(a int); +create table t98(a int); +create table t99(a int); +create table t100(a int); +create table t101(a int); +create table t102(a int); +create table t103(a int); +create table t104(a int); +create table t105(a int); +create table t106(a int); +create table t107(a int); +create table t108(a int); +create table t109(a int); +create table t110(a int); +create table t111(a int); +create table t112(a int); +create table t113(a int); +create table t114(a int); +create table t115(a int); +create table t116(a int); +create table t117(a int); +create table t118(a int); +create table t119(a int); +create table t120(a int); +create table t121(a int); +create table t122(a int); +create table t123(a int); +create table t124(a int); +create table t125(a int); +create table t126(a int); +create table t127(a int); +create table t128(a int); +create table t129(a int); +create table t130(a int); +create table t131(a int); +create table t132(a int); +create table t133(a int); +create table t134(a int); +create table t135(a int); +create table t136(a int); +create table t137(a int); +create table t138(a int); +create table t139(a int); +create table t140(a int); +create table t141(a int); +create table t142(a int); +create table t143(a int); +create table t144(a int); +create table t145(a int); +create table t146(a int); +create table t147(a int); +create table t148(a int); +create table t149(a int); +create table t150(a int); +create table t151(a int); +create table t152(a int); +create table t153(a int); +create table t154(a int); +create table t155(a int); +create table t156(a int); +create table t157(a int); +create table t158(a int); +create table t159(a int); +create table t160(a int); +create table t161(a int); +create table t162(a int); +create table t163(a int); +create table t164(a int); +create table t165(a int); +create table t166(a int); +create table t167(a int); +create table t168(a int); +create table t169(a int); +create table t170(a int); +create table t171(a int); +create table t172(a int); +create table t173(a int); +create table t174(a int); +create table t175(a int); +create table t176(a int); +create table t177(a int); +create table t178(a int); +create table t179(a int); +create table t180(a int); +create table t181(a int); +create table t182(a int); +create table t183(a int); +create table t184(a int); +create table t185(a int); +create table t186(a int); +create table t187(a int); +create table t188(a int); +create table t189(a int); +create table t190(a int); +create table t191(a int); +create table t192(a int); +create table t193(a int); +create table t194(a int); +create table t195(a int); +create table t196(a int); +create table t197(a int); +create table t198(a int); +create table t199(a int); +create table t200(a int); +create table t201(a int); +create table t202(a int); +create table t203(a int); +create table t204(a int); +create table t205(a int); +create table t206(a int); +create table t207(a int); +create table t208(a int); +create table t209(a int); +create table t210(a int); +create table t211(a int); +create table t212(a int); +create table t213(a int); +create table t214(a int); +create table t215(a int); +create table t216(a int); +create table t217(a int); +create table t218(a int); +create table t219(a int); +create table t220(a int); +create table t221(a int); +create table t222(a int); +create table t223(a int); +create table t224(a int); +create table t225(a int); +create table t226(a int); +create table t227(a int); +create table t228(a int); +create table t229(a int); +create table t230(a int); +create table t231(a int); +create table t232(a int); +create table t233(a int); +create table t234(a int); +create table t235(a int); +create table t236(a int); +create table t237(a int); +create table t238(a int); +create table t239(a int); +create table t240(a int); +create table t241(a int); +create table t242(a int); +create table t243(a int); +create table t244(a int); +create table t245(a int); +create table t246(a int); +create table t247(a int); +create table t248(a int); +create table t249(a int); +create table t250(a int); +create table t251(a int); +create table t252(a int); +create table t253(a int); +create table t254(a int); +create table t255(a int); +create table t256(a int); +create table t00 (a int) type=MERGE UNION=(t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256) INSERT_METHOD=FIRST; +insert into t0 values (1),(2); +insert into t1 values (1),(2); +insert into t2 values (1),(2); +insert into t3 values (1),(2); +insert into t4 values (1),(2); +insert into t5 values (1),(2); +insert into t6 values (1),(2); +insert into t7 values (1),(2); +insert into t8 values (1),(2); +insert into t9 values (1),(2); +insert into t10 values (1),(2); +insert into t11 values (1),(2); +insert into t12 values (1),(2); +insert into t13 values (1),(2); +insert into t14 values (1),(2); +insert into t15 values (1),(2); +insert into t16 values (1),(2); +insert into t17 values (1),(2); +insert into t18 values (1),(2); +insert into t19 values (1),(2); +insert into t20 values (1),(2); +insert into t21 values (1),(2); +insert into t22 values (1),(2); +insert into t23 values (1),(2); +insert into t24 values (1),(2); +insert into t25 values (1),(2); +insert into t26 values (1),(2); +insert into t27 values (1),(2); +insert into t28 values (1),(2); +insert into t29 values (1),(2); +insert into t30 values (1),(2); +insert into t31 values (1),(2); +insert into t32 values (1),(2); +insert into t33 values (1),(2); +insert into t34 values (1),(2); +insert into t35 values (1),(2); +insert into t36 values (1),(2); +insert into t37 values (1),(2); +insert into t38 values (1),(2); +insert into t39 values (1),(2); +insert into t40 values (1),(2); +insert into t41 values (1),(2); +insert into t42 values (1),(2); +insert into t43 values (1),(2); +insert into t44 values (1),(2); +insert into t45 values (1),(2); +insert into t46 values (1),(2); +insert into t47 values (1),(2); +insert into t48 values (1),(2); +insert into t49 values (1),(2); +insert into t50 values (1),(2); +insert into t51 values (1),(2); +insert into t52 values (1),(2); +insert into t53 values (1),(2); +insert into t54 values (1),(2); +insert into t55 values (1),(2); +insert into t56 values (1),(2); +insert into t57 values (1),(2); +insert into t58 values (1),(2); +insert into t59 values (1),(2); +insert into t60 values (1),(2); +insert into t61 values (1),(2); +insert into t62 values (1),(2); +insert into t63 values (1),(2); +insert into t64 values (1),(2); +insert into t65 values (1),(2); +insert into t66 values (1),(2); +insert into t67 values (1),(2); +insert into t68 values (1),(2); +insert into t69 values (1),(2); +insert into t70 values (1),(2); +insert into t71 values (1),(2); +insert into t72 values (1),(2); +insert into t73 values (1),(2); +insert into t74 values (1),(2); +insert into t75 values (1),(2); +insert into t76 values (1),(2); +insert into t77 values (1),(2); +insert into t78 values (1),(2); +insert into t79 values (1),(2); +insert into t80 values (1),(2); +insert into t81 values (1),(2); +insert into t82 values (1),(2); +insert into t83 values (1),(2); +insert into t84 values (1),(2); +insert into t85 values (1),(2); +insert into t86 values (1),(2); +insert into t87 values (1),(2); +insert into t88 values (1),(2); +insert into t89 values (1),(2); +insert into t90 values (1),(2); +insert into t91 values (1),(2); +insert into t92 values (1),(2); +insert into t93 values (1),(2); +insert into t94 values (1),(2); +insert into t95 values (1),(2); +insert into t96 values (1),(2); +insert into t97 values (1),(2); +insert into t98 values (1),(2); +insert into t99 values (1),(2); +insert into t100 values (1),(2); +insert into t101 values (1),(2); +insert into t102 values (1),(2); +insert into t103 values (1),(2); +insert into t104 values (1),(2); +insert into t105 values (1),(2); +insert into t106 values (1),(2); +insert into t107 values (1),(2); +insert into t108 values (1),(2); +insert into t109 values (1),(2); +insert into t110 values (1),(2); +insert into t111 values (1),(2); +insert into t112 values (1),(2); +insert into t113 values (1),(2); +insert into t114 values (1),(2); +insert into t115 values (1),(2); +insert into t116 values (1),(2); +insert into t117 values (1),(2); +insert into t118 values (1),(2); +insert into t119 values (1),(2); +insert into t120 values (1),(2); +insert into t121 values (1),(2); +insert into t122 values (1),(2); +insert into t123 values (1),(2); +insert into t124 values (1),(2); +insert into t125 values (1),(2); +insert into t126 values (1),(2); +insert into t127 values (1),(2); +insert into t128 values (1),(2); +insert into t129 values (1),(2); +insert into t130 values (1),(2); +insert into t131 values (1),(2); +insert into t132 values (1),(2); +insert into t133 values (1),(2); +insert into t134 values (1),(2); +insert into t135 values (1),(2); +insert into t136 values (1),(2); +insert into t137 values (1),(2); +insert into t138 values (1),(2); +insert into t139 values (1),(2); +insert into t140 values (1),(2); +insert into t141 values (1),(2); +insert into t142 values (1),(2); +insert into t143 values (1),(2); +insert into t144 values (1),(2); +insert into t145 values (1),(2); +insert into t146 values (1),(2); +insert into t147 values (1),(2); +insert into t148 values (1),(2); +insert into t149 values (1),(2); +insert into t150 values (1),(2); +insert into t151 values (1),(2); +insert into t152 values (1),(2); +insert into t153 values (1),(2); +insert into t154 values (1),(2); +insert into t155 values (1),(2); +insert into t156 values (1),(2); +insert into t157 values (1),(2); +insert into t158 values (1),(2); +insert into t159 values (1),(2); +insert into t160 values (1),(2); +insert into t161 values (1),(2); +insert into t162 values (1),(2); +insert into t163 values (1),(2); +insert into t164 values (1),(2); +insert into t165 values (1),(2); +insert into t166 values (1),(2); +insert into t167 values (1),(2); +insert into t168 values (1),(2); +insert into t169 values (1),(2); +insert into t170 values (1),(2); +insert into t171 values (1),(2); +insert into t172 values (1),(2); +insert into t173 values (1),(2); +insert into t174 values (1),(2); +insert into t175 values (1),(2); +insert into t176 values (1),(2); +insert into t177 values (1),(2); +insert into t178 values (1),(2); +insert into t179 values (1),(2); +insert into t180 values (1),(2); +insert into t181 values (1),(2); +insert into t182 values (1),(2); +insert into t183 values (1),(2); +insert into t184 values (1),(2); +insert into t185 values (1),(2); +insert into t186 values (1),(2); +insert into t187 values (1),(2); +insert into t188 values (1),(2); +insert into t189 values (1),(2); +insert into t190 values (1),(2); +insert into t191 values (1),(2); +insert into t192 values (1),(2); +insert into t193 values (1),(2); +insert into t194 values (1),(2); +insert into t195 values (1),(2); +insert into t196 values (1),(2); +insert into t197 values (1),(2); +insert into t198 values (1),(2); +insert into t199 values (1),(2); +insert into t200 values (1),(2); +insert into t201 values (1),(2); +insert into t202 values (1),(2); +insert into t203 values (1),(2); +insert into t204 values (1),(2); +insert into t205 values (1),(2); +insert into t206 values (1),(2); +insert into t207 values (1),(2); +insert into t208 values (1),(2); +insert into t209 values (1),(2); +insert into t210 values (1),(2); +insert into t211 values (1),(2); +insert into t212 values (1),(2); +insert into t213 values (1),(2); +insert into t214 values (1),(2); +insert into t215 values (1),(2); +insert into t216 values (1),(2); +insert into t217 values (1),(2); +insert into t218 values (1),(2); +insert into t219 values (1),(2); +insert into t220 values (1),(2); +insert into t221 values (1),(2); +insert into t222 values (1),(2); +insert into t223 values (1),(2); +insert into t224 values (1),(2); +insert into t225 values (1),(2); +insert into t226 values (1),(2); +insert into t227 values (1),(2); +insert into t228 values (1),(2); +insert into t229 values (1),(2); +insert into t230 values (1),(2); +insert into t231 values (1),(2); +insert into t232 values (1),(2); +insert into t233 values (1),(2); +insert into t234 values (1),(2); +insert into t235 values (1),(2); +insert into t236 values (1),(2); +insert into t237 values (1),(2); +insert into t238 values (1),(2); +insert into t239 values (1),(2); +insert into t240 values (1),(2); +insert into t241 values (1),(2); +insert into t242 values (1),(2); +insert into t243 values (1),(2); +insert into t244 values (1),(2); +insert into t245 values (1),(2); +insert into t246 values (1),(2); +insert into t247 values (1),(2); +insert into t248 values (1),(2); +insert into t249 values (1),(2); +insert into t250 values (1),(2); +insert into t251 values (1),(2); +insert into t252 values (1),(2); +insert into t253 values (1),(2); +insert into t254 values (1),(2); +insert into t255 values (1),(2); +insert into t256 values (1),(2); +select count(*) from t00; +count(*) +514 +select count(*) from t00; +count(*) +514 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 1 +delete from t256; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +drop table t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t00; -- cgit v1.2.1 From 687ebba461075af77544c3bf1d268148fc3b2c90 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 31 Jul 2003 13:45:35 +0200 Subject: bug #942. docid == ftb->lastpos in join on looping over nested table --- mysql-test/r/fulltext.result | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 646c1a7bee2..737390865f1 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -256,3 +256,14 @@ select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); ref_mag test drop table t1; +create table t1 (t1_id int(11) primary key, name varchar(32)); +insert into t1 values (1, 'data1'); +insert into t1 values (2, 'data2'); +create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32)); +insert into t2 values (1, 1, 'xxfoo'); +insert into t2 values (2, 1, 'xxbar'); +insert into t2 values (3, 1, 'xxbuz'); +select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); +t1_id name t2_id t1_id name +1 data1 1 1 xxfoo +drop table t1,t2; -- cgit v1.2.1 From 3b013646e1550d22dff01dd02c8d12118a563fe2 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 2 Aug 2003 23:46:26 +0200 Subject: Fix so that SET PASSWORD is not replicated by the slave if running with replicate-*-table rules which exclude 'mysql' tables (e.g. replicate-wild-ignore-table=mysql.%). This was already the behaviour for GRANT/REVOKE, I'm extending it to SET PASSWORD because it seems very logical (the contrary seems illogical). 2 new tests: - one to test if GRANT and SET PASSWORD are replicated - one to test if they are not replicated if replicate-wild-ignore-table=mysql.% The 2nd is also a testcase for BUG#980. sql/sql_acl.cc: Fix so that SET PASSWORD is not replicated by the slave if running with replicate-*-table rules which exclude 'mysql' tables (e.g. replicate-wild-ignore-table=mysql.%). This was already the behaviour for GRANT/REVOKE, I'm extending it to SET PASSWORD because it seems very logical (the contrary seems illogical). --- mysql-test/r/rpl_do_grant.result | 26 +++++++++++++++++++++++++ mysql-test/r/rpl_ignore_grant.result | 37 ++++++++++++++++++++++++++++++++++++ 2 files changed, 63 insertions(+) create mode 100644 mysql-test/r/rpl_do_grant.result create mode 100644 mysql-test/r/rpl_ignore_grant.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_do_grant.result b/mysql-test/r/rpl_do_grant.result new file mode 100644 index 00000000000..fec935ae7ac --- /dev/null +++ b/mysql-test/r/rpl_do_grant.result @@ -0,0 +1,26 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +delete from mysql.user where user='rpl_do_grant'; +delete from mysql.db where user='rpl_do_grant'; +flush privileges; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; +grant select on *.* to rpl_do_grant@localhost; +grant drop on test.* to rpl_do_grant@localhost; +show grants for rpl_do_grant@localhost; +Grants for rpl_do_grant@localhost +GRANT SELECT ON *.* TO 'rpl_do_grant'@'localhost' +GRANT DROP ON `test`.* TO 'rpl_do_grant'@'localhost' +set password for rpl_do_grant@localhost=password("does it work?"); +select password<>'' from mysql.user where user='rpl_do_grant'; +password<>'' +1 +delete from mysql.user where user='rpl_do_grant'; +delete from mysql.db where user='rpl_do_grant'; +flush privileges; +flush privileges; diff --git a/mysql-test/r/rpl_ignore_grant.result b/mysql-test/r/rpl_ignore_grant.result new file mode 100644 index 00000000000..6cd7d5b4c00 --- /dev/null +++ b/mysql-test/r/rpl_ignore_grant.result @@ -0,0 +1,37 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; +grant select on *.* to rpl_ignore_grant@localhost; +grant drop on test.* to rpl_ignore_grant@localhost; +show grants for rpl_ignore_grant@localhost; +Grants for rpl_ignore_grant@localhost +GRANT SELECT ON *.* TO 'rpl_ignore_grant'@'localhost' +GRANT DROP ON `test`.* TO 'rpl_ignore_grant'@'localhost' +show grants for rpl_ignore_grant@localhost; +There is no such grant defined for user 'rpl_ignore_grant' on host 'localhost' +select count(*) from mysql.user where user='rpl_ignore_grant'; +count(*) +0 +select count(*) from mysql.db where user='rpl_ignore_grant'; +count(*) +0 +grant select on *.* to rpl_ignore_grant@localhost; +set password for rpl_ignore_grant@localhost=password("does it work?"); +select password<>'' from mysql.user where user='rpl_ignore_grant'; +password<>'' +0 +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; +delete from mysql.user where user='rpl_ignore_grant'; +delete from mysql.db where user='rpl_ignore_grant'; +flush privileges; -- cgit v1.2.1 From d8df84aa438a2dfe4a90d7889713c2ccd52cca1d Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 4 Aug 2003 10:59:44 +0200 Subject: 2 bugfixes: - Bug #985: "Between RESET SLAVE and START SLAVE, SHOW SLAVE STATUS is wrong." Now RESET SLAVE puts correct info in mi->host etc. A new test rpl_reset_slave for that. - Bug #986: "CHANGE MASTER & START SLAVE do not reset error columns in SHOW SLAVE STATUS". Now these reset the errors. mysql-test/r/rpl_loaddata.result: result update. mysql-test/t/rpl_loaddata.test: Test that RESET SLAVE, START SLAVE and CHANGE MASTER all reset Last_slave_error and Last_slave_errno (columns of SHOW SLAVE STATUS). We do it in this test because that's one of tests which have an intentional query error on the slave. sql/slave.cc: As we need TWICE the code to copy command-line options (--master-host etc) to mi (we already had it in init_master_info, but we also need it in RESET SLAVE to fix bug#985), I make a function of this code. And a function to reset Last_slave_error and Last_slave_errno (we need it in CHANGE MASTER, RESET SLAVE, and at the start of the SQL thread). sql/slave.h: declarations for new functions. sql/sql_repl.cc: copy --master-host etc to mi in RESET SLAVE, so that SHOW SLAVE STATUS shows correct information. --- mysql-test/r/rpl_loaddata.result | 26 ++++++++++++++++++++++++++ mysql-test/r/rpl_reset_slave.result | 22 ++++++++++++++++++++++ 2 files changed, 48 insertions(+) create mode 100644 mysql-test/r/rpl_reset_slave.result (limited to 'mysql-test/r') diff --git a/mysql-test/r/rpl_loaddata.result b/mysql-test/r/rpl_loaddata.result index b5154ca95cf..05bdad0fb84 100644 --- a/mysql-test/r/rpl_loaddata.result +++ b/mysql-test/r/rpl_loaddata.result @@ -25,3 +25,29 @@ drop table t3; create table t1(a int, b int, unique(b)); insert into t1 values(1,10); load data infile '../../std_data/rpl_loaddata.dat' into table t1; +set global sql_slave_skip_counter=1; +start slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 1311 slave-relay-bin.002 1352 master-bin.001 Yes Yes 0 0 1311 1352 +set sql_log_bin=0; +delete from t1; +set sql_log_bin=1; +load data infile '../../std_data/rpl_loaddata.dat' into table t1; +stop slave; +change master to master_user='test'; +change master to master_user='root'; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 1442 slave-relay-bin.001 4 master-bin.001 No No 0 0 1442 4 +set global sql_slave_skip_counter=1; +start slave; +set sql_log_bin=0; +delete from t1; +set sql_log_bin=1; +load data infile '../../std_data/rpl_loaddata.dat' into table t1; +stop slave; +reset slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 4 slave-relay-bin.001 4 No No 0 0 0 4 diff --git a/mysql-test/r/rpl_reset_slave.result b/mysql-test/r/rpl_reset_slave.result new file mode 100644 index 00000000000..c1bc1e8e483 --- /dev/null +++ b/mysql-test/r/rpl_reset_slave.result @@ -0,0 +1,22 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 79 slave-relay-bin.002 120 master-bin.001 Yes Yes 0 0 79 120 +stop slave; +change master to master_user='test'; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 test MASTER_PORT 1 master-bin.001 79 slave-relay-bin.001 4 master-bin.001 No No 0 0 79 4 +reset slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 4 slave-relay-bin.001 4 No No 0 0 0 4 +start slave; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root MASTER_PORT 1 master-bin.001 79 slave-relay-bin.002 120 master-bin.001 Yes Yes 0 0 79 120 -- cgit v1.2.1 From 0f314c9da3803367d34633a51dd2dbf3f0bd10a0 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 5 Aug 2003 16:29:09 +0200 Subject: make it clear for optimizer that XOR's are not optimizable at the moment (BUG#992) --- mysql-test/r/func_test.result | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index 8cfae44b9dd..e1c27daa9a7 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -46,6 +46,11 @@ select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1; select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL; 1 XOR 1 1 XOR 0 0 XOR 1 0 XOR 0 NULL XOR 1 1 XOR NULL 0 XOR NULL 0 1 1 0 NULL NULL NULL +create table t1 (a int); +insert t1 values (1); +select * from t1 where 1 xor 1; +a +drop table t1; select 5 between 0 and 10 between 0 and 1,(5 between 0 and 10) between 0 and 1; 5 between 0 and 10 between 0 and 1 (5 between 0 and 10) between 0 and 1 0 1 -- cgit v1.2.1 From e50930d36c33c4457d9ba6d68e5f6b2c6e622f11 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 6 Aug 2003 15:37:22 +0200 Subject: Bug #792 combination of date-interval and between with mixed constant and non-constant --- mysql-test/r/type_date.result | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index 214328eab47..8dfe14bc1a2 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -32,6 +32,8 @@ datum 2000-01-02 2000-01-03 2000-01-04 +SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND datum - INTERVAL 100 DAY; +datum DROP TABLE t1; CREATE TABLE t1 ( user_id char(10), -- cgit v1.2.1