diff options
author | unknown <igor@rurik.mysql.com> | 2005-06-29 02:40:25 -0700 |
---|---|---|
committer | unknown <igor@rurik.mysql.com> | 2005-06-29 02:40:25 -0700 |
commit | 3dcf7083a9518a4d4bb6c0c6cd3aad12e6864d02 (patch) | |
tree | 26a7b4701df54ab3fcf83d8fceb434fe513a070e | |
parent | dadf91f0ff29d1ac767a16803c3eaca7db3e13ed (diff) | |
download | mariadb-git-3dcf7083a9518a4d4bb6c0c6cd3aad12e6864d02.tar.gz |
func_str.test:
Added test cases for bug #11469.
item_strfunc.h:
Fixed bug #11469: wrong implementation of the not_null_tables
method for CONCAT_WS.
sql/item_strfunc.h:
Fixed bug #11469: wrong implementation of the not_null_tables
method for CONCAT_WS.
mysql-test/t/func_str.test:
Added test cases for bug #11469.
-rw-r--r-- | mysql-test/r/func_str.result | 50 | ||||
-rw-r--r-- | mysql-test/t/func_str.test | 56 | ||||
-rw-r--r-- | sql/item_strfunc.h | 1 |
3 files changed, 107 insertions, 0 deletions
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 60c77d91ca5..cbedf4370ff 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -800,3 +800,53 @@ SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6); str num notnumber 0 DROP TABLE t1,t2; +CREATE TABLE t1( +id int(11) NOT NULL auto_increment, +pc int(11) NOT NULL default '0', +title varchar(20) default NULL, +PRIMARY KEY (id) +); +INSERT INTO t1 VALUES +(1, 0, 'Main'), +(2, 1, 'Toys'), +(3, 1, 'Games'); +SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1 +FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id +LEFT JOIN t1 AS t3 ON t2.pc=t3.id; +id col1 +1 Main +2 Main->Toys +3 Main->Games +SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1 +FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id +LEFT JOIN t1 AS t3 ON t2.pc=t3.id +WHERE CONCAT_WS('->', t3.title, t2.title, t1.title) LIKE '%Toys%'; +id col1 +2 Main->Toys +DROP TABLE t1; +CREATE TABLE t1( +trackid int(10) unsigned NOT NULL auto_increment, +trackname varchar(100) NOT NULL default '', +PRIMARY KEY (trackid) +); +CREATE TABLE t2( +artistid int(10) unsigned NOT NULL auto_increment, +artistname varchar(100) NOT NULL default '', +PRIMARY KEY (artistid) +); +CREATE TABLE t3( +trackid int(10) unsigned NOT NULL, +artistid int(10) unsigned NOT NULL, +PRIMARY KEY (trackid,artistid) +); +INSERT INTO t1 VALUES (1, 'April In Paris'), (2, 'Autumn In New York'); +INSERT INTO t2 VALUES (1, 'Vernon Duke'); +INSERT INTO t3 VALUES (1,1); +SELECT CONCAT_WS(' ', trackname, artistname) trackname, artistname +FROM t1 LEFT JOIN t3 ON t1.trackid=t3.trackid +LEFT JOIN t2 ON t2.artistid=t3.artistid +WHERE CONCAT_WS(' ', trackname, artistname) LIKE '%In%'; +trackname artistname +April In Paris Vernon Duke Vernon Duke +Autumn In New York NULL +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 36cfac16ff3..f5f9ddac3b5 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -541,3 +541,59 @@ SELECT * FROM t1, t2 WHERE num=str; SELECT * FROM t1, t2 WHERE num=substring(str from 1 for 6); DROP TABLE t1,t2; + +# +# Bug #11469: NOT NULL optimization wrongly used for arguments of CONCAT_WS +# + +CREATE TABLE t1( + id int(11) NOT NULL auto_increment, + pc int(11) NOT NULL default '0', + title varchar(20) default NULL, + PRIMARY KEY (id) +); + +INSERT INTO t1 VALUES + (1, 0, 'Main'), + (2, 1, 'Toys'), + (3, 1, 'Games'); + +SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1 + FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id + LEFT JOIN t1 AS t3 ON t2.pc=t3.id; +SELECT t1.id, CONCAT_WS('->', t3.title, t2.title, t1.title) as col1 + FROM t1 LEFT JOIN t1 AS t2 ON t1.pc=t2.id + LEFT JOIN t1 AS t3 ON t2.pc=t3.id + WHERE CONCAT_WS('->', t3.title, t2.title, t1.title) LIKE '%Toys%'; + +DROP TABLE t1; + + +CREATE TABLE t1( + trackid int(10) unsigned NOT NULL auto_increment, + trackname varchar(100) NOT NULL default '', + PRIMARY KEY (trackid) +); + +CREATE TABLE t2( + artistid int(10) unsigned NOT NULL auto_increment, + artistname varchar(100) NOT NULL default '', + PRIMARY KEY (artistid) +); + +CREATE TABLE t3( + trackid int(10) unsigned NOT NULL, + artistid int(10) unsigned NOT NULL, + PRIMARY KEY (trackid,artistid) +); + +INSERT INTO t1 VALUES (1, 'April In Paris'), (2, 'Autumn In New York'); +INSERT INTO t2 VALUES (1, 'Vernon Duke'); +INSERT INTO t3 VALUES (1,1); + +SELECT CONCAT_WS(' ', trackname, artistname) trackname, artistname + FROM t1 LEFT JOIN t3 ON t1.trackid=t3.trackid + LEFT JOIN t2 ON t2.artistid=t3.artistid + WHERE CONCAT_WS(' ', trackname, artistname) LIKE '%In%'; + +DROP TABLE t1,t2,t3; diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index 6f6af415086..b01d75b8e02 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -95,6 +95,7 @@ public: String *val_str(String *); void fix_length_and_dec(); const char *func_name() const { return "concat_ws"; } + table_map not_null_tables() const { return 0; } }; class Item_func_reverse :public Item_str_func |