Merge chilla.local:/home/mydev/mysql-5.0--main

into  chilla.local:/home/mydev/mysql-5.0-toteam


sql/opt_range.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged

11 files changed, 283 insertions, 15 deletions

diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 07edbf206fe..db73782fdbf 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -674,4 +674,10 @@ create table t1(f1 varchar(800) binary not null, key(f1)) engine = innodb
 insert into t1 values('aaa');
 drop table t1;
 
+#
+# Bug#21772: can not name a column 'upgrade' when create a table
+#
+create table t1 (upgrade int);
+drop table t1;
+
 # End of 5.0 tests
diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test
index 677ffaa2860..865e1746fd3 100644
--- a/mysql-test/t/delete.test
+++ b/mysql-test/t/delete.test
@@ -153,6 +153,16 @@ DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a LIMIT 1;
 SELECT * FROM t1;
 DROP TABLE t1;
 
+#
+# Bug #21392: multi-table delete with alias table name fails with 
+# 1003: Incorrect table name
+#
+
+create table t1 (a int);
+delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5;
+delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5;
+drop table t1;
+
 # End of 4.1 tests
 
 #
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index c2dd77e662a..079d107fad8 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -566,6 +566,19 @@ INSERT INTO t1 VALUES
 SELECT MAX(b) FROM t1;
 EXPLAIN SELECT MAX(b) FROM t1;
 DROP TABLE t1;
+#
+# Bug #16792  query with subselect, join, and group not returning proper values
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1),(1,2),(2,3);
+
+SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
+SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a;
+# an attempt to test all aggregate function with no table.
+SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12),
+       COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2),
+       GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);
+DROP TABLE t1;
 
 # End of 4.1 tests
 
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 8753db0ebe1..45415882ac7 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -753,4 +753,31 @@ select cast(rtrim(ltrim('  20.06 ')) as decimal(19,2));
 select conv("18383815659218730760",10,10) + 0;
 select "18383815659218730760" + 0;
 
+#
+# Bug #21698: substitution of a string field for a constant under a function 
+#
+
+CREATE TABLE t1 (code varchar(10));
+INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13');
+
+SELECT ASCII(code), code FROM t1 WHERE code='A12';
+SELECT ASCII(code), code FROM t1 WHERE code='A12' AND ASCII(code)=65;
+
+INSERT INTO t1 VALUES ('a12 '), ('A12  ');
+
+SELECT LENGTH(code), code FROM t1 WHERE code='A12';
+SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5;
+
+ALTER TABLE t1 ADD INDEX (code);
+CREATE TABLE t2 (id varchar(10) PRIMARY KEY);
+INSERT INTO t2 VALUES ('a11'), ('a12'), ('a13'), ('a14');
+
+SELECT * FROM t1 INNER JOIN t2 ON t1.code=t2.id 
+  WHERE t2.id='a12' AND (LENGTH(code)=5 OR code < 'a00');
+EXPLAIN EXTENDED 
+SELECT * FROM t1 INNER JOIN t2 ON code=id 
+  WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00');
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index be09b00ad46..7303be31e03 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -419,20 +419,20 @@ drop table t1;
 #
 # Bug#16377 result of DATE/TIME functions were compared as strings which
 #           can lead to a wrong result.
-#
+# Now wrong dates should be compared only with CAST()
 create table t1(f1 date, f2 time, f3 datetime);
 insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01");
 insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02");
-select f1 from t1 where f1 between "2006-1-1" and 20060101;
-select f1 from t1 where f1 between "2006-1-1" and "2006.1.1";
-select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1";
-select f2 from t1 where f2 between "12:1:2" and "12:2:2";
-select f2 from t1 where time(f2) between "12:1:2" and "12:2:2";
-select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2";
-select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2";
-select f1 from t1 where "2006-1-1" between f1 and f3;
-select f1 from t1 where "2006-1-1" between date(f1) and date(f3);
-select f1 from t1 where "2006-1-1" between f1 and 'zzz';
+select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);
+select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);
+select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
+select f2 from t1 where f2 between cast("12:1:2" as time) and cast("12:2:2" as time);
+select f2 from t1 where time(f2) between cast("12:1:2" as time) and cast("12:2:2" as time);
+select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
+select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
+select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;
+select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3);
+select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date);
 select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);
 select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);
 drop table t1;
diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
index eda768be1bc..b3813864464 100644
--- a/mysql-test/t/insert_update.test
+++ b/mysql-test/t/insert_update.test
@@ -115,4 +115,27 @@ INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a) ;
 
 DROP TABLE t1;
 
+#
+# Bug#21555: incorrect behavior with INSERT ... ON DUPL KEY UPDATE and VALUES
+#
+
+
 # End of 4.1 tests
+CREATE TABLE t1
+(
+  a   BIGINT UNSIGNED,
+  b   BIGINT UNSIGNED,
+  PRIMARY KEY (a)
+);
+
+INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b =
+  IF(VALUES(b) > t1.b, VALUES(b), t1.b);
+SELECT * FROM t1;
+INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE b =
+  IF(VALUES(b) > t1.b, VALUES(b), t1.b);
+SELECT * FROM t1;
+INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = 
+  IF(VALUES(b) > t1.b, VALUES(b), t1.b);
+SELECT * FROM t1;
+
+DROP TABLE t1;
diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test
index 6301cc0f584..63c611e6be6 100644
--- a/mysql-test/t/row.test
+++ b/mysql-test/t/row.test
@@ -92,3 +92,50 @@ SELECT ROW(NULL,10) <=> ROW(3,NULL);
 #
 SELECT ROW(1,1,1) = ROW(1,1,1) as `1`, ROW(1,1,1) = ROW(1,2,1) as `0`, ROW(1,NULL,1) = ROW(2,2,1) as `0`, ROW(1,NULL,1) = ROW(1,2,2) as `0`, ROW(1,NULL,1) = ROW(1,2,1) as `null` ;
 select row(NULL,1)=(2,0);
+
+#
+# Bug #16081: row equalities are to be used for query optimizations  
+#
+
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b));
+INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (1,2), (3,2), (3,3);
+
+EXPLAIN SELECT * FROM t1 WHERE a=3 AND b=2;
+EXPLAIN SELECT * FROM t1 WHERE (a,b)=(3,2);
+SELECT * FROM t1 WHERE a=3 and b=2;
+SELECT * FROM t1 WHERE (a,b)=(3,2);
+
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
+INSERT INTO t2 VALUES
+  (1,1,2), (3,1,3), (1,2,2), (4,4,2),
+  (1,1,1), (3,1,1), (1,2,1);
+
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b;
+EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b);
+SELECT * FROM t1,t2 WHERE t1.a=t2.a and t1.b=t2.b;
+SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b);
+
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2;
+EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2);
+SELECT * FROM t1,t2 WHERE t1.a=1 and t1.b=t2.b;
+SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2);
+
+EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1);
+SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1);
+
+EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1);
+SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1);
+
+EXPLAIN SELECT * FROM t2 WHERE a=3 AND b=2;
+EXPLAIN SELECT * FROM t2 WHERE (a,b)=(3,2);
+SELECT * FROM t2 WHERE a=3 and b=2;
+SELECT * FROM t2 WHERE (a,b)=(3,2);
+
+EXPLAIN SELECT * FROM t1,t2 WHERE t2.a=t1.a AND t2.b=2 AND t2.c=1;
+EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1));
+SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1));
+
+EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1);
+SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1);
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 4b0f463a9e3..4707a9b0d30 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -6323,6 +6323,73 @@ call bug21416()|
 drop procedure bug21416|
 
 #
+# BUG#21493: Crash on the second call of a procedure containing
+#            a select statement that uses an IN aggregating subquery  
+#
+
+CREATE TABLE t3 (
+  Member_ID varchar(15) NOT NULL,
+  PRIMARY KEY (Member_ID)
+)|
+
+CREATE TABLE t4 (
+  ID int(10) unsigned NOT NULL auto_increment,
+  Member_ID varchar(15) NOT NULL default '',
+  Action varchar(12) NOT NULL,
+  Action_Date datetime NOT NULL,
+  Track varchar(15) default NULL,
+  User varchar(12) default NULL,
+  Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update
+    CURRENT_TIMESTAMP,
+  PRIMARY KEY (ID),
+  KEY Action (Action),
+  KEY Action_Date (Action_Date)
+)|
+
+
+INSERT INTO t3(Member_ID) VALUES
+  ('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666')|
+
+INSERT INTO t4(Member_ID, Action, Action_Date, Track) VALUES
+  ('111111', 'Disenrolled', '2006-03-01', 'CAD' ),
+  ('111111', 'Enrolled', '2006-03-01', 'CAD' ),
+  ('111111', 'Disenrolled', '2006-07-03', 'CAD' ),
+  ('222222', 'Enrolled', '2006-03-07', 'CAD' ),
+  ('222222', 'Enrolled', '2006-03-07', 'CHF' ),
+  ('222222', 'Disenrolled', '2006-08-02', 'CHF' ),
+  ('333333', 'Enrolled', '2006-03-01', 'CAD' ),
+  ('333333', 'Disenrolled', '2006-03-01', 'CAD' ),
+  ('444444', 'Enrolled', '2006-03-01', 'CAD' ),
+  ('555555', 'Disenrolled', '2006-03-01', 'CAD' ),
+  ('555555', 'Enrolled', '2006-07-21', 'CAD' ),
+  ('555555', 'Disenrolled', '2006-03-01', 'CHF' ),
+  ('666666', 'Enrolled', '2006-02-09', 'CAD' ),
+  ('666666', 'Enrolled', '2006-05-12', 'CHF' ),
+  ('666666', 'Disenrolled', '2006-06-01', 'CAD' )|
+
+#--disable_warnings
+DROP FUNCTION IF EXISTS bug21493|
+#--enable_warnings
+
+CREATE FUNCTION bug21493(paramMember VARCHAR(15)) RETURNS varchar(45)
+BEGIN
+DECLARE tracks VARCHAR(45);
+SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM t4
+  WHERE Member_ID=paramMember AND Action='Enrolled' AND 
+        (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t4
+                                  WHERE Member_ID=paramMember GROUP BY Track);
+RETURN tracks;
+END|
+
+SELECT bug21493('111111')|
+SELECT bug21493('222222')|
+
+SELECT bug21493(Member_ID) FROM t3|
+
+DROP FUNCTION bug21493|
+DROP TABLE t3,t4|
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index ed122e9ff5a..a4e535ac418 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1892,6 +1892,32 @@ select * from t1 r1
           group by r2.retailerId);
 drop table t1;
 
+#
+# Bug #21180: Subselect with index for both WHERE and ORDER BY 
+#             produces empty result
+#
+create table t1(a int, primary key (a));
+insert into t1 values (10);
+
+create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
+insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
+
+explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+             ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
+SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+            ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
+
+explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+            ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
+SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+            ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
+
+drop table t1,t2;
+
 # End of 4.1 tests
 
 #
@@ -2280,3 +2306,29 @@ SELECT * FROM t1,t2
                     ORDER BY t1.t DESC LIMIT 1);
 
 DROP TABLE t1, t2;
+
+#
+# Bug#14654 : Cannot select from the same table twice within a UNION
+# statement 
+#
+CREATE TABLE t1 (i INT);
+
+(SELECT i FROM t1) UNION (SELECT i FROM t1);
+SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS 
+  (
+   (SELECT i FROM t1) UNION 
+   (SELECT i FROM t1)
+  );
+
+SELECT * FROM t1 
+WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
+
+#TODO:not supported
+--error 1064
+explain select ((select t11.i from t1 t11) union (select t12.i from t1 t12))
+  from t1;
+#supported
+explain select * from t1 where not exists 
+  ((select t11.i from t1 t11) union (select t12.i from t1 t12));
+
+DROP TABLE t1;
diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test
index 78bdd9b8a80..c6050753943 100644
--- a/mysql-test/t/type_date.test
+++ b/mysql-test/t/type_date.test
@@ -36,8 +36,8 @@ INSERT INTO t1 VALUES ( "2000-1-2" );
 INSERT INTO t1 VALUES ( "2000-1-3" );
 INSERT INTO t1 VALUES ( "2000-1-4" );
 INSERT INTO t1 VALUES ( "2000-1-5" );
-SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND "2000-1-4";
-SELECT * FROM t1 WHERE datum BETWEEN "2000-1-2" AND datum - INTERVAL 100 DAY;
+SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date);
+SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY;
 DROP TABLE t1;
 
 #
@@ -115,4 +115,11 @@ INSERT INTO t1 VALUES ('abc');
 SELECT * FROM t1;
 DROP TABLE t1;
 
+#
+# Bug#21677: Wrong result when comparing a DATE and a DATETIME in BETWEEN
+#
+create table t1(start_date date, end_date date);
+insert into t1 values ('2000-01-01','2000-01-02');
+select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_date and end_date;
+drop table t1;
 # End of 4.1 tests
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index edff38274c4..4745804e847 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2449,8 +2449,8 @@ INSERT INTO t1 VALUES
 
 CREATE VIEW v1 AS SELECT * FROM t1;
 
-SELECT * FROM t1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04';
-SELECT * FROM v1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04';
+SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE);
+SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE);
 
 DROP VIEW v1;
 DROP TABLE t1;
@@ -2833,5 +2833,21 @@ DROP FUNCTION f2;
 DROP VIEW v1, v2;
 DROP TABLE t1;
 
+#
+# Bug #5500: wrong select_type in EXPLAIN output for queries over views
+#
+
+CREATE TABLE t1 (s1 int); 
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+EXPLAIN SELECT * FROM t1;
+EXPLAIN SELECT * FROM v1;
 
+INSERT INTO t1 VALUES (1), (3), (2);
+
+EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); 
+EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); 
+
+DROP VIEW v1;
+DROP TABLE t1;
 --echo End of 5.0 tests.