summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorunknown <igor@rurik.mysql.com>2005-06-29 02:40:25 -0700
committerunknown <igor@rurik.mysql.com>2005-06-29 02:40:25 -0700
commit3dcf7083a9518a4d4bb6c0c6cd3aad12e6864d02 (patch)
tree26a7b4701df54ab3fcf83d8fceb434fe513a070e
parentdadf91f0ff29d1ac767a16803c3eaca7db3e13ed (diff)
downloadmariadb-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.result50
-rw-r--r--mysql-test/t/func_str.test56
-rw-r--r--sql/item_strfunc.h1
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