summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--myisam/mi_check.c9
-rw-r--r--mysql-test/r/ctype_ucs.result21
-rw-r--r--mysql-test/r/func_gconcat.result9
-rw-r--r--mysql-test/r/func_group.result4
-rw-r--r--mysql-test/r/group_by.result9
-rw-r--r--mysql-test/r/ps.result47
-rw-r--r--mysql-test/r/subselect.result101
-rw-r--r--mysql-test/r/view.result14
-rw-r--r--mysql-test/t/ctype_ucs.test21
-rw-r--r--mysql-test/t/func_gconcat.test15
-rw-r--r--mysql-test/t/group_by.test12
-rw-r--r--mysql-test/t/ps.test52
-rw-r--r--mysql-test/t/subselect.test72
-rw-r--r--mysql-test/t/view.test18
-rw-r--r--mysys/my_chsize.c4
-rw-r--r--sql/field.h2
-rw-r--r--sql/filesort.cc1
-rw-r--r--sql/item.cc1
-rw-r--r--sql/item_sum.cc22
-rw-r--r--sql/mysql_priv.h3
-rw-r--r--sql/sql_lex.cc3
-rw-r--r--sql/sql_select.cc2
-rw-r--r--sql/sql_yacc.yy8
-rw-r--r--sql/unireg.cc39
24 files changed, 446 insertions, 43 deletions
diff --git a/myisam/mi_check.c b/myisam/mi_check.c
index a3195ebce7f..5a81d221d2a 100644
--- a/myisam/mi_check.c
+++ b/myisam/mi_check.c
@@ -1370,7 +1370,8 @@ int mi_repair(MI_CHECK *param, register MI_INFO *info,
param->temp_filename);
goto err;
}
- if (filecopy(param,new_file,info->dfile,0L,new_header_length,
+ if (new_header_length &&
+ filecopy(param,new_file,info->dfile,0L,new_header_length,
"datafile-header"))
goto err;
info->s->state.dellink= HA_OFFSET_ERROR;
@@ -2072,7 +2073,8 @@ int mi_repair_by_sort(MI_CHECK *param, register MI_INFO *info,
param->temp_filename);
goto err;
}
- if (filecopy(param, new_file,info->dfile,0L,new_header_length,
+ if (new_header_length &&
+ filecopy(param, new_file,info->dfile,0L,new_header_length,
"datafile-header"))
goto err;
if (param->testflag & T_UNPACK)
@@ -2440,7 +2442,8 @@ int mi_repair_parallel(MI_CHECK *param, register MI_INFO *info,
param->temp_filename);
goto err;
}
- if (filecopy(param, new_file,info->dfile,0L,new_header_length,
+ if (new_header_length &&
+ filecopy(param, new_file,info->dfile,0L,new_header_length,
"datafile-header"))
goto err;
if (param->testflag & T_UNPACK)
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 3b6bfa6d776..76fcbbeb9f8 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -755,6 +755,27 @@ select export_set(5, name, upper(name), ",", 5) from bug20536;
export_set(5, name, upper(name), ",", 5)
test1,TEST1,test1,TEST1,TEST1
'test\_2','TEST\_2','test\_2','TEST\_2','TEST\_2'
+CREATE TABLE t1 (
+status enum('active','passive') collate latin1_general_ci
+NOT NULL default 'passive'
+);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `status` enum('active','passive') character set latin1 collate latin1_general_ci NOT NULL default 'passive'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+ALTER TABLE t1 ADD a int NOT NULL AFTER status;
+CREATE TABLE t2 (
+status enum('active','passive') collate ucs2_turkish_ci
+NOT NULL default 'passive'
+);
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `status` enum('active','passive') character set ucs2 collate ucs2_turkish_ci NOT NULL default 'passive'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+ALTER TABLE t2 ADD a int NOT NULL AFTER status;
+DROP TABLE t1,t2;
select password(name) from bug20536;
password(name)
????????????????????
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 6617ccc671e..93925670d01 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -654,3 +654,12 @@ CHAR_LENGTH( GROUP_CONCAT(b) )
240001
SET GROUP_CONCAT_MAX_LEN = 1024;
DROP TABLE t1;
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (2,1), (1,2), (2,2), (1,3);
+SELECT GROUP_CONCAT(a), x
+FROM (SELECT a, GROUP_CONCAT(b) x FROM t1 GROUP BY a) AS s
+GROUP BY x;
+GROUP_CONCAT(a) x
+2 1,2
+1 2,3
+DROP TABLE t1;
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 98eb9e9e2b3..c6117053a60 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -860,8 +860,8 @@ 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 COUNT(DISTINCT t1.b))
-0
2
+1
SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a;
(SELECT COUNT(DISTINCT 12))
1
@@ -1013,7 +1013,7 @@ SELECT SQL_NO_CACHE
WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
FROM t1 t, t2 c WHERE t.a = c.b;
minid
-NULL
+1
DROP TABLE t1,t2;
create table t1 select variance(0);
show create table t1;
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index ef057e04d1a..5eb2e5744c1 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -821,12 +821,3 @@ a b real_b
68 France France
DROP VIEW v1;
DROP TABLE t1,t2;
-CREATE TABLE t1 (a INT, b INT, KEY(a));
-INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
-EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL a 5 NULL 4
-EXPLAIN SELECT a, SUM(b) FROM t1 IGNORE INDEX (a) GROUP BY a LIMIT 2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
-DROP TABLE t1;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 080187cfa7b..1727c4ddfbe 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -909,6 +909,53 @@ create temporary table if not exists t1 (a1 int);
execute stmt;
drop temporary table t1;
deallocate prepare stmt;
+CREATE TABLE t1(
+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 t1(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' );
+PREPARE STMT FROM
+"SELECT GROUP_CONCAT(Track SEPARATOR ', ') FROM t1
+ WHERE Member_ID=? AND Action='Enrolled' AND
+ (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t1
+ WHERE Member_ID=?
+ GROUP BY Track
+ HAVING Track>='CAD' AND
+ MAX(Action_Date)>'2006-03-01')";
+SET @id='111111';
+EXECUTE STMT USING @id,@id;
+GROUP_CONCAT(Track SEPARATOR ', ')
+NULL
+SET @id='222222';
+EXECUTE STMT USING @id,@id;
+GROUP_CONCAT(Track SEPARATOR ', ')
+CAD
+DEALLOCATE PREPARE STMT;
+DROP TABLE t1;
End of 4.1 tests
create table t1 (a varchar(20));
insert into t1 values ('foo');
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index c6a31856a24..6bf85979002 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1160,7 +1160,7 @@ Code2 char(2) NOT NULL default '',
PRIMARY KEY (Code)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU');
-INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ');
+INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ');
select t2.Continent, t1.Name, t1.Population from t2 LEFT JOIN t1 ON t2.Code = t1.t2 where t1.Population IN (select max(t1.Population) AS Population from t1, t2 where t1.t2 = t2.Code group by Continent);
Continent Name Population
Oceania Sydney 3276207
@@ -2512,7 +2512,7 @@ Code2 char(2) NOT NULL default ''
) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,68000,75.1,334.00,NULL,'Amerika Samoa','US Territory','George W. Bush',54,'AS');
-INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
+INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 WHERE Continent = c AND Population < 200);
@@ -2966,6 +2966,42 @@ ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
a a b
10 1 359
drop table t1,t2;
+CREATE TABLE t1 (
+field1 int NOT NULL,
+field2 int NOT NULL,
+field3 int NOT NULL,
+PRIMARY KEY (field1,field2,field3)
+);
+CREATE TABLE t2 (
+fieldA int NOT NULL,
+fieldB int NOT NULL,
+PRIMARY KEY (fieldA,fieldB)
+);
+INSERT INTO t1 VALUES
+(1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
+INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
+SELECT field1, field2, COUNT(*)
+FROM t1 GROUP BY field1, field2;
+field1 field2 COUNT(*)
+1 1 2
+1 2 3
+1 3 1
+SELECT field1, field2
+FROM t1
+GROUP BY field1, field2
+HAVING COUNT(*) >= ALL (SELECT fieldB
+FROM t2 WHERE fieldA = field1);
+field1 field2
+1 2
+SELECT field1, field2
+FROM t1
+GROUP BY field1, field2
+HAVING COUNT(*) < ANY (SELECT fieldB
+FROM t2 WHERE fieldA = field1);
+field1 field2
+1 1
+1 3
+DROP TABLE t1, t2;
create table t1 (df decimal(5,1));
insert into t1 values(1.1);
insert into t1 values(2.2);
@@ -3422,3 +3458,64 @@ id select_type table type possible_keys key key_len ref rows Extra
4 UNION t12 system NULL NULL NULL NULL 0 const row not found
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
DROP TABLE t1;
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 values (1);
+INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
+SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
+(SELECT COUNT(DISTINCT t1.b) from t2)
+2
+1
+1
+SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
+FROM t1 GROUP BY t1.a;
+(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
+2
+1
+1
+SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
+COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b))
+2 2
+1 1
+1 1
+SELECT COUNT(DISTINCT t1.b),
+(SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
+FROM t1 GROUP BY t1.a;
+COUNT(DISTINCT t1.b) (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
+2 2
+1 1
+1 1
+SELECT (
+SELECT (
+SELECT COUNT(DISTINCT t1.b)
+)
+)
+FROM t1 GROUP BY t1.a;
+(
+SELECT (
+SELECT COUNT(DISTINCT t1.b)
+)
+)
+2
+1
+1
+SELECT (
+SELECT (
+SELECT (
+SELECT COUNT(DISTINCT t1.b)
+)
+)
+FROM t1 GROUP BY t1.a LIMIT 1)
+FROM t1 t2
+GROUP BY t2.a;
+(
+SELECT (
+SELECT (
+SELECT COUNT(DISTINCT t1.b)
+)
+)
+FROM t1 GROUP BY t1.a LIMIT 1)
+2
+2
+2
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 9c05aba168c..7b81072cfd7 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2935,4 +2935,18 @@ id select_type table type possible_keys key key_len ref rows Extra
2 SUBQUERY t1 ALL NULL NULL NULL NULL 3
DROP VIEW v1;
DROP TABLE t1;
+CREATE TABLE t1(pk int PRIMARY KEY);
+CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int);
+CREATE ALGORITHM=MERGE VIEW v1 AS
+SELECT t1.*
+FROM t1 JOIN t2
+ON t2.fk = t1.pk AND
+t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org);
+SHOW WARNINGS;
+Level Code Message
+SHOW CREATE VIEW v1;
+View Create View
+v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk` from (`t1` join `t2` on(((`t2`.`fk` = `t1`.`pk`) and (`t2`.`ver` = (select max(`t`.`ver`) AS `MAX(t.ver)` from `t2` `t` where (`t`.`org` = `t2`.`org`))))))
+DROP VIEW v1;
+DROP TABLE t1, t2;
End of 5.0 tests.
diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test
index 8116d39e3db..6c814368c88 100644
--- a/mysql-test/t/ctype_ucs.test
+++ b/mysql-test/t/ctype_ucs.test
@@ -484,6 +484,27 @@ select make_set(3, name, upper(name)) from bug20536;
select export_set(5, name, upper(name)) from bug20536;
select export_set(5, name, upper(name), ",", 5) from bug20536;
+#
+# Bug #20108: corrupted default enum value for a ucs2 field
+#
+
+CREATE TABLE t1 (
+ status enum('active','passive') collate latin1_general_ci
+ NOT NULL default 'passive'
+);
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 ADD a int NOT NULL AFTER status;
+
+CREATE TABLE t2 (
+ status enum('active','passive') collate ucs2_turkish_ci
+ NOT NULL default 'passive'
+);
+SHOW CREATE TABLE t2;
+ALTER TABLE t2 ADD a int NOT NULL AFTER status;
+
+DROP TABLE t1,t2;
+
+
# Some broken functions: add these tests just to document current behavior.
# PASSWORD and OLD_PASSWORD don't work with UCS2 strings, but to fix it would
diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index 98c21986aa9..b5c468e1638 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -447,3 +447,18 @@ SELECT a, CHAR_LENGTH(b) FROM t1;
SELECT CHAR_LENGTH( GROUP_CONCAT(b) ) FROM t1;
SET GROUP_CONCAT_MAX_LEN = 1024;
DROP TABLE t1;
+
+#
+# Bug #22015: crash with GROUP_CONCAT over a derived table that
+# returns the results of aggregation by GROUP_CONCAT
+#
+
+CREATE TABLE t1 (a int, b int);
+
+INSERT INTO t1 VALUES (2,1), (1,2), (2,2), (1,3);
+
+SELECT GROUP_CONCAT(a), x
+ FROM (SELECT a, GROUP_CONCAT(b) x FROM t1 GROUP BY a) AS s
+ GROUP BY x;
+
+DROP TABLE t1;
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 8a514108dc3..ce1e4e59600 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -655,15 +655,3 @@ where t2.b=v1.a GROUP BY t2.b;
DROP VIEW v1;
DROP TABLE t1,t2;
-
-#
-# Bug #21174: Index degrades sort performance and
-# optimizer does not honor IGNORE INDEX
-#
-CREATE TABLE t1 (a INT, b INT, KEY(a));
-INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
-
-EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2;
-EXPLAIN SELECT a, SUM(b) FROM t1 IGNORE INDEX (a) GROUP BY a LIMIT 2;
-
-DROP TABLE t1;
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 5b2e37ecc94..cc239306558 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -988,6 +988,58 @@ execute stmt;
drop temporary table t1;
deallocate prepare stmt;
+#
+# BUG#22085: Crash on the execution of a prepared statement that
+# uses an IN subquery with aggregate functions in HAVING
+#
+
+CREATE TABLE t1(
+ 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 t1(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' );
+
+PREPARE STMT FROM
+"SELECT GROUP_CONCAT(Track SEPARATOR ', ') FROM t1
+ WHERE Member_ID=? AND Action='Enrolled' AND
+ (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t1
+ WHERE Member_ID=?
+ GROUP BY Track
+ HAVING Track>='CAD' AND
+ MAX(Action_Date)>'2006-03-01')";
+SET @id='111111';
+EXECUTE STMT USING @id,@id;
+SET @id='222222';
+EXECUTE STMT USING @id,@id;
+
+DEALLOCATE PREPARE STMT;
+DROP TABLE t1;
+
--echo End of 4.1 tests
############################# 5.0 tests start ################################
#
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index a4e535ac418..7811301a9bc 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -665,7 +665,7 @@ CREATE TABLE t2 (
) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU');
-INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ');
+INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ');
select t2.Continent, t1.Name, t1.Population from t2 LEFT JOIN t1 ON t2.Code = t1.t2 where t1.Population IN (select max(t1.Population) AS Population from t1, t2 where t1.t2 = t2.Code group by Continent);
@@ -1526,7 +1526,7 @@ CREATE TABLE t1 (
) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('XXX','Xxxxx','Oceania','Xxxxxx',26.00,0,0,0,0,0,'Xxxxx','Xxxxx','Xxxxx',NULL,'XX');
INSERT INTO t1 VALUES ('ASM','American Samoa','Oceania','Polynesia',199.00,0,68000,75.1,334.00,NULL,'Amerika Samoa','US Territory','George W. Bush',54,'AS');
-INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
+INSERT INTO t1 VALUES ('ATF','French Southern territories','Antarctica','Antarctica',7780.00,0,0,NULL,0.00,NULL,'Terres australes françaises','Nonmetropolitan Territory of France','Jacques Chirac',NULL,'TF');
INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','Micronesia/Caribbean',16.00,0,0,NULL,0.00,NULL,'United States Minor Outlying Islands','Dependent Territory of the US','George W. Bush',NULL,'UM');
/*!40000 ALTER TABLE t1 ENABLE KEYS */;
SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 WHERE Continent = c AND Population < 200);
@@ -1918,6 +1918,43 @@ SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
drop table t1,t2;
+#
+# Bug #21853: assert failure for a grouping query with
+# an ALL/ANY quantified subquery in HAVING
+#
+
+CREATE TABLE t1 (
+ field1 int NOT NULL,
+ field2 int NOT NULL,
+ field3 int NOT NULL,
+ PRIMARY KEY (field1,field2,field3)
+);
+CREATE TABLE t2 (
+ fieldA int NOT NULL,
+ fieldB int NOT NULL,
+ PRIMARY KEY (fieldA,fieldB)
+);
+
+INSERT INTO t1 VALUES
+ (1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
+INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
+
+SELECT field1, field2, COUNT(*)
+ FROM t1 GROUP BY field1, field2;
+
+SELECT field1, field2
+ FROM t1
+ GROUP BY field1, field2
+ HAVING COUNT(*) >= ALL (SELECT fieldB
+ FROM t2 WHERE fieldA = field1);
+SELECT field1, field2
+ FROM t1
+ GROUP BY field1, field2
+ HAVING COUNT(*) < ANY (SELECT fieldB
+ FROM t2 WHERE fieldA = field1);
+
+DROP TABLE t1, t2;
+
# End of 4.1 tests
#
@@ -2332,3 +2369,34 @@ explain select * from t1 where not exists
((select t11.i from t1 t11) union (select t12.i from t1 t12));
DROP TABLE t1;
+
+#
+# Bug #21540: Subqueries with no from and aggregate functions return
+# wrong results
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 values (1);
+INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
+SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
+SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
+ FROM t1 GROUP BY t1.a;
+SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
+SELECT COUNT(DISTINCT t1.b),
+ (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
+ FROM t1 GROUP BY t1.a;
+SELECT (
+ SELECT (
+ SELECT COUNT(DISTINCT t1.b)
+ )
+)
+FROM t1 GROUP BY t1.a;
+SELECT (
+ SELECT (
+ SELECT (
+ SELECT COUNT(DISTINCT t1.b)
+ )
+ )
+ FROM t1 GROUP BY t1.a LIMIT 1)
+FROM t1 t2
+GROUP BY t2.a;
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 4745804e847..b8cbb491832 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2850,4 +2850,22 @@ EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1);
DROP VIEW v1;
DROP TABLE t1;
+
+#
+# Bug #21646: view qith a subquery in ON expression
+#
+
+CREATE TABLE t1(pk int PRIMARY KEY);
+CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int);
+
+CREATE ALGORITHM=MERGE VIEW v1 AS
+SELECT t1.*
+ FROM t1 JOIN t2
+ ON t2.fk = t1.pk AND
+ t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org);
+SHOW WARNINGS;
+SHOW CREATE VIEW v1;
+
+DROP VIEW v1;
+DROP TABLE t1, t2;
--echo End of 5.0 tests.
diff --git a/mysys/my_chsize.c b/mysys/my_chsize.c
index 9760de29a08..fe0d0ffa607 100644
--- a/mysys/my_chsize.c
+++ b/mysys/my_chsize.c
@@ -46,7 +46,9 @@ int my_chsize(File fd, my_off_t newlength, int filler, myf MyFlags)
DBUG_PRINT("my",("fd: %d length: %lu MyFlags: %d",fd,(ulong) newlength,
MyFlags));
- oldsize = my_seek(fd, 0L, MY_SEEK_END, MYF(MY_WME+MY_FAE));
+ if ((oldsize = my_seek(fd, 0L, MY_SEEK_END, MYF(MY_WME+MY_FAE))) == newlength)
+ DBUG_RETURN(0);
+
DBUG_PRINT("info",("old_size: %ld", (ulong) oldsize));
if (oldsize > newlength)
diff --git a/sql/field.h b/sql/field.h
index 65e747e9d2f..b79c2bf77a8 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -1432,6 +1432,8 @@ public:
uint decimals, flags, pack_length, key_length;
Field::utype unireg_check;
TYPELIB *interval; // Which interval to use
+ TYPELIB *save_interval; // Temporary copy for the above
+ // Used only for UCS2 intervals
List<String> interval_list;
CHARSET_INFO *charset;
Field::geometry_type geom_type;
diff --git a/sql/filesort.cc b/sql/filesort.cc
index 42d25dbbaee..f13354d5c72 100644
--- a/sql/filesort.cc
+++ b/sql/filesort.cc
@@ -1268,6 +1268,7 @@ sortlength(THD *thd, SORT_FIELD *sortorder, uint s_length,
switch ((sortorder->result_type=sortorder->item->result_type())) {
case STRING_RESULT:
sortorder->length=sortorder->item->max_length;
+ set_if_smaller(sortorder->length, thd->variables.max_sort_length);
if (use_strnxfrm((cs=sortorder->item->collation.collation)))
{
sortorder->length= cs->coll->strnxfrmlen(cs, sortorder->length);
diff --git a/sql/item.cc b/sql/item.cc
index de78a2539a7..a0eef7a19e9 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1182,6 +1182,7 @@ void Item::split_sum_func2(THD *thd, Item **ref_pointer_array,
split_sum_func(thd, ref_pointer_array, fields);
}
else if ((type() == SUM_FUNC_ITEM || (used_tables() & ~PARAM_TABLE_BIT)) &&
+ type() != SUBSELECT_ITEM &&
(type() != REF_ITEM ||
((Item_ref*)this)->ref_type() == Item_ref::VIEW_REF))
{
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index bcd8270e52f..5ca1dbba94b 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -246,7 +246,27 @@ bool Item_sum::register_sum_func(THD *thd, Item **ref)
aggr_sl->inner_sum_func_list->next= this;
}
aggr_sl->inner_sum_func_list= this;
-
+ aggr_sl->with_sum_func= 1;
+
+ /*
+ Mark Item_subselect(s) as containing aggregate function all the way up
+ to aggregate function's calculation context.
+ Note that we must not mark the Item of calculation context itself
+ because with_sum_func on the calculation context st_select_lex is
+ already set above.
+
+ with_sum_func being set for an Item means that this Item refers
+ (somewhere in it, e.g. one of its arguments if it's a function) directly
+ or through intermediate items to an aggregate function that is calculated
+ in a context "outside" of the Item (e.g. in the current or outer select).
+
+ with_sum_func being set for an st_select_lex means that this st_select_lex
+ has aggregate functions directly referenced (i.e. not through a sub-select).
+ */
+ for (sl= thd->lex->current_select;
+ sl && sl != aggr_sl && sl->master_unit()->item;
+ sl= sl->master_unit()->outer_select() )
+ sl->master_unit()->item->with_sum_func= 1;
}
return FALSE;
}
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 2c8c7d7cc00..ff04022b110 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -446,7 +446,8 @@ enum enum_parsing_place
NO_MATTER,
IN_HAVING,
SELECT_LIST,
- IN_WHERE
+ IN_WHERE,
+ IN_ON
};
struct st_table;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index d0087b14d6a..788276ac654 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1710,7 +1710,8 @@ bool st_lex::can_be_merged()
unit= unit->next_unit())
{
if (unit->first_select()->parent_lex == this &&
- (unit->item == 0 || unit->item->place() != IN_WHERE))
+ (unit->item == 0 ||
+ (unit->item->place() != IN_WHERE && unit->item->place() != IN_ON)))
{
selects_allow_merge= 0;
break;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ac4b404ce8e..b328d9162d5 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -11735,8 +11735,6 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
We must not try to use disabled keys.
*/
usable_keys= table->s->keys_in_use;
- /* we must not consider keys that are disabled by IGNORE INDEX */
- usable_keys.intersect(table->keys_in_use_for_query);
for (ORDER *tmp_order=order; tmp_order ; tmp_order=tmp_order->next)
{
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 764b6dd53c1..cb105d05332 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -5212,11 +5212,13 @@ join_table:
/* Change the current name resolution context to a local context. */
if (push_new_name_resolution_context(YYTHD, $1, $3))
YYABORT;
+ Select->parsing_place= IN_ON;
}
expr
{
add_join_on($3,$6);
Lex->pop_context();
+ Select->parsing_place= NO_MATTER;
}
| table_ref STRAIGHT_JOIN table_factor
ON
@@ -5225,12 +5227,14 @@ join_table:
/* Change the current name resolution context to a local context. */
if (push_new_name_resolution_context(YYTHD, $1, $3))
YYABORT;
+ Select->parsing_place= IN_ON;
}
expr
{
$3->straight=1;
add_join_on($3,$6);
Lex->pop_context();
+ Select->parsing_place= NO_MATTER;
}
| table_ref normal_join table_ref
USING
@@ -5254,6 +5258,7 @@ join_table:
/* Change the current name resolution context to a local context. */
if (push_new_name_resolution_context(YYTHD, $1, $5))
YYABORT;
+ Select->parsing_place= IN_ON;
}
expr
{
@@ -5261,6 +5266,7 @@ join_table:
Lex->pop_context();
$5->outer_join|=JOIN_TYPE_LEFT;
$$=$5;
+ Select->parsing_place= NO_MATTER;
}
| table_ref LEFT opt_outer JOIN_SYM table_factor
{
@@ -5285,6 +5291,7 @@ join_table:
/* Change the current name resolution context to a local context. */
if (push_new_name_resolution_context(YYTHD, $1, $5))
YYABORT;
+ Select->parsing_place= IN_ON;
}
expr
{
@@ -5293,6 +5300,7 @@ join_table:
YYABORT;
add_join_on($$, $8);
Lex->pop_context();
+ Select->parsing_place= NO_MATTER;
}
| table_ref RIGHT opt_outer JOIN_SYM table_factor
{
diff --git a/sql/unireg.cc b/sql/unireg.cc
index 3a139aea4c7..93afd9c9e4e 100644
--- a/sql/unireg.cc
+++ b/sql/unireg.cc
@@ -228,13 +228,19 @@ bool mysql_create_frm(THD *thd, my_string file_name,
goto err3;
{
- /* Unescape all UCS2 intervals: were escaped in pack_headers */
+ /*
+ Restore all UCS2 intervals.
+ HEX representation of them is not needed anymore.
+ */
List_iterator<create_field> it(create_fields);
create_field *field;
while ((field=it++))
{
- if (field->interval && field->charset->mbminlen > 1)
- unhex_type2(field->interval);
+ if (field->save_interval)
+ {
+ field->interval= field->save_interval;
+ field->save_interval= 0;
+ }
}
}
DBUG_RETURN(0);
@@ -514,18 +520,36 @@ static bool pack_header(uchar *forminfo, enum db_type table_type,
reclength=(uint) (field->offset+ data_offset + length);
n_length+= (ulong) strlen(field->field_name)+1;
field->interval_id=0;
+ field->save_interval= 0;
if (field->interval)
{
uint old_int_count=int_count;
if (field->charset->mbminlen > 1)
{
- /* Escape UCS2 intervals using HEX notation */
+ /*
+ Escape UCS2 intervals using HEX notation to avoid
+ problems with delimiters between enum elements.
+ As the original representation is still needed in
+ the function make_empty_rec to create a record of
+ filled with default values it is saved in save_interval
+ The HEX representation is created from this copy.
+ */
+ field->save_interval= field->interval;
+ field->interval= (TYPELIB*) sql_alloc(sizeof(TYPELIB));
+ *field->interval= *field->save_interval;
+ field->interval->type_names=
+ (const char **) sql_alloc(sizeof(char*) *
+ (field->interval->count+1));
+ field->interval->type_names[field->interval->count]= 0;
+ field->interval->type_lengths=
+ (uint *) sql_alloc(sizeof(uint) * field->interval->count);
+
for (uint pos= 0; pos < field->interval->count; pos++)
{
char *dst;
- uint length= field->interval->type_lengths[pos], hex_length;
- const char *src= field->interval->type_names[pos];
+ uint length= field->save_interval->type_lengths[pos], hex_length;
+ const char *src= field->save_interval->type_names[pos];
hex_length= length * 2;
field->interval->type_lengths[pos]= hex_length;
field->interval->type_names[pos]= dst= sql_alloc(hex_length + 1);
@@ -777,7 +801,8 @@ static bool make_empty_rec(THD *thd, File file,enum db_type table_type,
field->charset,
field->geom_type,
field->unireg_check,
- field->interval,
+ field->save_interval ? field->save_interval :
+ field->interval,
field->field_name,
&table);
if (!regfield)
View Lorry Controller Status