From: Alexander Barkov Date: April 29 2010 5:46pm Subject: bzr commit into mysql-trunk-bugfixing branch (bar:3026) Bug#52849 WL#2649 List-Archive: http://lists.mysql.com/commits/106972 X-Bug: 52849 Message-Id: <201004291747.o3THl1Yx023094@bar.myoffice.izhnet.ru> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1014699735==" --===============1014699735== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/bar/mysql-bzr/mysql-trunk-bugfixing/ based on revid:sven.sandberg@stripped 3026 Alexander Barkov 2010-04-29 Bug#52849 [Com]: datetime index not work Problem: after introduction of "WL#2649 Number-to-string conversions" This query: SET NAMES cp850; -- Or any other non-latin1 ASCII-based character set SELECT * FROM t1 WHERE datetime_column='2010-01-01 00:00:00' started to add extra character set conversion: SELECT * FROM t1 WHERE CONVERT(datetime_column USING cp850)='2010-01-01 00:00:00'; so index on DATETIME column was not used anymore. Fix: avoid convertion of NUMERIC/DATETIME items (i.e. those with derivation DERIVATION_NUMERIC). modified: mysql-test/r/type_datetime.result mysql-test/t/type_datetime.test sql/item.cc === modified file 'mysql-test/r/type_datetime.result' --- a/mysql-test/r/type_datetime.result 2010-03-24 15:03:44 +0000 +++ b/mysql-test/r/type_datetime.result 2010-04-29 17:46:33 +0000 @@ -638,3 +638,22 @@ CAST(CAST('2008-07-29T10:42:51.1234567' Warnings: Warning 1292 Truncated incorrect datetime value: '2008-07-29T10:42:51.1234567' End of 5.1 tests +# +# Start of 5.5 tests +# +# +# Bug#52849 datetime index not work +# +CREATE TABLE t1 (Id INT, AtTime DATETIME, KEY AtTime (AtTime)); +SET NAMES CP850; +INSERT INTO t1 VALUES (1,'2010-04-12 22:30:12'), (2,'2010-04-12 22:30:12'), (3,'2010-04-12 22:30:12'); +EXPLAIN EXTENDED SELECT * FROM t1 FORCE INDEX(attime) WHERE AtTime = '2010-02-22 18:40:07'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref AtTime AtTime 9 const 1 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`Id` AS `Id`,`test`.`t1`.`AtTime` AS `AtTime` from `test`.`t1` FORCE INDEX (`attime`) where (`test`.`t1`.`AtTime` = '2010-02-22 18:40:07') +DROP TABLE t1; +SET NAMES latin1; +# +# End of 5.5 tests +# === modified file 'mysql-test/t/type_datetime.test' --- a/mysql-test/t/type_datetime.test 2009-02-13 18:07:03 +0000 +++ b/mysql-test/t/type_datetime.test 2010-04-29 17:46:33 +0000 @@ -446,3 +446,21 @@ SELECT CAST(CAST('00000002006-000008-000 SELECT CAST(CAST('2008-07-29T10:42:51.1234567' AS DateTime) AS DECIMAL(30,7)); --echo End of 5.1 tests + +--echo # +--echo # Start of 5.5 tests +--echo # + +--echo # +--echo # Bug#52849 datetime index not work +--echo # +CREATE TABLE t1 (Id INT, AtTime DATETIME, KEY AtTime (AtTime)); +SET NAMES CP850; +INSERT INTO t1 VALUES (1,'2010-04-12 22:30:12'), (2,'2010-04-12 22:30:12'), (3,'2010-04-12 22:30:12'); +EXPLAIN EXTENDED SELECT * FROM t1 FORCE INDEX(attime) WHERE AtTime = '2010-02-22 18:40:07'; +DROP TABLE t1; +SET NAMES latin1; + +--echo # +--echo # End of 5.5 tests +--echo # === modified file 'sql/item.cc' --- a/sql/item.cc 2010-04-19 08:27:46 +0000 +++ b/sql/item.cc 2010-04-29 17:46:33 +0000 @@ -1796,6 +1796,24 @@ bool agg_item_set_converter(DTCollation &dummy_offset)) continue; + /* + No needs to add converter if an "arg" is NUMERIC or DATETIME + value (which is pure ASCII) and at the same time target DTCollation + is ASCII-compatible. For example, no needs to rewrite: + SELECT * FROM t1 WHERE datetime_field = '2010-01-01'; + to + SELECT * FROM t1 WHERE CONVERT(datetime_field USING cs) = '2010-01-01'; + + TODO: avoid conversion of any values with + repertoire ASCII and 7bit-ASCII-compatible, + not only numeric/datetime origin. + */ + if ((*arg)->collation.derivation == DERIVATION_NUMERIC && + (*arg)->collation.repertoire == MY_REPERTOIRE_ASCII && + !((*arg)->collation.collation->state & MY_CS_NONASCII) && + !(coll.collation->state & MY_CS_NONASCII)) + continue; + if (!(conv= (*arg)->safe_charset_converter(coll.collation)) && ((*arg)->collation.repertoire == MY_REPERTOIRE_ASCII)) conv= new Item_func_conv_charset(*arg, coll.collation, 1); --===============1014699735== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/bar@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: bar@stripped # target_branch: file:///home/bar/mysql-bzr/mysql-trunk-bugfixing/ # testament_sha1: 52ad75d41c0833d0871b787761c48f20b2082274 # timestamp: 2010-04-29 21:47:01 +0400 # base_revision_id: sven.sandberg@stripped\ # bmuxjeswfp4s69rt # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWWQ3TikABKRfgFQweff//3/v 3+r////wYAon29O65uhQAGpAtpYh0AAdBXcNJPSaaamUwNJtT0TyaepA2kGnqANA0AGmg0CUhI2i bSekYjTQUeSb1R5NEzTUNBoaPKeo9QBoHMJoDQGjRhGgxGmJkxNBhGgZAMmAlNEUanpGU8k2U8U8 U9TwptT0nlPUHqHqNHqANGmgNBzCaA0Bo0YRoMRpiZMTQYRoGQDJgJJAgAmTQRiMgARtUaYiekbQ TaEDan6UOiwgiLRa7izZjVLoGl534XefxqGGJDXzFuE07QtoGf50jPXXBgY055J14PdUU5VsV3Ul lTXCHRGy5aMki4RmXpUjJgCCkzeDZ905W+TgsqcE21hLuOVqfGEDom8bvGXfpQFrCAMQE8WXEgPP PPs7+lBvuNANHm5crF9wk+oPqmBEJIRaqnUGacCuokXsDOhseGfGFV5Sd1q7S6bRKpYody/M0mE/ lgwrGRxGNdPxGjnHQJk0uSYyjzWSsQv1a+y4dop5IbyeWYqIVqVHhP3g8hqfpjxskLUi+l906mhw ZqqKwYsUwvc18qaBOpTpRG6FvwhiSUJnr3KSrMFkM4g/jbKepUDI01yifqXDHyGHEPEJxV9cHlV7 RsrR3XVqLKDqUBOTkS+yTpeFPDkcrvyFCd81YwQcyQHsBAIC8yQIEJJL6WW2NDGlUbto3ASYzjT1 FQ3z9pgcG3EtpDY93q8mTKPsTkg5G1uUFn2Vht9luCoFc5iM5pNxf91W1BILULuF1HYjcAVL51iN pmXeTa+pcmxheQJbcAsjauw2q6+u60HGqCKp6igdzBiRKUhkRkMIby8x1CZMvhv3B0xlJbfyR5q8 10CTlfzoTnt7I+8jDgjgOB6AfAmOcB4JRIpg8Agtn8kKCQ6u8+k4oJKRZUTWKvlLHTvIjx4TKAmU GrcojHwEa7io/Tt66KKXmIOcOsGNbxPPAZAUDXqAOCixS71Ak4kAOSs9NMXLcXiodhChaRUI4HVE FBHQqxDBy4uDraNcM5eUDCJtRqMwMFx9ojlr3CGK8laC5TJXi80JkloPT1RgrScNWgWwsgtMarSJ P8Zk6MQvTbVVOQEYkRTip3bC8kOPTA9dsrmcTTaC0RF+A4qFNZz3x5SJAE/H1Dx4xNz1Ei0zjtJ4 FGBVr9mh5dUeYhiyqlLIxwZs6xFSYeqf+8RyFspfi99VCRWUq0h120KZaDWKgrHU83idQ5axHSs9 mJsDNvMgwudmNsFQauCwifRjWbSg5+E1gPO05iB1zG0oLiYkYX0EtBpKqjUai5qtL2aLhDBoNJHd iOOyjAkYYV4HuUkbSBcW5eRwjkTiI2rkKyT2Hlxmx6eQ86TbqJqRGCrJDy1Diu4uJi43GsxpraMp h8AbCsnJRUbTaW3ZOJBd+Y0DhDpKVYzGo8qoWhMytIQIJExmMIQMJECJEwNREKCBYYjgWKf1b9u3 h+PpWJYC1Kt5kMPsReh8AkKOJYo1MltKBEERmNjakB0IyMbzHWCmgR47bYicmcAw+2g7/gGzlfM4 mZ3ioMVQYRxMF8A/E+Z+yLTrTpKlbz6sVDi0kxtxvayX2QkLqCdOBz3B+2+wEBVLJVG4+tyZfR2R CbTQRb6eEPi1YiBSBDiTxaS0UiPzFMESJqPf6EUQROiSpKz0KnHIyRefQWgCIZgKUVaidOKhwQMZ jlOUiVYL7Q1CrKBWozg9Dp0wyIER6OQYTQZoB/259xkxeLExKR59FNSRK/9GquKgKJbS8VArByC4 ZMBUH+y0qHsasCsgMK8oQxMQCmkvGRJDETCarxQtpEKSxi8eOFwmqQZ1D3v9DaTjukR3yuzg6ZtM Rp+cNJMTNWtx3aA6CulmcHf9P4TqELgERJ5jtHHE958D/PuSEbiKEWwgE4n47CCkI/znnYin4PVt SQ8sEc38qnz+py/affUxA1CVJ/AYDIVm3hEOwbjeZNxmPpEcDIby1wCw7+4yIKTdYVChpxMcCWF/ RYn3jTM5ds6gHeNQ1RoLRQ4pKyktnMmsechSH+a6mu3mSDKB0VnyXxmZ6AdOe77ngUlJXaPrJcVf R5xShSuJPAuTFba/EXDYMUGYvFLDjFDGHRLCYCwMWTlLjZsA2KBWClrpdNA5EYqsBnMoXMpzE8Bn Jng5Sgx4ZEh8jjsiTINiOFBBJWIGcuBPgdxwgOwibj9uknIXZ/rVgTEdehlAziYvRITg1mW870Ej RpIV1nXcSX/NDHSUE6pZtqmTJkHNAtY5H5PgNBaKMZigWQzBErMpFCBmCtfyqYsQEoJFIc9ygk6/ vGKcjfYyGZ3elsHYG0nNOsWwkWJRrgQgXGj7LEVoDHVGs1YjcmEyFlSSG0YZFC/gTLle4JgHOhnv yol6aZyRu4HWc1rtgDKGZsV8uoighQU9B2m5KPg1zFju5sdE1BwEaSg+ezMFO2QjnGzgTpMMIZMs Sfe9RNQoBbTh3YZ5UdM73qZhmQyRicYGirUI7Hcd5LaeET5Z7F2+3JUnkTrg5tPd7TdbpPE6PE4c zxceHEvzTmWcyYE8ejqjyEhaDZ2Js7OSZ4135esoboS9c4y9bryuRJatvHn5mIHfIQZlDoTn6lda hVsIYGAd9fe830zyXHoogeYOqrlrVQ3t/8C1kvGqmdyuYGg5Pa1DEkz1ccw8JmRzBE30r4jxOBgp lI2EAi1mE00x6mCzZyTSL0zKkZJpmT0VLso2YoGOn8OZ57RGi1WJfYcSR0fqwEmQDmWYmZKlkLye 6CUkdZmI8aRWP7FivCKzutRjNorrJyxZCKlUc5gZi+kRWHb29iImZg7QMxsGKMhiEcvXvfXWM+yk KzOU92jPoAOJv4iM2JZpyu/dnLoZetk5ItGIC8vARE4GiL+QonEefcy5fZetjJ7SCgG+0cUVX8M8 SCmZbnez+Jzqp1hjSd4yoPFM4VbFCxanZ+S0FRComGzWVp4IGioVxUZzBWk+gvZZqSxpK9gagr/f N2MzANBe3FG/3BwZmZuOxJWmPK4RN9heKaCX2IfQw9Zmyba2YrgwO9kouHGOOnSC/AOjnnrPehZK r1Ca9gqar9ByzUGblbNS5wN2VGy7gmMQtCoSYT6B1bfe6JUaAjSOEM4gsGVcnI4k8DymWyYxFQuY mxIQJOBxJkXVp5o5Nk4zHQx2mVOH1P+KeWt6bW20YhseubVgz60rCuDCbSJHJfFINMRdPQ/MIXmk qlPz6rrUx8GOSI5ZGRZlEcDAYiK588SAw99tZDN6VnFey03iRKBSh8JInMC72Kttma0RdsAabHmi Z6Bz/F3JFOFCQZDdOKQ= --===============1014699735==--