From c47c56452680e1bbdaa9d8ed4203470cf7f02c56 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 31 May 2003 18:31:57 +0300 Subject: Fix for the optimiser problem caused by the fact that with derived tables one (or more tables) is opened / closed twice. --- mysql-test/r/derived.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index bfd4c544131..52a54ac7773 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -137,7 +137,7 @@ a t explain select count(*) from t1 as tt1, (select * from t1) as tt2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -2 DERIVED tt1 index NULL a 4 NULL 10000 Using index +2 DERIVED tt1 ALL NULL NULL NULL NULL 10000 drop table t1; SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b; (SELECT * FROM (SELECT 1 as a) as a ) -- cgit v1.2.1 From 4ac20ad4a925fe9efa4f0937ddeb026cf112857f Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 12 Jun 2003 16:52:36 +0300 Subject: Derived tables bug fix ....... sql/ha_myisam.cc: DISABLE KEYS warnings sql/sql_table.cc: DISABLE KEYS warnings --- mysql-test/r/innodb.result | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 4fb6efe6a14..8409ca59784 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1322,10 +1322,10 @@ SELECT t2.id, t1.label FROM t2 INNER JOIN (SELECT t1.id_object as id_object FROM t1 WHERE t1.label LIKE '%test%') AS lbl ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object); id label -3382 Fournisseur Test -102 Fournisseur Test -1794 Fournisseur Test -1822 Fournisseur Test -3524 Fournisseur Test +3382 Test +102 Le Pekin (Test) +1794 Test de resto +1822 Test 3 +3524 Societe Test 3525 Fournisseur Test drop table t1,t2; -- cgit v1.2.1 From 691b7584fb3bc42b70fe470ba5fa812d28190141 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 5 Aug 2003 10:08:16 +0500 Subject: fix_fields recoursion didn't stop when some level's fix_length_and_dec produced an error. This led to server crash in some cases, e.g.: create table t7 (s1 char); select * from t7 where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA'; --- mysql-test/r/func_str.result | 5 +++++ mysql-test/t/func_str.test | 10 ++++++++++ 2 files changed, 15 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 37694ad2f0a..929ab0535d8 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -553,3 +553,8 @@ SUBSTR('abcdefg',-1,-1) select SUBSTR('abcdefg',1,-1) FROM DUAL; SUBSTR('abcdefg',1,-1) +create table t7 (s1 char); +select * from t7 +where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA'; +ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'concat' +drop table t7; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index f158eaa3764..4c996121446 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -305,3 +305,13 @@ select SUBSTR('abcdefg',-1,5) FROM DUAL; select SUBSTR('abcdefg',0,0) FROM DUAL; select SUBSTR('abcdefg',-1,-1) FROM DUAL; select SUBSTR('abcdefg',1,-1) FROM DUAL; + +# +# Test that fix_fields doesn't follow to upper level (to comparison) +# when an error on a lower level (in concat) has accured: +# +create table t7 (s1 char); +--error 1265 +select * from t7 +where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA'; +drop table t7; -- cgit v1.2.1 From fc8b138544c6edb34a1bc26b8cc8b69630bb81b5 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 5 Aug 2003 11:03:05 +0500 Subject: Fix: MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci); INSERT INTO t1 VALUES ('Ãœ'); INSERT INTO t1 VALUES ('ue'); SELECT DISTINCT s1 FROM t1; The above returned two rows in error. Now it returns one row, in latin1_german2_ci: Ãœ == ue --- mysql-test/r/ctype_latin1_de.result | 15 +++++++++++++++ mysql-test/t/ctype_latin1_de.test | 10 ++++++++++ 2 files changed, 25 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index e0cb7008899..418bd65e0dc 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -264,3 +264,18 @@ select * from t1 where word like CAST(0xDF as CHAR); word word2 ß ß drop table t1; +CREATE TABLE t1 ( +s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci +); +INSERT INTO t1 VALUES ('Ü'); +INSERT INTO t1 VALUES ('ue'); +SELECT DISTINCT s1 FROM t1; +s1 +Ü +SELECT s1,COUNT(*) FROM t1 GROUP BY s1; +s1 COUNT(*) +Ü 2 +SELECT COUNT(DISTINCT s1) FROM t1; +COUNT(DISTINCT s1) +1 +DROP TABLE t1; diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test index 22a4e14158d..0bbd9fa95e4 100644 --- a/mysql-test/t/ctype_latin1_de.test +++ b/mysql-test/t/ctype_latin1_de.test @@ -73,3 +73,13 @@ select * from t1 where word like 'AE'; select * from t1 where word like binary 0xDF; select * from t1 where word like CAST(0xDF as CHAR); drop table t1; + +CREATE TABLE t1 ( +s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci +); +INSERT INTO t1 VALUES ('Ü'); +INSERT INTO t1 VALUES ('ue'); +SELECT DISTINCT s1 FROM t1; +SELECT s1,COUNT(*) FROM t1 GROUP BY s1; +SELECT COUNT(DISTINCT s1) FROM t1; +DROP TABLE t1; -- cgit v1.2.1 From 970f910d757f5a5a8ce0ff31a8d7321da71995e8 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 5 Aug 2003 11:26:31 +0500 Subject: =?UTF-8?q?Fix:=20CREATE=20TABLE=20t1=20(s1=20CHAR(5)=20COLLATE=20?= =?UTF-8?q?latin1=5Fgerman2=5Fci);=20INSERT=20INTO=20t1=20VALUES=20('?= =?UTF-8?q?=C3=9C');=20SELECT=20FIELD('ue',s1)=20FROM=20t1;=20The=20above?= =?UTF-8?q?=20returned=200=20in=20error.=20Now=201=20is=20returned.?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- mysql-test/r/ctype_latin1_de.result | 4 ++++ mysql-test/t/ctype_latin1_de.test | 1 + 2 files changed, 5 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index 418bd65e0dc..7503c16a85e 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -278,4 +278,8 @@ s1 COUNT(*) SELECT COUNT(DISTINCT s1) FROM t1; COUNT(DISTINCT s1) 1 +SELECT FIELD('ue',s1), FIELD('Ü',s1), s1='ue', s1='Ü' FROM t1; +FIELD('ue',s1) FIELD('Ü',s1) s1='ue' s1='Ü' +1 1 1 1 +1 1 1 1 DROP TABLE t1; diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test index 0bbd9fa95e4..00ac128a478 100644 --- a/mysql-test/t/ctype_latin1_de.test +++ b/mysql-test/t/ctype_latin1_de.test @@ -82,4 +82,5 @@ INSERT INTO t1 VALUES ('ue'); SELECT DISTINCT s1 FROM t1; SELECT s1,COUNT(*) FROM t1 GROUP BY s1; SELECT COUNT(DISTINCT s1) FROM t1; +SELECT FIELD('ue',s1), FIELD('Ü',s1), s1='ue', s1='Ü' FROM t1; DROP TABLE t1; -- cgit v1.2.1 From a8cb3628b7b37503fa02e9f3b091eff5a3c3c399 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 6 Aug 2003 16:58:03 +0400 Subject: fix for bug #958 and tests for it aligned with Sergey notes. sql/records.cc: fix for bug #958 - big select with count distinct and group by aligned with Sergey notes. --- mysql-test/r/count_distinct3.result | 7 +++++ mysql-test/t/count_distinct3.test | 55 +++++++++++++++++++++++++++++++++++++ 2 files changed, 62 insertions(+) create mode 100644 mysql-test/r/count_distinct3.result create mode 100644 mysql-test/t/count_distinct3.test (limited to 'mysql-test') diff --git a/mysql-test/r/count_distinct3.result b/mysql-test/r/count_distinct3.result new file mode 100644 index 00000000000..633bb2fa252 --- /dev/null +++ b/mysql-test/r/count_distinct3.result @@ -0,0 +1,7 @@ +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER); +SELECT COUNT(*) FROM t1; +COUNT(*) +4181000 +SELECT COUNT(DISTINCT id) FROM t1 GROUP BY grp; +DROP TABLE t1; diff --git a/mysql-test/t/count_distinct3.test b/mysql-test/t/count_distinct3.test new file mode 100644 index 00000000000..e6cc98d47df --- /dev/null +++ b/mysql-test/t/count_distinct3.test @@ -0,0 +1,55 @@ +# +# this is a test for error 1032 in count(distinct) + group by, introduced in +# mysql-4.1 +# + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER); + +--disable_query_log +SET @rnd_max= 2147483647; +let $1 = 1000; +while ($1) +{ + SET @rnd= RAND(); + SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); + SET @id_rev= @rnd_max - @id; + SET @grp= CAST(128.0 * @rnd AS UNSIGNED); + INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); + dec $1; +} +CREATE TABLE t2 SELECT * FROM t1; +INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; +INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; +INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; +INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; +INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; +INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; +INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; +INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; +INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; +INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; +INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; +INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; +INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; +INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; +INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; +INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; +INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; +INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; +DROP TABLE t2; +--enable_query_log + +SELECT COUNT(*) FROM t1; + +# As t1 contains random numbers, results are different from test to test. +# That's okay, because we test only that select doesn't yield an +# error. Note, that --disable_result_log doesn't suppress error output. + +--disable_result_log +SELECT COUNT(DISTINCT id) FROM t1 GROUP BY grp; +--enable_result_log +DROP TABLE t1; -- cgit v1.2.1 From b5e85696741e02ab33432bdec254edd802839fa1 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 6 Aug 2003 18:48:34 +0300 Subject: sql_union.cc, sql_select.cc: Code clean-up sql_union.cc, union.test, union.result: A fix for a bug #978. This enables that NULL's can be entered into UNION's result set, although first SELECT columns are NOT NULL. This is also a start of fixing UNION's properly regarding type acceptance. sql_select.cc: A commit for my second July SPRINT task mysql-test/r/union.result: A fix for a bug #978. This enables that NULL's can be entered into UNION's result set, although first SELECT columns are NOT NULL. This is also a start of fixing UNION's properly regarding type acceptance. mysql-test/t/union.test: A fix for a bug #978. This enables that NULL's can be entered into UNION's result set, although first SELECT columns are NOT NULL. This is also a start of fixing UNION's properly regarding type acceptance. sql/sql_select.cc: Code clean-up sql/sql_union.cc: Code clean-up --- mysql-test/r/union.result | 13 +++++++++++++ mysql-test/t/union.test | 9 +++++++++ 2 files changed, 22 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 5b7b26bc1bb..6d8bd263546 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -328,3 +328,16 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 2 UNION t1 ref b b 5 const 1 Using where drop table t1,t2; +create table t1 ( id int not null auto_increment, primary key (id) ,user_name text ); +create table t2 ( id int not null auto_increment, primary key (id) ,group_name text ); +create table t3 ( id int not null auto_increment, primary key (id) ,user_id int ,index user_idx (user_id) ,foreign key (user_id) references users(id) ,group_id int ,index group_idx (group_id) ,foreign key (group_id) references groups(id) ); +insert into t1 (user_name) values ('Tester'); +insert into t2 (group_name) values ('Group A'); +insert into t2 (group_name) values ('Group B'); +insert into t3 (user_id, group_id) values (1,1); +select 1 'is_in_group', a.user_name, c.group_name, b.id from t1 a, t3 b, t2 c where a.id = b.user_id and b.group_id = c.id UNION select 0 'is_in_group', a.user_name, c.group_name, null from t1 a, t2 c; +is_in_group user_name group_name id +1 Tester Group A 1 +0 Tester Group A NULL +0 Tester Group B NULL +drop table t1, t2, t3; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 6d857a4b40f..aeea27ade0f 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -209,3 +209,12 @@ explain (select * from t1 where a=1) union (select * from t2 where a=1); explain (select * from t1 where a=1 and b=10) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a); explain (select * from t1 where a=1) union (select * from t1 where b=1); drop table t1,t2; +create table t1 ( id int not null auto_increment, primary key (id) ,user_name text ); +create table t2 ( id int not null auto_increment, primary key (id) ,group_name text ); +create table t3 ( id int not null auto_increment, primary key (id) ,user_id int ,index user_idx (user_id) ,foreign key (user_id) references users(id) ,group_id int ,index group_idx (group_id) ,foreign key (group_id) references groups(id) ); +insert into t1 (user_name) values ('Tester'); +insert into t2 (group_name) values ('Group A'); +insert into t2 (group_name) values ('Group B'); +insert into t3 (user_id, group_id) values (1,1); +select 1 'is_in_group', a.user_name, c.group_name, b.id from t1 a, t3 b, t2 c where a.id = b.user_id and b.group_id = c.id UNION select 0 'is_in_group', a.user_name, c.group_name, null from t1 a, t2 c; +drop table t1, t2, t3; -- cgit v1.2.1