From: Tor Didriksen Date: January 4 2011 9:56am Subject: bzr push into mysql-trunk branch (tor.didriksen:3453 to 3454) List-Archive: http://lists.mysql.com/commits/127869 Message-Id: <20110104095603.0A5E53776@atum07.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3454 Tor Didriksen 2011-01-04 [merge] Automerge opt-team => trunk modified: mysql-test/r/myisam_mrr_all.result mysql-test/r/myisam_mrr_cost_all.result mysql-test/r/order_by_all.result mysql-test/r/select_all.result mysql-test/r/select_all_jcl6.result mysql-test/r/select_icp_mrr_jcl6.result mysql-test/r/select_none_jcl6.result 3453 Marc Alff 2011-01-04 Removed experimental flag, now that bugs impactings the tests have been fixed. modified: mysql-test/collections/default.experimental === modified file 'mysql-test/r/myisam_mrr_all.result' --- a/mysql-test/r/myisam_mrr_all.result 2010-12-02 15:51:35 +0000 +++ b/mysql-test/r/myisam_mrr_all.result 2010-12-20 14:21:44 +0000 @@ -347,7 +347,7 @@ GROUP BY t2.pk ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition; Using where; Using filesort +2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition Warnings: Note 1003 select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0 DROP TABLE t1, t2; === modified file 'mysql-test/r/myisam_mrr_cost_all.result' --- a/mysql-test/r/myisam_mrr_cost_all.result 2010-12-02 15:51:35 +0000 +++ b/mysql-test/r/myisam_mrr_cost_all.result 2010-12-20 14:21:44 +0000 @@ -347,7 +347,7 @@ GROUP BY t2.pk ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition; Using where; Using filesort +2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition Warnings: Note 1003 select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0 DROP TABLE t1, t2; === modified file 'mysql-test/r/order_by_all.result' --- a/mysql-test/r/order_by_all.result 2010-11-30 13:55:22 +0000 +++ b/mysql-test/r/order_by_all.result 2010-12-20 14:21:44 +0000 @@ -1505,6 +1505,890 @@ ORDER BY t2.c LIMIT 1; d 52.5 DROP TABLE t1,t2,t3; +# +# WL#1393 - Optimizing filesort with small limit +# +CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200)); +INSERT INTO t1(f1, f2) VALUES +(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"), +(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"), +(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"), +(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"), +(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"), +(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"), +(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"), +(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"), +(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"), +(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"), +(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"), +(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"), +(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"), +(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"), +(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"), +(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"), +(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"), +(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"), +(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"), +(96,"96"),(97,"97"),(98,"98"),(99,"99"); +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 100; +f0 f1 f2 +1 0 0 +2 1 1 +3 2 2 +4 3 3 +5 4 4 +6 5 5 +7 6 6 +8 7 7 +9 8 8 +10 9 9 +11 10 10 +12 11 11 +13 12 12 +14 13 13 +15 14 14 +16 15 15 +17 16 16 +18 17 17 +19 18 18 +20 19 19 +21 20 20 +22 21 21 +23 22 22 +24 23 23 +25 24 24 +26 25 25 +27 26 26 +28 27 27 +29 28 28 +30 29 29 +31 30 30 +32 31 31 +33 32 32 +34 33 33 +35 34 34 +36 35 35 +37 36 36 +38 37 37 +39 38 38 +40 39 39 +41 40 40 +42 41 41 +43 42 42 +44 43 43 +45 44 44 +46 45 45 +47 46 46 +48 47 47 +49 48 48 +50 49 49 +51 50 50 +52 51 51 +53 52 52 +54 53 53 +55 54 54 +56 55 55 +57 56 56 +58 57 57 +59 58 58 +60 59 59 +61 60 60 +62 61 61 +63 62 62 +64 63 63 +65 64 64 +66 65 65 +67 66 66 +68 67 67 +69 68 68 +70 69 69 +71 70 70 +72 71 71 +73 72 72 +74 73 73 +75 74 74 +76 75 75 +77 76 76 +78 77 77 +79 78 78 +80 79 79 +81 80 80 +82 81 81 +83 82 82 +84 83 83 +85 84 84 +86 85 85 +87 86 86 +88 87 87 +89 88 88 +90 89 89 +91 90 90 +92 91 91 +93 92 92 +94 93 93 +95 94 94 +96 95 95 +97 96 96 +98 97 97 +99 98 98 +100 99 99 +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; +f0 f1 f2 +1 0 0 +2 1 1 +3 2 2 +4 3 3 +5 4 4 +6 5 5 +7 6 6 +8 7 7 +9 8 8 +10 9 9 +11 10 10 +12 11 11 +13 12 12 +14 13 13 +15 14 14 +16 15 15 +17 16 16 +18 17 17 +19 18 18 +20 19 19 +21 20 20 +22 21 21 +23 22 22 +24 23 23 +25 24 24 +26 25 25 +27 26 26 +28 27 27 +29 28 28 +30 29 29 +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; +f0 f1 f2 +100 99 99 +99 98 98 +98 97 97 +97 96 96 +96 95 95 +95 94 94 +94 93 93 +93 92 92 +92 91 91 +91 90 90 +10 9 9 +90 89 89 +89 88 88 +88 87 87 +87 86 86 +86 85 85 +85 84 84 +84 83 83 +83 82 82 +82 81 81 +81 80 80 +9 8 8 +80 79 79 +79 78 78 +78 77 77 +77 76 76 +76 75 75 +75 74 74 +74 73 73 +73 72 72 +SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; +f0 f1 f2 +12 11 11 +13 12 12 +14 13 13 +15 14 14 +16 15 15 +17 16 16 +18 17 17 +19 18 18 +20 19 19 +21 20 20 +22 21 21 +23 22 22 +24 23 23 +25 24 24 +26 25 25 +27 26 26 +28 27 27 +29 28 28 +30 29 29 +31 30 30 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; +f0 f1 f2 +22 21 21 +23 22 22 +24 23 23 +25 24 24 +26 25 25 +27 26 26 +28 27 27 +29 28 28 +30 29 29 +31 30 30 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; +f0 f1 f2 +set sort_buffer_size= 32768; +CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20)); +INSERT INTO tmp SELECT f1, f2 FROM t1; +INSERT INTO t1(f1, f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1, f2 FROM t1; +INSERT INTO t1(f1, f2) SELECT * FROM tmp; +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +3 2 2 +103 2 2 +203 2 2 +303 2 2 +403 2 2 +4 3 3 +104 3 3 +204 3 3 +304 3 3 +404 3 3 +5 4 4 +105 4 4 +205 4 4 +305 4 4 +405 4 4 +6 5 5 +106 5 5 +206 5 5 +306 5 5 +406 5 5 +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; +f0 f1 f2 +100 99 99 +200 99 99 +300 99 99 +400 99 99 +500 99 99 +99 98 98 +199 98 98 +299 98 98 +399 98 98 +499 98 98 +98 97 97 +198 97 97 +298 97 97 +398 97 97 +498 97 97 +97 96 96 +197 96 96 +297 96 96 +397 96 96 +497 96 96 +96 95 95 +196 95 95 +296 95 95 +396 95 95 +496 95 95 +95 94 94 +195 94 94 +295 94 94 +395 94 94 +495 94 94 +SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; +f0 f1 f2 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; +f0 f1 f2 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; +f0 f1 f2 +set sort_buffer_size= 32768; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 +ORDER BY f1, f0 LIMIT 30; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +3 2 2 +103 2 2 +203 2 2 +303 2 2 +403 2 2 +4 3 3 +104 3 3 +204 3 3 +304 3 3 +404 3 3 +5 4 4 +105 4 4 +205 4 4 +305 4 4 +405 4 4 +6 5 5 +106 5 5 +206 5 5 +306 5 5 +406 5 5 +SELECT FOUND_ROWS(); +FOUND_ROWS() +500 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 +ORDER BY f1, f0 LIMIT 0; +f0 f1 f2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +500 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 20; +f0 f1 f2 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +SELECT FOUND_ROWS(); +FOUND_ROWS() +445 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 0; +f0 f1 f2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +445 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 10 OFFSET 10; +f0 f1 f2 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +SELECT FOUND_ROWS(); +FOUND_ROWS() +445 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 0 OFFSET 10; +f0 f1 f2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +445 +set sort_buffer_size= 327680; +SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 +ORDER BY tmp.f1, f0 LIMIT 30; +f0 f1 f2 f1 f2 +1 0 0 0 0 +1 0 0 0 0 +1 0 0 0 0 +101 0 0 0 0 +101 0 0 0 0 +101 0 0 0 0 +201 0 0 0 0 +201 0 0 0 0 +201 0 0 0 0 +301 0 0 0 0 +301 0 0 0 0 +301 0 0 0 0 +401 0 0 0 0 +401 0 0 0 0 +401 0 0 0 0 +2 1 1 1 1 +2 1 1 1 1 +2 1 1 1 1 +102 1 1 1 1 +102 1 1 1 1 +102 1 1 1 1 +202 1 1 1 1 +202 1 1 1 1 +202 1 1 1 1 +302 1 1 1 1 +302 1 1 1 1 +302 1 1 1 1 +402 1 1 1 1 +402 1 1 1 1 +402 1 1 1 1 +SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 +ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; +f0 f1 f2 f1 f2 +3 2 2 2 2 +3 2 2 2 2 +3 2 2 2 2 +103 2 2 2 2 +103 2 2 2 2 +103 2 2 2 2 +203 2 2 2 2 +203 2 2 2 2 +203 2 2 2 2 +303 2 2 2 2 +303 2 2 2 2 +303 2 2 2 2 +403 2 2 2 2 +403 2 2 2 2 +403 2 2 2 2 +4 3 3 3 3 +4 3 3 3 3 +4 3 3 3 3 +104 3 3 3 3 +104 3 3 3 3 +104 3 3 3 3 +204 3 3 3 3 +204 3 3 3 3 +204 3 3 3 3 +304 3 3 3 3 +304 3 3 3 3 +304 3 3 3 3 +404 3 3 3 3 +404 3 3 3 3 +404 3 3 3 3 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 +ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; +f0 f1 f2 f1 f2 +3 2 2 2 2 +3 2 2 2 2 +3 2 2 2 2 +103 2 2 2 2 +103 2 2 2 2 +103 2 2 2 2 +203 2 2 2 2 +203 2 2 2 2 +203 2 2 2 2 +303 2 2 2 2 +303 2 2 2 2 +303 2 2 2 2 +403 2 2 2 2 +403 2 2 2 2 +403 2 2 2 2 +4 3 3 3 3 +4 3 3 3 3 +4 3 3 3 3 +104 3 3 3 3 +104 3 3 3 3 +104 3 3 3 3 +204 3 3 3 3 +204 3 3 3 3 +204 3 3 3 3 +304 3 3 3 3 +304 3 3 3 3 +304 3 3 3 3 +404 3 3 3 3 +404 3 3 3 3 +404 3 3 3 3 +SELECT FOUND_ROWS(); +FOUND_ROWS() +1500 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 +WHERE t1.f2>20 +ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; +f0 f1 f2 f1 f2 +24 23 23 23 23 +24 23 23 23 23 +24 23 23 23 23 +124 23 23 23 23 +124 23 23 23 23 +124 23 23 23 23 +224 23 23 23 23 +224 23 23 23 23 +224 23 23 23 23 +324 23 23 23 23 +324 23 23 23 23 +324 23 23 23 23 +424 23 23 23 23 +424 23 23 23 23 +424 23 23 23 23 +25 24 24 24 24 +25 24 24 24 24 +25 24 24 24 24 +125 24 24 24 24 +125 24 24 24 24 +125 24 24 24 24 +225 24 24 24 24 +225 24 24 24 24 +225 24 24 24 24 +325 24 24 24 24 +325 24 24 24 24 +325 24 24 24 24 +425 24 24 24 24 +425 24 24 24 24 +425 24 24 24 24 +SELECT FOUND_ROWS(); +FOUND_ROWS() +1185 +CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 30; +SELECT * FROM v1; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +3 2 2 +103 2 2 +203 2 2 +303 2 2 +403 2 2 +4 3 3 +104 3 3 +204 3 3 +304 3 3 +404 3 3 +5 4 4 +105 4 4 +205 4 4 +305 4 4 +405 4 4 +6 5 5 +106 5 5 +206 5 5 +306 5 5 +406 5 5 +drop view v1; +CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 100; +SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +11 10 10 +111 10 10 +211 10 10 +311 10 10 +411 10 10 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +CREATE VIEW v2 as SELECT * FROM t1 ORDER BY f2, f0 LIMIT 100; +SELECT * FROM v1 JOIN v2 on v1.f1=v2.f1 ORDER BY v1.f2,v1.f0,v2.f0 +LIMIT 30; +f0 f1 f2 f0 f1 f2 +1 0 0 1 0 0 +1 0 0 101 0 0 +1 0 0 201 0 0 +1 0 0 301 0 0 +1 0 0 401 0 0 +101 0 0 1 0 0 +101 0 0 101 0 0 +101 0 0 201 0 0 +101 0 0 301 0 0 +101 0 0 401 0 0 +201 0 0 1 0 0 +201 0 0 101 0 0 +201 0 0 201 0 0 +201 0 0 301 0 0 +201 0 0 401 0 0 +301 0 0 1 0 0 +301 0 0 101 0 0 +301 0 0 201 0 0 +301 0 0 301 0 0 +301 0 0 401 0 0 +401 0 0 1 0 0 +401 0 0 101 0 0 +401 0 0 201 0 0 +401 0 0 301 0 0 +401 0 0 401 0 0 +2 1 1 2 1 1 +2 1 1 102 1 1 +2 1 1 202 1 1 +2 1 1 302 1 1 +2 1 1 402 1 1 +SELECT floor(f1/10) f3, count(f2) FROM t1 +GROUP BY 1 ORDER BY 2,1 LIMIT 5; +f3 count(f2) +0 50 +1 50 +2 50 +3 50 +4 50 +SELECT floor(f1/10) f3, count(f2) FROM t1 +GROUP BY 1 ORDER BY 2,1 LIMIT 0; +f3 count(f2) +CREATE PROCEDURE wl1393_sp_test() +BEGIN +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 30; +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 15 OFFSET 15; +SELECT FOUND_ROWS(); +SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; +END| +CALL wl1393_sp_test()| +f0 f1 f2 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +16 15 15 +116 15 15 +216 15 15 +316 15 15 +416 15 15 +17 16 16 +117 16 16 +217 16 16 +317 16 16 +417 16 16 +f0 f1 f2 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +16 15 15 +116 15 15 +216 15 15 +316 15 15 +416 15 15 +17 16 16 +117 16 16 +217 16 16 +317 16 16 +417 16 16 +f0 f1 f2 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +16 15 15 +116 15 15 +216 15 15 +316 15 15 +416 15 15 +17 16 16 +117 16 16 +217 16 16 +317 16 16 +417 16 16 +FOUND_ROWS() +445 +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +11 10 10 +111 10 10 +211 10 10 +311 10 10 +411 10 10 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +DROP PROCEDURE wl1393_sp_test| +SELECT d1.f1, d1.f2 FROM t1 +LEFT JOIN (SELECT * FROM t1 ORDER BY f1 LIMIT 30) d1 on t1.f1=d1.f1 +ORDER BY d1.f2 DESC LIMIT 30; +f1 f2 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +4 4 +4 4 +4 4 +4 4 +4 4 +SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 1); +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 2); +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1, tmp; +DROP VIEW v1, v2; +# end of WL#1393 - Optimizing filesort with small limit +# +# Bug #58761 +# Crash in Field::is_null in field.h on subquery in WHERE clause +# +CREATE TABLE t1 ( +pk INT NOT NULL AUTO_INCREMENT, +col_int_key INT DEFAULT NULL, +col_varchar_key VARCHAR(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key,col_int_key) +); +INSERT INTO t1 VALUES (27,7,'x'); +INSERT INTO t1 VALUES (28,6,'m'); +INSERT INTO t1 VALUES (29,4,'c'); +CREATE TABLE where_subselect +SELECT DISTINCT `pk` AS field1 , `pk` AS field2 +FROM t1 AS alias1 +WHERE alias1 . `col_int_key` > 229 +OR alias1 . `col_varchar_key` IS NOT NULL +GROUP BY field1, field2 +; +SELECT * +FROM where_subselect +WHERE (field1, field2) IN ( +SELECT DISTINCT `pk` AS field1 , `pk` AS field2 +FROM t1 AS alias1 +WHERE alias1 . `col_int_key` > 229 +OR alias1 . `col_varchar_key` IS NOT NULL +GROUP BY field1, field2 +); +field1 field2 +27 27 +28 28 +29 29 +DROP TABLE t1; +DROP TABLE where_subselect; +# End of Bug #58761 CREATE TABLE t1 ( id1 INT NULL, id2 INT NOT NULL, === modified file 'mysql-test/r/select_all.result' --- a/mysql-test/r/select_all.result 2010-11-30 13:55:22 +0000 +++ b/mysql-test/r/select_all.result 2010-12-20 14:21:44 +0000 @@ -4876,7 +4876,7 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati DROP TABLE t1; End of 5.1 tests # -# Bug#45277: Lost HAVING clause led to a wrong result. +# Bug#45227: Lost HAVING clause led to a wrong result. # CREATE TABLE `CC` ( `int_nokey` int(11) NOT NULL, @@ -4905,7 +4905,7 @@ Warning 1292 Truncated incorrect DOUBLE Warning 1292 Truncated incorrect DOUBLE value: 'q' Warning 1292 Truncated incorrect DOUBLE value: 'm' DROP TABLE CC; -# End of test#45277 +# End of test#45227 # # Bug#54515: Crash in opt_range.cc::get_best_group_min_max on # SELECT from VIEW with GROUP BY === modified file 'mysql-test/r/select_all_jcl6.result' --- a/mysql-test/r/select_all_jcl6.result 2010-11-30 13:55:22 +0000 +++ b/mysql-test/r/select_all_jcl6.result 2010-12-20 14:21:44 +0000 @@ -4880,7 +4880,7 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati DROP TABLE t1; End of 5.1 tests # -# Bug#45277: Lost HAVING clause led to a wrong result. +# Bug#45227: Lost HAVING clause led to a wrong result. # CREATE TABLE `CC` ( `int_nokey` int(11) NOT NULL, @@ -4909,7 +4909,7 @@ Warning 1292 Truncated incorrect DOUBLE Warning 1292 Truncated incorrect DOUBLE value: 'q' Warning 1292 Truncated incorrect DOUBLE value: 'm' DROP TABLE CC; -# End of test#45277 +# End of test#45227 # # Bug#54515: Crash in opt_range.cc::get_best_group_min_max on # SELECT from VIEW with GROUP BY === modified file 'mysql-test/r/select_icp_mrr_jcl6.result' --- a/mysql-test/r/select_icp_mrr_jcl6.result 2010-11-30 13:55:22 +0000 +++ b/mysql-test/r/select_icp_mrr_jcl6.result 2010-12-20 14:21:44 +0000 @@ -4880,7 +4880,7 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati DROP TABLE t1; End of 5.1 tests # -# Bug#45277: Lost HAVING clause led to a wrong result. +# Bug#45227: Lost HAVING clause led to a wrong result. # CREATE TABLE `CC` ( `int_nokey` int(11) NOT NULL, @@ -4909,7 +4909,7 @@ Warning 1292 Truncated incorrect DOUBLE Warning 1292 Truncated incorrect DOUBLE value: 'q' Warning 1292 Truncated incorrect DOUBLE value: 'm' DROP TABLE CC; -# End of test#45277 +# End of test#45227 # # Bug#54515: Crash in opt_range.cc::get_best_group_min_max on # SELECT from VIEW with GROUP BY === modified file 'mysql-test/r/select_none_jcl6.result' --- a/mysql-test/r/select_none_jcl6.result 2010-11-29 13:04:34 +0000 +++ b/mysql-test/r/select_none_jcl6.result 2010-12-20 14:21:44 +0000 @@ -4879,7 +4879,7 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati DROP TABLE t1; End of 5.1 tests # -# Bug#45277: Lost HAVING clause led to a wrong result. +# Bug#45227: Lost HAVING clause led to a wrong result. # CREATE TABLE `CC` ( `int_nokey` int(11) NOT NULL, @@ -4908,7 +4908,7 @@ Warning 1292 Truncated incorrect DOUBLE Warning 1292 Truncated incorrect DOUBLE value: 'm' Warning 1292 Truncated incorrect DOUBLE value: 'j' DROP TABLE CC; -# End of test#45277 +# End of test#45227 # # Bug#54515: Crash in opt_range.cc::get_best_group_min_max on # SELECT from VIEW with GROUP BY No bundle (reason: useless for push emails).