From 661796389f88dc2d436441156dd9d52da2407068 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 11 Jan 2007 16:05:03 +0300 Subject: Bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and non-restricting WHERE is present. If a DELETE statement with ORDER BY and LIMIT contains a WHERE clause with conditions that for sure cannot be used for index access (like in WHERE @var:= field) the execution always follows the filesort path. It happens currently even when for the above case there is an index that can be used to speedup sorting by the order by list. Now if a DELETE statement with ORDER BY and LIMIT contains such WHERE clause conditions that cannot be used to build any quick select then the mysql_delete() tries to use an index like there is no WHERE clause at all. mysql-test/t/delete.test: Added a test case for bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and non-restricting WHERE is present. mysql-test/r/delete.result: Added a test case for bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and non-restricting WHERE is present. sql/sql_delete.cc: Bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and non-restricting WHERE is present. Now if a DELETE statement with ORDER BY and LIMIT contains such WHERE clause conditions that cannot be used to build any quick select then the mysql_delete() tries to use an index like there is no WHERE clause at all. --- mysql-test/r/delete.result | 7 +++++++ 1 file changed, 7 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 7a6af8fd905..a5c22e66569 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -186,4 +186,11 @@ select count(*) from t1; count(*) 0 drop table t1; +create table t1(f1 int primary key); +insert into t1 values (4),(3),(1),(2); +delete from t1 where (@a:= f1) order by f1 limit 1; +select @a; +@a +1 +drop table t1; End of 4.1 tests -- cgit v1.2.1 From 52c100ae0cfbf5588fe3fa1917aba3934241e3f6 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 11 Jan 2007 16:45:38 +0300 Subject: Bug#23409: Arguments of the ENCODE() and the DECODE() functions were not printed correctly. The Item_func::print method was used to print the Item_func_encode and the Item_func_decode objects. The last argument to ENCODE and DECODE functions is a plain C string and thus Item_func::print wasn't able to print it. The print() method is added to the Item_func_encode class. It correctly prints the Item_func_encode and the Item_func_decode objects. mysql-test/t/func_str.test: Added a test case for bug#23409: Arguments of the ENCODE() and the > DECODE() functionswere not printed correctly. mysql-test/r/func_str.result: Added a test case for bug#23409: Arguments of the ENCODE() and the DECODE() functions were not printed correctly. sql/item_strfunc.h: Bug#23409: Arguments of the ENCODE() and the DECODE() functions were not printed correctly. The print() method is added to the Item_func_encode class. sql/item_strfunc.cc: Bug#23409: Arguments of the ENCODE() and the DECODE() functions were not printed correctly. The print() method is added to the Item_func_encode class. It correctly prints the Item_func_encode and the Item_func_decode objects. --- mysql-test/r/func_str.result | 14 +++++++++++++- 1 file changed, 13 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 2c15e5581e8..af6a4d20cff 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -806,7 +806,7 @@ explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'moo id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,cast(_latin1'HE' as char charset binary) AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate _latin1'latin1_bin'),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substr_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")` +Note 1003 select md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,cast(_latin1'HE' as char charset binary) AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate _latin1'latin1_bin'),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substr_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000),'monty'),'monty') AS `decode(encode(repeat("a",100000),"monty"),"monty")` SELECT lpad(12345, 5, "#"); lpad(12345, 5, "#") 12345 @@ -1064,4 +1064,16 @@ id select_type table type possible_keys key key_len ref rows Extra Warnings: Note 1003 select test.t1.s AS `s` from test.t1 where (trim(both _latin1'y' from test.t1.s) > _latin1'ab') DROP TABLE t1; +create table t1(f1 varchar(4)); +explain extended select encode(f1,'zxcv') as 'enc' from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +Warnings: +Note 1003 select encode(test.t1.f1,'zxcv') AS `enc` from test.t1 +explain extended select decode(f1,'zxcv') as 'enc' from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +Warnings: +Note 1003 select decode(test.t1.f1,'zxcv') AS `enc` from test.t1 +drop table t1; End of 4.1 tests -- cgit v1.2.1 From 4d143a6ff6112cc3f03bc078a97417c22810ad19 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 11 Jan 2007 23:18:01 +0300 Subject: Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode. Currently in the ONLY_FULL_GROUP_BY mode no hidden fields are allowed in the select list. To ensure this each expression in the select list is checked to be a constant, an aggregate function or to occur in the GROUP BY list. The last two requirements are wrong and doesn't allow valid expressions like "MAX(b) - MIN(b)" or "a + 1" in a query with grouping by a. The correct check implemented by the patch will ensure that: any field reference in the [sub]expressions of the select list is under an aggregate function or is mentioned as member of the group list or is an outer reference or is part of the select list element that coincide with a grouping element. The Item_field objects now can contain the position of the select list expression which they belong to. The position is saved during the field's Item_field::fix_fields() call. The non_agg_fields list for non-aggregated fields is added to the SELECT_LEX class. The SELECT_LEX::cur_pos_in_select_list now contains the position in the select list of the expression being currently fixed. sql/item.cc: Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode. The Item_field objects now contain the position of the select list expression which they belong to. The position is saved at the field's Item_field::fix_fields() call. sql/item.h: Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode. The Item_field objects now can store the position in the select list of the expression to which they are belongs to. sql/mysql_priv.h: Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode. Added the UNDEF_POS constant. sql/sql_base.cc: Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode. Now the setup_fields() function maintains the cur_pos_in_select_list variable. sql/sql_lex.cc: Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode. Set the cur_pos_in_select_list variable and the non_agg_fields list to their initial state. sql/sql_lex.h: Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode. The non_agg_fields list for non-aggregated fields is added to the SELECT_LEX class. The SELECT_LEX::cur_pos_in_select_list now stores the position in the select list of the expression being currently fixed. sql/sql_select.cc: Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode. Each select now keeps the list of fields that aren't used under any aggregate function. If an expression from the select list isn't found in the GROUP BY list the setup_group() function additionally checks whether non-aggregated fields occur in that expression. If there at least one such field and it isn't found in the GROUP BY list then an error is thrown. sql/sql_union.cc: Bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode. Clean up of the non_agg_fields list. mysql-test/r/group_by.result: Added a test case for the bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode. mysql-test/t/group_by.test: Added a test case for the bug#23417: Too strict checks against GROUP BY in the ONLY_FULL_GROUP_BY mode. --- mysql-test/r/group_by.result | 102 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 102 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 7d1e8832069..97375898f41 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -933,3 +933,105 @@ b sum(1) 18 6 19 6 DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); +SET SQL_MODE = 'ONLY_FULL_GROUP_BY'; +SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b; +MAX(a)-MIN(a) +1 +1 +1 +SELECT CEILING(MIN(a)) FROM t1 GROUP BY b; +CEILING(MIN(a)) +1 +3 +5 +SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 +GROUP BY b; +CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END +Positive +Positive +Positive +SELECT a + 1 FROM t1 GROUP BY a; +a + 1 +2 +3 +4 +5 +6 +7 +SELECT a + b FROM t1 GROUP BY b; +ERROR 42000: 'test.t1.a' isn't in GROUP BY +SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) +FROM t1 AS t1_outer; +(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) +1 +2 +3 +4 +5 +6 +SELECT 1 FROM t1 as t1_outer GROUP BY a +HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1); +1 +1 +1 +1 +1 +1 +1 +SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1) +FROM t1 AS t1_outer GROUP BY t1_outer.b; +ERROR 42000: 'test.t1_outer.a' isn't in GROUP BY +SELECT 1 FROM t1 as t1_outer GROUP BY a +HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1); +ERROR 42S22: Unknown column 'test.t1_outer.b' in 'field list' +SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) +FROM t1 AS t1_outer GROUP BY t1_outer.b; +(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) +21 +21 +21 +SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1) +FROM t1 AS t1_outer; +(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1) +3 +3 +3 +3 +3 +3 +SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1) +FROM t1 AS t1_outer GROUP BY t1_outer.b; +ERROR 42000: 'test.t1_outer.a' isn't in GROUP BY +SELECT 1 FROM t1 as t1_outer +WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1); +1 +1 +1 +1 +1 +1 +1 +SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0; +b +1 +2 +3 +SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12; +1 +1 +1 +SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1); +1 +1 +SELECT 1 FROM t1 GROUP BY b HAVING a = 2; +ERROR 42S22: Unknown column 'a' in 'having clause' +SELECT 1 FROM t1 GROUP BY SUM(b); +ERROR HY000: Invalid use of group function +SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN +(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a +HAVING SUM(t1_inner.b)+t1_outer.b > 5); +ERROR 42000: 'test.t1_outer.b' isn't in GROUP BY +DROP TABLE t1; +SET SQL_MODE = ''; -- cgit v1.2.1 From 94046b73e29d54fe0f08c38532e0130a68ec18bd Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 12 Jan 2007 15:46:20 +0400 Subject: bug #24186 (nested query across partitions returns fewer records) Subselect's engine checks table->status field to determine if the record was properly found when we use keyread upon the table. Partition engine checks all the partitions for given key before return. So if matching record was found in the first partition and no matching records were found in the second, we have table->status == NOT_FOUND after the function, what makes subselects to skip matching records. The patch adds table->status= 0 if we actually found something. mysql-test/r/partition.result: result fixed mysql-test/t/partition.test: testcase sql/ha_partition.cc: table->status set to 0 if we found something in previous partitions --- mysql-test/r/partition.result | 81 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 81 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 55ba8380665..f7eda649dd2 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1138,4 +1138,85 @@ PARTITION p_100 VALUES LESS THAN (100), PARTITION p_X VALUES LESS THAN MAXVALUE ); drop table t1; +CREATE TABLE t2 ( +taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +id int(11) NOT NULL DEFAULT '0', +PRIMARY KEY (id,taken), +KEY taken (taken) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES +('2006-09-27 21:50:01',16421), +('2006-10-02 21:50:01',16421), +('2006-09-27 21:50:01',19092), +('2006-09-28 21:50:01',19092), +('2006-09-29 21:50:01',19092), +('2006-09-30 21:50:01',19092), +('2006-10-01 21:50:01',19092), +('2006-10-02 21:50:01',19092), +('2006-09-27 21:50:01',22589), +('2006-09-29 21:50:01',22589); +CREATE TABLE t1 ( +id int(8) NOT NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES +(16421), +(19092), +(22589); +CREATE TABLE t4 ( +taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +id int(11) NOT NULL DEFAULT '0', +PRIMARY KEY (id,taken), +KEY taken (taken) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +PARTITION BY RANGE (to_days(taken)) +( +PARTITION p01 VALUES LESS THAN (732920) , +PARTITION p02 VALUES LESS THAN (732950) , +PARTITION p03 VALUES LESS THAN MAXVALUE ) ; +INSERT INTO t4 select * from t2; +set @f_date='2006-09-28'; +set @t_date='2006-10-02'; +SELECT t1.id AS MyISAM_part +FROM t1 +WHERE t1.id IN ( +SELECT distinct id +FROM t4 +WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY)) +ORDER BY t1.id +; +MyISAM_part +16421 +19092 +22589 +drop table t1, t2, t4; +CREATE TABLE t1 ( +taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +id int(11) NOT NULL DEFAULT '0', +status varchar(20) NOT NULL DEFAULT '', +PRIMARY KEY (id,taken) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +PARTITION BY RANGE (to_days(taken)) +( +PARTITION p15 VALUES LESS THAN (732950) , +PARTITION p16 VALUES LESS THAN MAXVALUE ) ; +INSERT INTO t1 VALUES +('2006-09-27 21:50:01',22589,'Open'), +('2006-09-29 21:50:01',22589,'Verified'); +DROP TABLE IF EXISTS t2; +Warnings: +Note 1051 Unknown table 't2' +CREATE TABLE t2 ( +id int(8) NOT NULL, +severity tinyint(4) NOT NULL DEFAULT '0', +priority tinyint(4) NOT NULL DEFAULT '0', +status varchar(20) DEFAULT NULL, +alien tinyint(4) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES +(22589,1,1,'Need Feedback',0); +SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified'); +id +22589 +drop table t1, t2; End of 5.1 tests -- cgit v1.2.1 From 6746efb123ba5ae104e849c40e7bf85cb9ca7ef8 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 12 Jan 2007 17:35:24 +0300 Subject: func_str.result: After merge fix mysql-test/r/func_str.result: After merge fix --- mysql-test/r/func_str.result | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index c922f57d820..052451f8c54 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -817,7 +817,7 @@ explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'moo id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,cast(_latin1'HE' as char charset binary) AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate latin1_bin),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substring_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")` +Note 1003 select md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,cast(_latin1'HE' as char charset binary) AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate latin1_bin),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substring_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000),'monty'),'monty') AS `decode(encode(repeat("a",100000),"monty"),"monty")` SELECT lpad(12345, 5, "#"); lpad(12345, 5, "#") 12345 @@ -1089,12 +1089,12 @@ explain extended select encode(f1,'zxcv') as 'enc' from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found Warnings: -Note 1003 select encode(test.t1.f1,'zxcv') AS `enc` from test.t1 +Note 1003 select encode(`test`.`t1`.`f1`,'zxcv') AS `enc` from `test`.`t1` explain extended select decode(f1,'zxcv') as 'enc' from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found Warnings: -Note 1003 select decode(test.t1.f1,'zxcv') AS `enc` from test.t1 +Note 1003 select decode(`test`.`t1`.`f1`,'zxcv') AS `enc` from `test`.`t1` drop table t1; End of 4.1 tests create table t1 (d decimal default null); -- cgit v1.2.1 From 35e363932cdcf626efe42006df98f7182731e427 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 12 Jan 2007 13:43:25 -0800 Subject: Fixed bug #25398: crash in a trigger when using trigger fields in a select list. The objects of the Item_trigger_field class inherited the implementations of the methods copy_or_same, get_tmp_table_item and get_tmp_table_field from the class Item_field while they rather should have used the default implementations defined for the base class Item. It could cause catastrophic problems for triggers that used SELECTs with select list containing trigger fields such as NEW. under DISTINCT. mysql-test/r/trigger.result: Added a test case for bug #25398. mysql-test/t/trigger.test: Added a test case for bug #25398. --- mysql-test/r/trigger.result | 32 ++++++++++++++++++++++++++++++++ 1 file changed, 32 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 9f34f60eb1a..3d40a2d05df 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1278,4 +1278,36 @@ a b 2 b 3 c drop table t1; +CREATE TABLE t1 ( +id int NOT NULL DEFAULT '0', +a varchar(10) NOT NULL, +b varchar(10), +c varchar(10), +d timestamp NOT NULL, +PRIMARY KEY (id, a) +); +CREATE TABLE t2 ( +fubar_id int unsigned NOT NULL DEFAULT '0', +last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +PRIMARY KEY (fubar_id) +); +CREATE TRIGGER fubar_change +AFTER UPDATE ON t1 +FOR EACH ROW +BEGIN +INSERT INTO t2 (fubar_id, last_change_time) +SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time +FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c) +ON DUPLICATE KEY UPDATE +last_change_time = +IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time); +END +| +INSERT INTO t1 (id,a, b,c,d) VALUES +(1,'a','b','c',now()),(2,'a','b','c',now()); +UPDATE t1 SET c='Bang!' WHERE id=1; +SELECT fubar_id FROM t2; +fubar_id +1 +DROP TABLE t1,t2; End of 5.0 tests -- cgit v1.2.1 From b9dc0243a822df6484f91f15aa1734b84396ba7d Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 13 Jan 2007 10:49:26 -0800 Subject: Fixed bug #24776: an assertion abort in handler::ha_index_init for queries using 'range checked for each record'. The problem was fixed in 5.0 by the patch for bug 12291. This patch down-ported the corresponding code from 5.0 into QUICK_SELECT::init() and added a new test case. mysql-test/r/range.result: Added a test case for bug #24776. mysql-test/t/range.test: Added a test case for bug #24776. sql/opt_range.h: Fixed bug #24776: an assertion abort in handler::ha_index_init for queries using 'range checked for each record'. The problem was fixed in 5.0 by the patch for bug 12291. The patch for 4.1 down-ported the corresponding code from 5.0 into QUICK_SELECT::init(). --- mysql-test/r/range.result | 43 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 43 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index f25d94f8066..2125f237d0e 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -658,4 +658,47 @@ select a from t1 where a > 'x'; a xx drop table t1; +CREATE TABLE t1 ( +OXID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', +OXPARENTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT 'oxrootid', +OXLEFT int NOT NULL DEFAULT '0', +OXRIGHT int NOT NULL DEFAULT '0', +OXROOTID varchar(32) COLLATE latin1_german2_ci NOT NULL DEFAULT '', +PRIMARY KEY (OXID), +KEY OXNID (OXID), +KEY OXLEFT (OXLEFT), +KEY OXRIGHT (OXRIGHT), +KEY OXROOTID (OXROOTID) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci; +INSERT INTO t1 VALUES +('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'), +('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3, +'d8c4177d09f8b11f5.52725521'), +('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5, +'d8c4177d09f8b11f5.52725521'), +('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7, +'d8c4177d09f8b11f5.52725521'), +('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9, +'d8c4177d09f8b11f5.52725521'), +('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11, +'d8c4177d09f8b11f5.52725521'); +EXPLAIN +SELECT s.oxid FROM t1 v, t1 s +WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND +v.oxrootid ='d8c4177d09f8b11f5.52725521' AND +s.oxleft > v.oxleft AND s.oxleft < v.oxright; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 32 const 5 Using where +1 SIMPLE s ALL OXLEFT NULL NULL NULL 5 Range checked for each record (index map: 0x4) +SELECT s.oxid FROM t1 v, t1 s +WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND +v.oxrootid ='d8c4177d09f8b11f5.52725521' AND +s.oxleft > v.oxleft AND s.oxleft < v.oxright; +oxid +d8c4177d151affab2.81582770 +d8c4177d206a333d2.74422679 +d8c4177d225791924.30714720 +d8c4177d2380fc201.39666693 +d8c4177d24ccef970.14957924 +DROP TABLE t1; End of 4.1 tests -- cgit v1.2.1 From 9af4bbc7b038a89e60bced7bd97cc1165525a020 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 15 Jan 2007 13:39:28 +0400 Subject: Bug#21713 incorrect value for the REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME column added new field 'REFERENCED_TABLE_NAME' to 'referential_constraints' table field 'UNIQUE_CONSTRAINT_NAME' contains the name of the referenced index mysql-test/r/information_schema_inno.result: result fix mysql-test/t/information_schema_inno.test: test fix sql/sql_show.cc: added new field 'REFERENCED_TABLE_NAME' to 'referential_constraints' table field 'UNIQUE_CONSTRAINT_NAME' contains the name of the referenced index sql/table.h: added 'referenced_key_name' member to 'st_foreign_key_info' struct storage/innobase/handler/ha_innodb.cc: added the filling of referenced key name --- mysql-test/r/information_schema_inno.result | 16 ++++++++-------- 1 file changed, 8 insertions(+), 8 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/information_schema_inno.result b/mysql-test/r/information_schema_inno.result index b9c0cae5efb..2ea2a0bec0b 100644 --- a/mysql-test/r/information_schema_inno.result +++ b/mysql-test/r/information_schema_inno.result @@ -31,11 +31,11 @@ CREATE TABLE t2(b1 INT, b2 INT, INDEX (b1, b2), CONSTRAINT A1 FOREIGN KEY (b1, b2) REFERENCES t1(a1, a2) ON UPDATE CASCADE ON DELETE NO ACTION) ENGINE=INNODB; -CREATE TABLE t3(b1 INT, b2 INT, INDEX (b1, b2), +CREATE TABLE t3(b1 INT, b2 INT, INDEX t3_indx (b1, b2), CONSTRAINT A2 FOREIGN KEY (b1, b2) REFERENCES t2(b1, b2) ON UPDATE SET NULL ON DELETE RESTRICT) ENGINE=INNODB; -CREATE TABLE t4(b1 INT, b2 INT, INDEX (b1, b2), +CREATE TABLE t4(b1 INT, b2 INT, UNIQUE KEY t4_ukey (b1, b2), CONSTRAINT A3 FOREIGN KEY (b1, b2) REFERENCES t3(b1, b2) ON UPDATE NO ACTION ON DELETE SET NULL) ENGINE=INNODB; @@ -45,16 +45,16 @@ FOREIGN KEY (b1, b2) REFERENCES t4(b1, b2) ON UPDATE RESTRICT ON DELETE CASCADE) ENGINE=INNODB; select a.CONSTRAINT_SCHEMA, b.TABLE_NAME, CONSTRAINT_TYPE, b.CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_NAME, -MATCH_OPTION, UPDATE_RULE, DELETE_RULE +MATCH_OPTION, UPDATE_RULE, DELETE_RULE, b.REFERENCED_TABLE_NAME from information_schema.TABLE_CONSTRAINTS a, information_schema.REFERENTIAL_CONSTRAINTS b where a.CONSTRAINT_SCHEMA = 'test' and a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME; -CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE -test t2 FOREIGN KEY A1 test t1 NONE CASCADE NO ACTION -test t3 FOREIGN KEY A2 test t2 NONE SET NULL RESTRICT -test t4 FOREIGN KEY A3 test t3 NONE NO ACTION SET NULL -test t5 FOREIGN KEY A4 test t4 NONE RESTRICT CASCADE +CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE REFERENCED_TABLE_NAME +test t2 FOREIGN KEY A1 test PRIMARY NONE CASCADE NO ACTION t1 +test t3 FOREIGN KEY A2 test b1 NONE SET NULL RESTRICT t2 +test t4 FOREIGN KEY A3 test t3_indx NONE NO ACTION SET NULL t3 +test t5 FOREIGN KEY A4 test t4_ukey NONE RESTRICT CASCADE t4 drop tables t5, t4, t3, t2, t1; create database `db-1`; use `db-1`; -- cgit v1.2.1 From a2ae17b1e16ba18322ced5ffea96a90e408e8a48 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 15 Jan 2007 19:15:52 +0200 Subject: BUG#20420: optimizer reports wrong keys on left join with IN The optimizer needs to evaluate whether predicates are better evaluated using an index. IN is one such predicate. To qualify an IN predicate must involve a field of the index on the left and constant arguments on the right. However whether an expression is a constant can be determined only by knowing the preceding tables in the join order. Assuming that only IN predicates with expressions on the right that are constant for the whole query qualify limits the scope of possible optimizations of the IN predicate (more specifically it doesn't allow the "Range checked for each record" optimization for such an IN predicate. Fixed by not pre-determining the optimizability of the IN predicate in the case when all right IN operands are not SQL constant expressions mysql-test/r/func_in.result: BUG#20420: optimizer reports wrong keys on left join with IN - test case mysql-test/t/func_in.test: BUG#20420: optimizer reports wrong keys on left join with IN - test case sql/item_cmpfunc.h: BUG#20420: optimizer reports wrong keys on left join with IN - cannot decide on the optimizability of a function without knowing the tables before it in the join order --- mysql-test/r/func_in.result | 43 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 43 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index a172d04d880..98e39917f04 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -351,4 +351,47 @@ some_id 1 2 drop table t1; +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1); +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); +INSERT INTO t2 VALUES (3,2),(4,2); +CREATE TABLE t3 (a int PRIMARY KEY); +INSERT INTO t3 VALUES (1),(2),(3),(4); +CREATE TABLE t4 (a int PRIMARY KEY); +INSERT INTO t4 VALUES (1),(2); +EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 +JOIN t1 ON t3.a=t1.a +JOIN t2 ON t3.a=t2.a +JOIN t4 WHERE t4.a IN (t1.b, t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 +1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) +SELECT STRAIGHT_JOIN * FROM t3 +JOIN t1 ON t3.a=t1.a +JOIN t2 ON t3.a=t2.a +JOIN t4 WHERE t4.a IN (t1.b, t2.b); +a a b a b a +3 3 1 3 2 1 +3 3 1 3 2 2 +4 4 1 4 2 1 +4 4 1 4 2 2 +EXPLAIN SELECT STRAIGHT_JOIN +(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) +FROM t3, t1, t2 +WHERE t3.a=t1.a AND t3.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 +2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 2 Using where; Using index +SELECT STRAIGHT_JOIN +(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) +FROM t3, t1, t2 +WHERE t3.a=t1.a AND t3.a=t2.a; +(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) +3 +3 +DROP TABLE t1,t2,t3,t4; End of 5.0 tests -- cgit v1.2.1 From 759a028c20412d26802d479724de932a288a3e43 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 15 Jan 2007 14:01:36 -0800 Subject: Adjusted results after merge 4.1 -> 5.0 for the patch fixing bug 24776. --- mysql-test/r/range.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 6037824ea16..f84bb2788e1 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -704,7 +704,7 @@ WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 32 const 5 Using where +1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using where 1 SIMPLE s ALL OXLEFT NULL NULL NULL 5 Range checked for each record (index map: 0x4) SELECT s.oxid FROM t1 v, t1 s WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND -- cgit v1.2.1 From af3216862354eea44e594eb9216252c7b260fb3e Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 17 Jan 2007 20:13:45 -0800 Subject: Fixed bug #25580: incorrect stored representations of views in cases when they contain the '!' operator. Added an implementation for the method Item_func_not::print. The method encloses any NOT expression into extra parentheses to avoid incorrect stored representations of views that use the '!' operators. Without this change when a view was created that contained the expression !0*5 its stored representation contained not this expression but rather the expression not(0)*5 . The operator '!' is of a higher precedence than '*', while NOT is of a lower precedence than '*'. That's why the expression !0*5 is interpreted as not(0)*5, while the expression not(0)*5 is interpreted as not((0)*5) unless sql_mode is set to HIGH_NOT_PRECEDENCE. Now we translate !0*5 into (not(0))*5. mysql-test/r/sp-code.result: Adjusted results after the fix of bug 25580. mysql-test/r/subselect.result: Adjusted results after the fix of bug 25580. mysql-test/r/view.result: Added a test case for bug #25580. mysql-test/t/view.test: Added a test case for bug #25580. sql/item_cmpfunc.cc: Fixed bug #25580: incorrect stored representations of views in cases when they contain the '!' operator. Added an implementation for the method Item_func_not::print. The method encloses the NOT expression into extra parenthesis to avoid incorrect stored representations of views that use the '!' operators. sql/item_cmpfunc.h: Fixed bug #25580: incorrect stored representations of views in cases when they contain the '!' operator. Added an implementation for the method Item_func_not::print. The method encloses the NOT expression into extra parenthesis to avoid incorrect stored representations of views that use the '!' operators. --- mysql-test/r/sp-code.result | 2 +- mysql-test/r/subselect.result | 10 +++++----- mysql-test/r/view.result | 11 +++++++++++ 3 files changed, 17 insertions(+), 6 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result index 4ae38861d29..8fea85b7cd7 100644 --- a/mysql-test/r/sp-code.result +++ b/mysql-test/r/sp-code.result @@ -187,7 +187,7 @@ Pos Instruction 32 set v_dig@4 (v_dig@4 + 1) 33 stmt 4 "update sudoku_work set dig = v_dig wh..." 34 set v_tcounter@6 (v_tcounter@6 + 1) -35 jump_if_not 37(37) not(`test`.`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4)) +35 jump_if_not 37(37) (not(`test`.`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4))) 36 jump 15 37 set v_i@3 (v_i@3 + 1) 38 jump 15 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 1bf6d6c7716..7d72a9cb038 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1464,7 +1464,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,not((`test`.`t1`.`s1`,(((`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not((`test`.`t1`.`s1`,(((`test`.`t1`.`s1`) in t2 on s1 checking NULL))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 Using index @@ -1476,13 +1476,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,not((`test`.`t1`.`s1`,(((`test`.`t1`.`s1`) in t2 on s1 checking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not((`test`.`t1`.`s1`,(((`test`.`t1`.`s1`) in t2 on s1 checking NULL))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Using where Warnings: -Note 1003 select `test`.`t1`.`s1` AS `s1`,not((`test`.`t1`.`s1`,(((`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2'))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` +Note 1003 select `test`.`t1`.`s1` AS `s1`,(not((`test`.`t1`.`s1`,(((`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2')))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; create table t2 (a int, b int); create table t3 (a int); @@ -1737,14 +1737,14 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where 2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where Warnings: -Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where not((`test`.`t1`.`id`,(((`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8))))) +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not((`test`.`t1`.`id`,(((`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8)))))) explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where 2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 Using where; Using index Warnings: Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))) +Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))) insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001'); create table t2 (id int not null, text varchar(20) not null default '', primary key (id)); insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10'); diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 4d076db5c22..7f3106e5972 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3014,4 +3014,15 @@ i j 6 3 DROP VIEW v1, v2; DROP TABLE t1; +CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL; +SHOW CREATE VIEW v; +View Create View +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select ((not(0)) * 5) AS `x` +SELECT !0 * 5 AS x FROM DUAL; +x +5 +SELECT * FROM v; +x +5 +DROP VIEW v; End of 5.0 tests. -- cgit v1.2.1 From 4ced38b89bd5bedb75c5f4188ac9e8dbe7085e2e Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 18 Jan 2007 17:33:38 +0200 Subject: Bug #25382: Passing NULL to an UDF called from stored procedures crashes server Check for null value is reliable only after calling some of the val_xxx() methods. If the val_xxx() method is not called the null_value flag will be set only for certain types of NULL values (like SQL constant NULLs for example). This caused a crash while trying to dereference a NULL pointer that is returned by val_str() for NULL values. Fixed by swapping the order of val_xxx() and null_value check. mysql-test/r/udf.result: Bug #25382: Passing NULL to an UDF called from stored procedures crashes server - test case mysql-test/t/udf.test: Bug #25382: Passing NULL to an UDF called from stored procedures crashes server - test case sql/item_func.cc: Bug #25382: Passing NULL to an UDF called from stored procedures crashes server - reliably check null_value --- mysql-test/r/udf.result | 34 ++++++++++++++++++++++++++++++++++ 1 file changed, 34 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index 64b7111bbc8..d5f59247084 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -240,3 +240,37 @@ drop table bug18761; select is_const((1,2,3)); ERROR 21000: Operand should contain 1 column(s) drop function if exists is_const; +CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; +CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; +CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; +create function f1(p1 varchar(255)) +returns varchar(255) +begin +return metaphon(p1); +end// +create function f2(p1 varchar(255)) +returns double +begin +return myfunc_double(p1); +end// +create function f3(p1 varchar(255)) +returns double +begin +return myfunc_int(p1); +end// +select f3(NULL); +f3(NULL) +0 +select f2(NULL); +f2(NULL) +NULL +select f1(NULL); +f1(NULL) +NULL +drop function f1; +drop function f2; +drop function f3; +drop function metaphon; +drop function myfunc_double; +drop function myfunc_int; +End of 5.0 tests. -- cgit v1.2.1 From edf72bf61356553ba0007085c69bdb068c2cdf31 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 19 Jan 2007 00:17:28 -0800 Subject: Fixed bug #25219: crash for a query that contains an EXIST subquery with UNION over correlated and uncorrelated SELECTS. In such subqueries each uncorrelated SELECT should be considered as uncacheable. Otherwise join_free is called for it and in many cases it causes some problems. mysql-test/r/subselect.result: Added a test case for bug #25219. mysql-test/t/subselect.test: Added a test case for bug #25219. sql/mysql_priv.h: Fixed bug #25219: crash for a query that contains an EXIST subquery with UNION over correlated and uncorrelated SELECTS. In such subqueries each uncorrelated SELECT should be considered as uncacheable. Otherwise join_free is called for it and in many cases it causes some problems. Added a new flag UNCACHEABLE_UNITED for such SELECTs. sql/sql_lex.cc: Fixed bug #25219: crash for a query that contains an EXIST subquery with UNION over correlated and uncorrelated SELECTS. In such subqueries each uncorrelated SELECT should be considered as uncacheable. Otherwise join_free is called for it and in many cases it causes some problems. Added a new flag UNCACHEABLE_UNITED for such SELECTs. --- mysql-test/r/subselect.result | 33 +++++++++++++++++++++++++++++++++ 1 file changed, 33 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 1bf6d6c7716..5cf3cd110c0 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3605,3 +3605,36 @@ FROM t1) t; COUNT(*) 3000 DROP TABLE t1,t2; +CREATE TABLE t1 (id char(4) PRIMARY KEY, c int); +CREATE TABLE t2 (c int); +INSERT INTO t1 VALUES ('aa', 1); +INSERT INTO t2 VALUES (1); +SELECT * FROM t1 +WHERE EXISTS (SELECT c FROM t2 WHERE c=1 +UNION +SELECT c from t2 WHERE c=t1.c); +id c +aa 1 +INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1); +SELECT * FROM t1 +WHERE EXISTS (SELECT c FROM t2 WHERE c=1 +UNION +SELECT c from t2 WHERE c=t1.c); +id c +aa 1 +bb 2 +cc 3 +dd 1 +INSERT INTO t2 VALUES (2); +CREATE TABLE t3 (c int); +INSERT INTO t3 VALUES (1); +SELECT * FROM t1 +WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1 +UNION +SELECT c from t2 WHERE c=t1.c); +id c +aa 1 +bb 2 +cc 3 +dd 1 +DROP TABLE t1,t2,t3; -- cgit v1.2.1 From 5effa05d3f31971461da851f0650951e1e488421 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 19 Jan 2007 18:34:09 +0300 Subject: Bug#25172: Not checked buffer size leads to a server crash. After fix for bug#21798 JOIN stores the pointer to the buffer for sorting fields. It is used while sorting for grouping and for ordering. If ORDER BY clause has more elements then the GROUP BY clause then a memory overrun occurs. Now the length of the ORDER BY list is always passed to the make_unireg_sortorder() function and it allocates buffer big enough to be used for bigger list. sql/sql_delete.cc: Bug#25172: Not checked buffer size leads to a server crash. Length parameter is initialized to 0 for the make_unireg_sortorder() function. sql/sql_select.cc: Bug#25172: Not checked buffer size leads to a server crash. Now the length of the ORDER BY list is always passed to the make_unireg_sortorder() function and it allocates buffer big enough to be used for bigger list. sql/sql_table.cc: Bug#25172: Not checked buffer size leads to a server crash. Length parameter is initialized to 0 for the make_unireg_sortorder() function. sql/sql_update.cc: Bug#25172: Not checked buffer size leads to a server crash. Length parameter is initialized to 0 for the make_unireg_sortorder() function. mysql-test/r/select.result: Added a test case for bug#25172: Not checked buffer size leads to a server crash. mysql-test/t/select.test: Added a test case for bug#25172: Not checked buffer size leads to a server crash. --- mysql-test/r/select.result | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 44063c1e890..d890510ee67 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3611,3 +3611,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range si,ai si 5 NULL 2 Using where 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where DROP TABLE t1,t2,t3; +CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); +CREATE TABLE t2 ( f11 int PRIMARY KEY ); +INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0); +INSERT INTO t2 VALUES (62); +SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1; +f1 f2 f3 f4 f5 f6 checked_out f11 +1 1 1 0 0 0 0 NULL +DROP TABLE t1, t2; -- cgit v1.2.1 From 2c6e678dd9343a905ea6b7d32cf70fe43c9f9dbe Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 22 Jan 2007 12:51:21 +0200 Subject: BUG#16590: Optimized does not do right "const" table pre-read st_table::const_key_parts member is used in determining if certain key has a prefix that is compared to constant(s) in the query predicates. If there's such prefix the index can be used to get the data from the remaining suffix columns in sorted order. However if a field is compared to another field from a "const" table the const_key_parts is not amended. This makes the optimizer unable to detect that the key can be used for sorting and adds an extra filesort. Fixed by updating const_key_parts after reading in the "const" table. mysql-test/r/order_by.result: BUG#16590: Optimized does not do right "const" table pre-read - test case mysql-test/t/order_by.test: BUG#16590: Optimized does not do right "const" table pre-read - test case sql/sql_select.cc: BUG#16590: Optimized does not do right "const" table pre-read - fill up the const_key_parts structure --- mysql-test/r/order_by.result | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index e5cc47fd9d2..646390317d0 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -918,3 +918,12 @@ NULL 2 3 DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b)); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE TABLE t2 (a INT, b INT, KEY a (a,b)); +INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2); +EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY,b b 5 const 1 +1 SIMPLE t2 ref a a 5 const 2 Using where; Using index +DROP TABLE t1,t2; -- cgit v1.2.1 From df0a08964718cf9ff8189e16fbee75397784596e Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 22 Jan 2007 15:14:38 +0300 Subject: Bug#25123: ON DUPLICATE KEY clause allows fields not from the insert table. When inserting into a join-based view the update fields from the ON DUPLICATE KEY UPDATE wasn't checked to be from the table being inserted into and were silently ignored. The new check_view_single_update() function is added to check that insert/update fields are being from the same single table of the view. sql/sql_insert.cc: Bug#25123: ON DUPLICATE KEY clause allows fields not from the insert table. The new check_view_single_update() function is added to check that insert/update fields are being from the same single table of the view. mysql-test/r/insert.result: Added a test case for bug#25123: ON DUPLICATE KEY clause allows fields not from the insert table. mysql-test/t/insert.test: Added a test case for bug#25123: ON DUPLICATE KEY clause allows fields not from the insert table. --- mysql-test/r/insert.result | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 78ef6fbccba..7900e0b7695 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -325,3 +325,24 @@ select row_count(); row_count() 1 drop table t1; +create table t1 (f1 int unique, f2 int); +create table t2 (f3 int, f4 int); +create view v1 as select * from t1, t2 where f1= f3; +insert into t1 values (1,11), (2,22); +insert into t2 values (1,12), (2,24); +insert into v1 (f1) values (3) on duplicate key update f3= f3 + 10; +ERROR HY000: Can not modify more than one base table through a join view 'test.v1' +insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; +select * from t1; +f1 f2 +1 11 +2 22 +3 NULL +insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; +select * from t1; +f1 f2 +1 11 +2 22 +12 NULL +drop view v1; +drop table t1,t2; -- cgit v1.2.1 From d74a7b9def7a9fdbfe72325a4ba05f8647e252e4 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 23 Jan 2007 19:45:58 +0200 Subject: Bug #6172: RAND(a) should only accept constant values as arguments RAND() must accept scalar expressions regardless of their kind. That includes both constant expressions and expressions that depend on column values. When the expression is constant the random seed can be initialized at compile time. However when the expression is not constant the random seed must be initialized at each invocation (while it still can be allocated at compile time). Implemented the above rules by extending Item_func_rand::val_real() to initialize the random seed at the correct place. mysql-test/r/func_math.result: Bug #6172: RAND(a) should only accept constant values as arguments - extened the test case mysql-test/t/func_math.test: Bug #6172: RAND(a) should only accept constant values as arguments - extened the test case sql/item_func.cc: Bug #6172: RAND(a) should only accept constant values as arguments - allow specifying non-const expressions as RAND() arguments sql/item_func.h: Bug #6172: RAND(a) should only accept constant values as arguments - allow specifying non-const expressions as RAND() arguments --- mysql-test/r/func_math.result | 45 ++++++++++++++++++++++++++++++++++++++----- 1 file changed, 40 insertions(+), 5 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 9a2c55b802d..565c283ee83 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -177,11 +177,46 @@ drop table t1; select abs(-2) * -2; abs(-2) * -2 -4 -create table t1 (i int); -insert into t1 values (1); -select rand(i) from t1; -ERROR HY000: Incorrect arguments to RAND -drop table t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(1),(1),(2); +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) +FROM t1; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) +656 405 +122 405 +645 405 +858 656 +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) +FROM t1 WHERE a = 1; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) +656 405 +122 405 +645 405 +INSERT INTO t1 VALUES (3); +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) +FROM t1; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) +656 405 +122 405 +645 405 +858 656 +354 906 +SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) +FROM t1 WHERE a = 1; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) +656 405 +122 405 +645 405 +PREPARE stmt FROM +"SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(?) * 1000 AS UNSIGNED) + FROM t1 WHERE a = 1"; +set @var=2; +EXECUTE stmt USING @var; +CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(?) * 1000 AS UNSIGNED) +656 656 +122 122 +645 645 +DROP TABLE t1; create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8; insert into t1 values ('http://www.foo.com/', now()); select a from t1 where a='http://www.foo.com/' order by abs(timediff(ts, 0)); -- cgit v1.2.1 From f469aa0093f2a99f0359c939801efc0e51f8e2d5 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 24 Jan 2007 14:49:36 +0400 Subject: bug #22682 Test fails --without-geometry geometry dependent parts moved to proper .test files mysql-test/r/gis.result: result fixed mysql-test/r/innodb.result: result fixed mysql-test/r/innodb_gis.result: result fixed mysql-test/r/view.result: result fixed mysql-test/t/gis.test: HAVE_GEOMETRY dependent part moved here from view.test mysql-test/t/innodb.test: HAVE_GEOMETRY dependent part moved to innodb_gis.test BitKeeper/etc/ignore: Added libmysqld/sql_servers.cc to the ignore list mysql-test/t/innodb_gis.test: HAVE_GEOMETRY dependent part moved here from innodb.test mysql-test/t/view.test: HAVE_GEOMETRY dependent part moved to gis.test --- mysql-test/r/gis.result | 11 +++++++++++ mysql-test/r/innodb.result | 2 -- mysql-test/r/innodb_gis.result | 2 ++ mysql-test/r/view.result | 11 ----------- 4 files changed, 13 insertions(+), 13 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index b7f0e4b6b80..8cbc344f062 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -718,3 +718,14 @@ desc t1; Field Type Null Key Default Extra GeomFromText('point(1 1)') geometry NO drop table t1; +create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); +create view v1 as select * from t1; +desc v1; +Field Type Null Key Default Extra +f1 tinyint(1) YES NULL +f2 char(1) YES NULL +f3 varchar(1) YES NULL +f4 geometry YES NULL +f5 datetime YES NULL +drop view v1; +drop table t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index e7fa950a131..9032ea00e11 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -3447,8 +3447,6 @@ SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over'; a 1 drop table t2, t1; -create table t1 (g geometry not null, spatial gk(g)) engine=innodb; -ERROR HY000: The used table type doesn't support SPATIAL indexes CREATE TABLE t1 ( a int ) ENGINE=innodb; BEGIN; INSERT INTO t1 VALUES (1); diff --git a/mysql-test/r/innodb_gis.result b/mysql-test/r/innodb_gis.result index 09f0aebaefa..e714dbeffc0 100644 --- a/mysql-test/r/innodb_gis.result +++ b/mysql-test/r/innodb_gis.result @@ -460,3 +460,5 @@ ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field insert into t1 (fl) values (pointfromtext('point(1,1)')); ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field drop table t1; +create table t1 (g geometry not null, spatial gk(g)) engine=innodb; +ERROR HY000: The used table type doesn't support SPATIAL indexes diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 4fbe0176c57..838df85594b 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2060,17 +2060,6 @@ CALL p1(); DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1; -create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); -create view v1 as select * from t1; -desc v1; -Field Type Null Key Default Extra -f1 tinyint(1) YES NULL -f2 char(1) YES NULL -f3 varchar(1) YES NULL -f4 geometry YES NULL -f5 datetime YES NULL -drop view v1; -drop table t1; create table t1(f1 datetime); insert into t1 values('2005.01.01 12:0:0'); create view v1 as select f1, subtime(f1, '1:1:1') as sb from t1; -- cgit v1.2.1 From 6098f770787edc75992e0b9a96081dd77e4f89aa Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 24 Jan 2007 15:55:16 +0200 Subject: merge of 5.0-opt -> 5.1-opt --- mysql-test/r/func_in.result | 49 +++------------------------------------- mysql-test/r/func_str.result | 12 +++++----- mysql-test/r/range.result | 2 +- mysql-test/r/view.result | 54 ++++++++++++++++++++++++++++++++++++-------- 4 files changed, 54 insertions(+), 63 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 0069146bfe3..9b09cc0a43c 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -351,49 +351,6 @@ some_id 1 2 drop table t1; -CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); -INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1); -CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); -INSERT INTO t2 VALUES (3,2),(4,2); -CREATE TABLE t3 (a int PRIMARY KEY); -INSERT INTO t3 VALUES (1),(2),(3),(4); -CREATE TABLE t4 (a int PRIMARY KEY); -INSERT INTO t4 VALUES (1),(2); -EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 -JOIN t1 ON t3.a=t1.a -JOIN t2 ON t3.a=t2.a -JOIN t4 WHERE t4.a IN (t1.b, t2.b); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 -1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) -SELECT STRAIGHT_JOIN * FROM t3 -JOIN t1 ON t3.a=t1.a -JOIN t2 ON t3.a=t2.a -JOIN t4 WHERE t4.a IN (t1.b, t2.b); -a a b a b a -3 3 1 3 2 1 -3 3 1 3 2 2 -4 4 1 4 2 1 -4 4 1 4 2 2 -EXPLAIN SELECT STRAIGHT_JOIN -(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) -FROM t3, t1, t2 -WHERE t3.a=t1.a AND t3.a=t2.a; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 -1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 -2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 2 Using where; Using index -SELECT STRAIGHT_JOIN -(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) -FROM t3, t1, t2 -WHERE t3.a=t1.a AND t3.a=t2.a; -(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) -3 -3 -DROP TABLE t1,t2,t3,t4; End of 5.0 tests create table t1(f1 char(1)); insert into t1 values ('a'),('b'),('1'); @@ -419,7 +376,7 @@ Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'b' explain select f1 from t1 where f1 in ('a',1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL t1f1_idx 2 NULL 3 Using where; Using index +1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index select f1 from t1 where f1 in ('a','b'); f1 a @@ -448,7 +405,7 @@ Warning 1292 Truncated incorrect DOUBLE value: 'a' Warning 1292 Truncated incorrect DOUBLE value: 'a' explain select f2 from t2 where f2 in ('a',2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL t2f2 5 NULL 3 Using where; Using index +1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index select f2 from t2 where f2 in ('a','b'); f2 0 @@ -470,6 +427,6 @@ Warning 1292 Truncated incorrect DOUBLE value: 'b' Warning 1292 Truncated incorrect DOUBLE value: 'b' explain select f2 from t2 where f2 in (1,'b'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL t2f2 5 NULL 3 Using where; Using index +1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index drop table t1, t2; End of 5.1 tests diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index fea7464f854..e6c323276ea 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1306,15 +1306,15 @@ Note 1003 select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both _latin DROP TABLE t1; create table t1(f1 varchar(4)); explain extended select encode(f1,'zxcv') as 'enc' from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select encode(`test`.`t1`.`f1`,'zxcv') AS `enc` from `test`.`t1` +Note 1003 select encode(`test`.`t1`.`f1`,_latin1'zxcv') AS `enc` from `test`.`t1` explain extended select decode(f1,'zxcv') as 'enc' from t1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select decode(`test`.`t1`.`f1`,'zxcv') AS `enc` from `test`.`t1` +Note 1003 select decode(`test`.`t1`.`f1`,_latin1'zxcv') AS `enc` from `test`.`t1` drop table t1; End of 4.1 tests create table t1 (d decimal default null); diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index c94efb50bfe..1023287572d 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -705,7 +705,7 @@ v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using where -1 SIMPLE s ALL OXLEFT NULL NULL NULL 5 Range checked for each record (index map: 0x4) +1 SIMPLE s ALL OXLEFT NULL NULL NULL 6 Range checked for each record (index map: 0x4) SELECT s.oxid FROM t1 v, t1 s WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND v.oxrootid ='d8c4177d09f8b11f5.52725521' AND diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 77d9d8c8973..86765efc42f 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2970,16 +2970,6 @@ UPDATE t1 SET i= f1(); DROP FUNCTION f1; DROP VIEW v1; DROP TABLE t1; -CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL); -CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION; -INSERT INTO v1 (val) VALUES (2); -INSERT INTO v1 (val) VALUES (4); -INSERT INTO v1 (val) VALUES (6); -ERROR HY000: CHECK OPTION failed 'test.v1' -UPDATE v1 SET val=6 WHERE id=2; -ERROR HY000: CHECK OPTION failed 'test.v1' -DROP VIEW v1; -DROP TABLE t1; DROP VIEW IF EXISTS v1, v2; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT); @@ -3026,6 +3016,50 @@ x 5 DROP VIEW v; End of 5.0 tests. +CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL); +CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION; +INSERT INTO v1 (val) VALUES (2); +INSERT INTO v1 (val) VALUES (4); +INSERT INTO v1 (val) VALUES (6); +ERROR HY000: CHECK OPTION failed 'test.v1' +UPDATE v1 SET val=6 WHERE id=2; +ERROR HY000: CHECK OPTION failed 'test.v1' +DROP VIEW v1; +DROP TABLE t1; +DROP VIEW IF EXISTS v1, v2; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT); +CREATE VIEW v1 AS SELECT j FROM t1; +CREATE VIEW v2 AS SELECT * FROM t1; +INSERT INTO t1 (j) VALUES (1); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +1 +INSERT INTO v1 (j) VALUES (2); +# LAST_INSERT_ID() should not change. +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +1 +INSERT INTO v2 (j) VALUES (3); +# LAST_INSERT_ID() should be updated. +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +3 +INSERT INTO v1 (j) SELECT j FROM t1; +# LAST_INSERT_ID() should not change. +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +3 +SELECT * FROM t1; +i j +1 1 +2 2 +3 3 +4 1 +5 2 +6 3 +DROP VIEW v1, v2; +DROP TABLE t1; DROP DATABASE IF EXISTS `d-1`; CREATE DATABASE `d-1`; USE `d-1`; -- cgit v1.2.1