From 1731161ff472a3e39f3923e8648c37e163f25f74 Mon Sep 17 00:00:00 2001 From: "monty@tik.mysql.fi" <> Date: Mon, 30 Jul 2001 22:49:39 +0300 Subject: Fixed optimizer bug in ORDER BY where some ORDER BY parts where ignored. Extended default arguments to be up to 4095 characters --- mysql-test/r/order_by.result | 31 ++++++++++++++++++++++++++ mysql-test/t/order_by.test | 52 +++++++++++++++++++++++++++++++++++++++----- 2 files changed, 78 insertions(+), 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 74c8bd53af2..1a292b1203c 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -111,3 +111,34 @@ DateOfAction TransactionID member_id nickname voornaam 1 2 +gid sid uid +104620 5 15 +103867 5 27 +103962 5 27 +104619 5 75 +104505 5 117 +103853 5 250 +gid sid uid +104620 5 15 +103867 5 27 +103962 5 27 +104619 5 75 +104505 5 117 +103853 5 250 +table type possible_keys key key_len ref rows Extra +t1 index PRIMARY PRIMARY 4 NULL 6 Using index +t2 eq_ref PRIMARY,uid PRIMARY 4 t1.gid 1 +t3 eq_ref PRIMARY PRIMARY 2 t2.uid 1 where used; Using index +table type possible_keys key key_len ref rows Extra +t1 index PRIMARY PRIMARY 4 NULL 6 Using index +t3 eq_ref PRIMARY PRIMARY 2 t1.gid 1 where used +table type possible_keys key key_len ref rows Extra +t1 index PRIMARY PRIMARY 4 NULL 6 Using index; Using temporary; Using filesort +t2 eq_ref PRIMARY,uid PRIMARY 4 t1.gid 1 +t3 eq_ref PRIMARY PRIMARY 2 t2.uid 1 where used; Using index +table type possible_keys key key_len ref rows Extra +t1 index PRIMARY PRIMARY 4 NULL 6 Using index; Using temporary; Using filesort +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 diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 4e5cee0d0ff..16094206745 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -168,8 +168,8 @@ drop table t1,t2,t3; #bug reported by Wouter de Jong -drop table if exists members; -CREATE TABLE members ( +drop table if exists t1; +CREATE TABLE t1 ( member_id int(11) NOT NULL auto_increment, inschrijf_datum varchar(20) NOT NULL default '', lastchange_datum varchar(20) NOT NULL default '', @@ -200,8 +200,50 @@ CREATE TABLE members ( PRIMARY KEY (member_id) ) TYPE=MyISAM PACK_KEYS=1; -insert into members (member_id) values (1),(2),(3); -select member_id, nickname, voornaam FROM members +insert into t1 (member_id) values (1),(2),(3); +select member_id, nickname, voornaam FROM t1 ORDER by lastchange_datum DESC LIMIT 2; -drop table members; +drop table t1; + +# +# Test optimizing bug with EQ_REF tables, where some ORDER BY parts where +# wrongly removed. +CREATE TABLE t1 ( + gid int(10) unsigned NOT NULL auto_increment, + cid smallint(5) unsigned NOT NULL default '0', + PRIMARY KEY (gid), + KEY component_id (cid) +) TYPE=MyISAM; +INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108); +ALTER TABLE t1 add skr int(10) not null; + +CREATE TABLE t2 ( + gid int(10) unsigned NOT NULL default '0', + uid smallint(5) unsigned NOT NULL default '1', + sid tinyint(3) unsigned NOT NULL default '1', + PRIMARY KEY (gid), + KEY uid (uid), + KEY status_id (sid) +) TYPE=MyISAM; +INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5); + +CREATE TABLE t3 ( + uid smallint(6) NOT NULL auto_increment, + PRIMARY KEY (uid) +) TYPE=MyISAM; +INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250); +ALTER TABLE t3 add skr int(10) not null; + +select t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid; +select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid; + +# The following ORDER BY can be optimimized +EXPLAIN select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid; +EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr; + +# The following ORDER BY can't be optimimized +EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid; +EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid; +EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr; +drop table t1,t2,t3; -- cgit v1.2.1 From d31b49f32876be60bbfb9d161197e7f278ee2685 Mon Sep 17 00:00:00 2001 From: "sasha@mysql.sashanet.com" <> Date: Mon, 30 Jul 2001 17:43:58 -0600 Subject: added test case for fulltext join bug --- mysql-test/t/fulltext_join.test | 38 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 38 insertions(+) create mode 100644 mysql-test/t/fulltext_join.test (limited to 'mysql-test') diff --git a/mysql-test/t/fulltext_join.test b/mysql-test/t/fulltext_join.test new file mode 100644 index 00000000000..c5e6037180a --- /dev/null +++ b/mysql-test/t/fulltext_join.test @@ -0,0 +1,38 @@ +DROP TABLE IF EXISTS stories; +CREATE TABLE stories ( + sid char(16) NOT NULL, + tid smallint UNSIGNED NOT NULL, + uid mediumint UNSIGNED NOT NULL, + title varchar(100) DEFAULT '' NOT NULL, + dept varchar(100), + time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, + hits mediumint UNSIGNED DEFAULT '0' NOT NULL, + section varchar(30) DEFAULT '' NOT NULL, + displaystatus tinyint DEFAULT '0' NOT NULL, + commentstatus tinyint, + discussion mediumint UNSIGNED, + submitter mediumint UNSIGNED NOT NULL, + flags set("delete_me","data_dirty") DEFAULT '' NOT NULL, + PRIMARY KEY (sid), + FOREIGN KEY (uid) REFERENCES users(uid), + FOREIGN KEY (tid) REFERENCES tid(topic), + FOREIGN KEY (section) REFERENCES sections(section), + KEY time (time), + KEY searchform (displaystatus,time) +) TYPE = myisam; +DROP TABLE IF EXISTS story_text; +CREATE TABLE story_text ( + sid char(16) NOT NULL, + introtext text, + bodytext text, + relatedtext text, + FOREIGN KEY (sid) REFERENCES stories(sid), + PRIMARY KEY (sid) +) TYPE = myisam; +ALTER TABLE stories add fulltext (title); +ALTER TABLE story_text add fulltext (introtext,bodytext); + +SELECT stories.sid,title, TRUNCATE((MATCH (title,introtext,bodytext) +AGAINST('install')), 1) as score FROM stories,story_text WHERE +stories.sid = story_text.sid AND MATCH (title,introtext,bodytext) +AGAINST ('install'); -- cgit v1.2.1 From 0d55e936143781df740f815a0e21707c1f1932e7 Mon Sep 17 00:00:00 2001 From: "monty@tik.mysql.fi" <> Date: Sat, 4 Aug 2001 00:10:00 +0300 Subject: Fix when using --without-server --with-thread-safe-client Added prototype for my_thread_end New error message for MATCH Give error when using myisamchk --force --readonly --- mysql-test/t/fulltext.test | 23 +++++++++++++++++++++-- mysql-test/t/fulltext_join.test | 38 -------------------------------------- 2 files changed, 21 insertions(+), 40 deletions(-) delete mode 100644 mysql-test/t/fulltext_join.test (limited to 'mysql-test') diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 064219c6ad3..153fdefd960 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -2,7 +2,7 @@ # Test of fulltext index # -drop table if exists t1,t2; +drop table if exists t1,t2,t3; CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),('Full-text indexes', 'are called collections'),('Only MyISAM tables','support collections'),('Function MATCH ... AGAINST()','is used to do a search'),('Full-text search in MySQL', 'implements vector space model'); @@ -61,4 +61,23 @@ select * from t2 where MATCH inhalt AGAINST (NULL); select * from t2 where MATCH inhalt AGAINST ('foobar'); select * from t2 having MATCH inhalt AGAINST ('foobar'); -drop table t1,t2; +# +# check of fulltext errors +# + +CREATE TABLE t3 ( + ticket int(11), + inhalt text, + KEY tig (ticket), + fulltext index tix (inhalt) +); + +--error 1210 +select * from t2 having MATCH inhalt AGAINST (t1.id); +--error 1210 +select * from t2 having MATCH ticket AGAINST ('foobar'); +--error 1210 +select * from t2,t3 having MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar'); + +drop table t1,t2,t3; + diff --git a/mysql-test/t/fulltext_join.test b/mysql-test/t/fulltext_join.test deleted file mode 100644 index c5e6037180a..00000000000 --- a/mysql-test/t/fulltext_join.test +++ /dev/null @@ -1,38 +0,0 @@ -DROP TABLE IF EXISTS stories; -CREATE TABLE stories ( - sid char(16) NOT NULL, - tid smallint UNSIGNED NOT NULL, - uid mediumint UNSIGNED NOT NULL, - title varchar(100) DEFAULT '' NOT NULL, - dept varchar(100), - time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, - hits mediumint UNSIGNED DEFAULT '0' NOT NULL, - section varchar(30) DEFAULT '' NOT NULL, - displaystatus tinyint DEFAULT '0' NOT NULL, - commentstatus tinyint, - discussion mediumint UNSIGNED, - submitter mediumint UNSIGNED NOT NULL, - flags set("delete_me","data_dirty") DEFAULT '' NOT NULL, - PRIMARY KEY (sid), - FOREIGN KEY (uid) REFERENCES users(uid), - FOREIGN KEY (tid) REFERENCES tid(topic), - FOREIGN KEY (section) REFERENCES sections(section), - KEY time (time), - KEY searchform (displaystatus,time) -) TYPE = myisam; -DROP TABLE IF EXISTS story_text; -CREATE TABLE story_text ( - sid char(16) NOT NULL, - introtext text, - bodytext text, - relatedtext text, - FOREIGN KEY (sid) REFERENCES stories(sid), - PRIMARY KEY (sid) -) TYPE = myisam; -ALTER TABLE stories add fulltext (title); -ALTER TABLE story_text add fulltext (introtext,bodytext); - -SELECT stories.sid,title, TRUNCATE((MATCH (title,introtext,bodytext) -AGAINST('install')), 1) as score FROM stories,story_text WHERE -stories.sid = story_text.sid AND MATCH (title,introtext,bodytext) -AGAINST ('install'); -- cgit v1.2.1