Merge sanja.is.com.ua:/home/bell/mysql/bk/mysql-4.1

into sanja.is.com.ua:/home/bell/mysql/bk/work-order-4.1


sql/sql_select.cc:
  Auto merged

6 files changed, 170 insertions, 12 deletions

diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result
index 630fef9b679..e0cb7008899 100644
--- a/mysql-test/r/ctype_latin1_de.result
+++ b/mysql-test/r/ctype_latin1_de.result
@@ -215,21 +215,21 @@ drop table t1;
 create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word));
 insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae');
 update t1 set word2=word;
-select word, word=0xdf as t from t1 having t > 0;
+select word, word=binary 0xdf as t from t1 having t > 0;
 word	t
 ß	1
 select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0;
 word	t
 ss	1
 ß	1
-select * from t1 where word=0xDF;
+select * from t1 where word=binary 0xDF;
 word	word2
 ß	ß
 select * from t1 where word=CAST(0xDF as CHAR);
 word	word2
 ss	ss
 ß	ß
-select * from t1 where word2=0xDF;
+select * from t1 where word2=binary 0xDF;
 word	word2
 ß	ß
 select * from t1 where word2=CAST(0xDF as CHAR);
@@ -244,7 +244,7 @@ select * from t1 where word= 0xe4 or word=CAST(0xe4 as CHAR);
 word	word2
 ä	ä
 ae	ae
-select * from t1 where word between 0xDF and 0xDF;
+select * from t1 where word between binary 0xDF and binary 0xDF;
 word	word2
 ß	ß
 select * from t1 where word between CAST(0xDF AS CHAR) and CAST(0xDF AS CHAR);
@@ -257,7 +257,7 @@ ae	ae
 select * from t1 where word like 'AE';
 word	word2
 ae	ae
-select * from t1 where word like 0xDF;
+select * from t1 where word like binary 0xDF;
 word	word2
 ß	ß
 select * from t1 where word like CAST(0xDF as CHAR);
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 51b61dbb3e6..bf96727a6a7 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -154,8 +154,26 @@ Warning	1258	1 line(s) was(were) cut by group_concat()
 show warnings;
 Level	Code	Message
 Warning	1258	1 line(s) was(were) cut by group_concat()
+set group_concat_max_len = 1024;
+drop table if exists T_URL;
+Warnings:
+Note	1051	Unknown table 'T_URL'
+create table T_URL ( URL_ID int(11), URL varchar(80));
+drop table if exists T_REQUEST;
+Warnings:
+Note	1051	Unknown table 'T_REQUEST'
+create table T_REQUEST ( REQ_ID int(11), URL_ID int(11));
+insert into T_URL values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com');
+insert into T_REQUEST values (1,4), (5,4), (5,5);
+select REQ_ID, Group_Concat(URL) as URL from T_URL, T_REQUEST where 
+T_REQUEST.URL_ID = T_URL.URL_ID group by REQ_ID;
+REQ_ID	URL
+1	www.host.com
+5	www.host.com,www.google.com,www.help.com
+drop table T_URL;
+drop table T_REQUEST;
 select group_concat(sum(a)) from t1 group by grp;
 ERROR HY000: Invalid use of group function
 select grp,group_concat(c order by 2) from t1 group by grp;
 ERROR 42S22: Unknown column '2' in 'group statement'
-drop table if exists t1;
+drop table t1;
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 6ee452764c2..34c7752798d 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -249,6 +249,75 @@ INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf');
 SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password");
 1
 DROP TABLE t1;
+select POSITION(_latin1'B' IN _latin1'abcd');
+POSITION(_latin1'B' IN _latin1'abcd')
+2
+select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin);
+POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin)
+0
+select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd');
+POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd')
+0
+select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin);
+ERROR HY000: Illegal mix of collations (latin1_bin,EXPLICIT) and (latin1_general_ci,EXPLICIT) for operation 'locate'
+select POSITION(_latin1'B' IN _latin2'abcd');
+ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'locate'
+select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d');
+FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')
+2
+select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin);
+ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'find_in_set'
+select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d');
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'find_in_set'
+select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2);
+SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2)
+abcdabc
+select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'substr_index'
+select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);
+ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'substr_index'
+select _latin1'B' between _latin1'a' and _latin1'c';
+_latin1'B' between _latin1'a' and _latin1'c'
+1
+select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c';
+_latin1'B' collate latin1_bin between _latin1'a' and _latin1'c'
+0
+select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c';
+_latin1'B' between _latin1'a' collate latin1_bin and _latin1'c'
+0
+select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin;
+_latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin
+0
+select _latin2'B' between _latin1'a' and _latin1'b';
+ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'
+select _latin1'B' between _latin2'a' and _latin1'b';
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'
+select _latin1'B' between _latin1'a' and _latin2'b';
+ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation 'between'
+select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';
+ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation 'between'
+select _latin1'B' in (_latin1'a',_latin1'b');
+_latin1'B' in (_latin1'a',_latin1'b')
+1
+select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b');
+_latin1'B' collate latin1_bin in (_latin1'a',_latin1'b')
+0
+select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b');
+_latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b')
+0
+select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin);
+_latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin)
+0
+select _latin2'B' in (_latin1'a',_latin1'b');
+ERROR HY000: Illegal mix of collations for operation ' IN '
+select _latin1'B' in (_latin2'a',_latin1'b');
+ERROR HY000: Illegal mix of collations for operation ' IN '
+select _latin1'B' in (_latin1'a',_latin2'b');
+ERROR HY000: Illegal mix of collations for operation ' IN '
+select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');
+ERROR HY000: Illegal mix of collations for operation ' IN '
+select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin);
+ERROR HY000: Illegal mix of collations for operation ' IN '
 select collation(bin(130)), coercibility(bin(130));
 collation(bin(130))	coercibility(bin(130))
 latin1_swedish_ci	3
diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test
index b63af87601b..22a4e14158d 100644
--- a/mysql-test/t/ctype_latin1_de.test
+++ b/mysql-test/t/ctype_latin1_de.test
@@ -52,21 +52,24 @@ drop table t1;
 # Test bug report #152 (problem with index on latin1_de)
 #
 
+#
+# The below checks both binary and character comparisons.
+#
 create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word));
 insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae');
 update t1 set word2=word;
-select word, word=0xdf as t from t1 having t > 0;
+select word, word=binary 0xdf as t from t1 having t > 0;
 select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0;
-select * from t1 where word=0xDF;
+select * from t1 where word=binary 0xDF;
 select * from t1 where word=CAST(0xDF as CHAR);
-select * from t1 where word2=0xDF;
+select * from t1 where word2=binary 0xDF;
 select * from t1 where word2=CAST(0xDF as CHAR);
 select * from t1 where word='ae';
 select * from t1 where word= 0xe4 or word=CAST(0xe4 as CHAR);
-select * from t1 where word between 0xDF and 0xDF;
+select * from t1 where word between binary 0xDF and binary 0xDF;
 select * from t1 where word between CAST(0xDF AS CHAR) and CAST(0xDF AS CHAR);
 select * from t1 where word like 'ae';
 select * from t1 where word like 'AE';
-select * from t1 where word like 0xDF;
+select * from t1 where word like binary 0xDF;
 select * from t1 where word like CAST(0xDF as CHAR);
 drop table t1;
diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index 0a95410e842..a5a7abe3b01 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -68,6 +68,20 @@ select grp,group_concat(c order by c) from t1 group by grp;
 set group_concat_max_len = 5;
 select grp,group_concat(c) from t1 group by grp;
 show warnings;
+set group_concat_max_len = 1024;
+
+# Test variable length
+
+drop table if exists T_URL;
+create table T_URL ( URL_ID int(11), URL varchar(80));
+drop table if exists T_REQUEST;
+create table T_REQUEST ( REQ_ID int(11), URL_ID int(11));
+insert into T_URL values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com');
+insert into T_REQUEST values (1,4), (5,4), (5,5);
+select REQ_ID, Group_Concat(URL) as URL from T_URL, T_REQUEST where 
+T_REQUEST.URL_ID = T_URL.URL_ID group by REQ_ID;
+drop table T_URL;
+drop table T_REQUEST;
 
 # Test errors
 
@@ -76,4 +90,4 @@ select group_concat(sum(a)) from t1 group by grp;
 --error 1054
 select grp,group_concat(c order by 2) from t1 group by grp;
 
-drop table if exists t1;
+drop table t1;
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 83d49743a4a..a898d3551d7 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -136,6 +136,60 @@ DROP TABLE t1;
 #
 # Test collation and coercibility
 #
+select POSITION(_latin1'B' IN _latin1'abcd');
+select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin);
+select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd');
+--error 1265
+select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin);
+--error 1265
+select POSITION(_latin1'B' IN _latin2'abcd');
+
+select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d');
+--fix this:
+--select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d' COLLATE latin1_bin);
+--select FIND_IN_SET(_latin1'B' COLLATE latin1_bin,_latin1'a,b,c,d');
+--error 1265
+select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin);
+--error 1265
+select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d');
+
+select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2);
+--fix this:
+--select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_bin,_latin1'd',2);
+--select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd' COLLATE latin1_bin,2);
+--error 1265
+select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
+--error 1265
+select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);
+
+select _latin1'B' between _latin1'a' and _latin1'c';
+select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c';
+select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c';
+select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin;
+--error 1268
+select _latin2'B' between _latin1'a' and _latin1'b';
+--error 1268
+select _latin1'B' between _latin2'a' and _latin1'b';
+--error 1268
+select _latin1'B' between _latin1'a' and _latin2'b';
+--error 1268
+select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';
+
+select _latin1'B' in (_latin1'a',_latin1'b');
+select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b');
+select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b');
+select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin);
+--error 1269
+select _latin2'B' in (_latin1'a',_latin1'b');
+--error 1269
+select _latin1'B' in (_latin2'a',_latin1'b');
+--error 1269
+select _latin1'B' in (_latin1'a',_latin2'b');
+--error 1269
+select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');
+--error 1269
+select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin);
+
 select collation(bin(130)), coercibility(bin(130));
 select collation(oct(130)), coercibility(oct(130));
 select collation(conv(130,16,10)), coercibility(conv(130,16,10));