From: Alexander Barkov Date: April 28 2010 12:13pm Subject: bzr commit into mysql-trunk-bugfixing branch (bar:3025) Bug#52849 WL#2649 List-Archive: http://lists.mysql.com/commits/106806 X-Bug: 52849 Message-Id: <201004281213.o3SCDEG0021345@bar.myoffice.izhnet.ru> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0300366395==" --===============0300366395== 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:dao-gang.qu@stripped 3025 Alexander Barkov 2010-04-28 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-28 12:04:20 +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-28 12:04:20 +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-28 12:04:20 +0000 @@ -1796,6 +1796,20 @@ 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'; + */ + 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); --===============0300366395== 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: 06c214585ac918e7ca131f2e58af832cd6006a7b # timestamp: 2010-04-28 16:13:14 +0400 # base_revision_id: dao-gang.qu@stripped\ # ic2sj19infpe2ep5 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWWjMdr4ABJHfgFQweff//3/v 3+r////wYAnPl7dN2AABpCCWWqGig0AoCSUT0mk2kEz1R+TTVP1PKT2iGptT1D9UYmjaT1Gam1PQ jEDVT1GIwnojIAAGEAGgAA0ANABzCaA0Bo0YRoMRpiZMTQYRoGQDJgJEhTU2mmqbEIegjARtTaGo GTQ00aGEBo0HMJoDQGjRhGgxGmJkxNBhGgZAMmAkkEaAJiABGgJhDIU9CeowTTymQwZQ49DJHhvf daND8FHgedt2uGe57YDDG3spfKJH4nYyRju76zXhKwp6ujomjhzqv0b97qzaaKN171BZJ718dWhU AymZs7YbZFb5nEWLKnA/ZBwl2mxqe6EDml3Yym7NCFUwhPEFM0qDaLN3N45UQ/Y5AhJjQ882cUeM O1SJhmYZgqPuG1SUnGNktPGoYtfWphCqKTij0snXhLXA7+58T0Gk/jsHmwfTQbXaPwJFlh4C7q1e by48Cx6MNfb7+wZgRQJDcFiwhID96QiEceYzQrKHykXltMp/c0a4JChKAHUZ/fzL2d5zzTRWL4kq LCmCmOM+76LGvEZz/XTPSpDI01XRPiXDHyGHEPAJCu6oPKr2jfr2zwX9ianv4u8vsdTVu/rePPw5 ovXbEbD5N8RGayXlGIHxtgwY22+nAuJPoyNDsWCuFpGITtogM7uomeBcxasNu0Uc3R0U1D2ZnEA0 L0uJGH96hfbgohcdxVnNRyL/sqyQTBahdgug6kcACpfxvJxGZdpr6PJ16CzyVlKmIGNDvA9zS87p B3HdDXmYwXeGxLQrqkyGEN4ew6U+gXw5cQ6ozzL8UZlcaZCTlfzIXEQ+EzvZP9RPCdG8cDxA+BI4 nBhQBKYmGDuCC2/uQpJh0ytvPpCgTFIhkKRa8ntVFhZJwwTpwmUzVuXwJjXcVERH5dvVZU4xBhrR jU8T+ljvGQFA3yucIIj8M4q8hMImD678+wq15VmGkJ3lGSqDVMzQhfJHeNte7sGXY01eR/KjibDY WgwYG4+tTGwzD0oWK/TJXIW40KYRmF5ITJKIjSfP1T7SZbycNusWBfVKNx4lDAcJ1qPwrm3lVAYo Ta1c8pJyIpC0GBpN+cxXqtzqepnktY+E445xTc1l8QgOLzQQxpHjxicrMCastKDwKM+zw9ojPAzW BPddW9KszRZsVMiRm9Mv8xHJIyNmRXF7txRTWizrdStJUKgqHUc3gdY5ZqbzaFvT0GIX2u3vWYRo eazksPpA1iIlhI1877AebToKj75BvKTExzcCGoiSkcik1JguGL2BxFGg16iIuJ20dpEwwZqoI4Gk rVfiZ58SDq1yMFqIQYgXGGHbrPXUcNJPUQKjJDhwxcWlpcSOJkY0Qmnqc5pOTgRqFdkzRpaWkK3o HQ2aFNs8ZdU3M1hHi/NeEbC2OYaGukiQMjUTEjIuMxePJKHX28uPR87kKwVapesRhw/Yi9EIhMKN k/imb3E4w8e/rOFb7A6mbiOY17wVFazPQWoUbaTItumTlDHjlVBU42VNl6bEZTyLlDnO06kWndEN nMdaKF/zFBchzLAaaY2P8BSvVg5w7V2CkFRnlQsPdBQ02YhjVRGh2Xg6FWSkOYMguSFpF8Vwp2iq ChQ0n1fMiiCJImVJWfMqca2QQa4+gs4pgzCpRVpJJxQOCB+05tNpEqwX1hqFWUCtRkD0OkmGRAiP RsGE0GaAfzc+5XCwMCgefRTUESr/pqqioCiWUvFQLUOQXDJhVB+paVD2NWBWQGFeUIYnIBTSXjIm QxEmp8ELeQCgrYuHjhbFVaDEp8cv6apnEbQjeVqhIyRLe6ZKbRQy8EEQpB1lre3ARH7jgM4rDOGD PYWfIgofE6j/W+u8XayU455AX6H6++GcKfxlJnfBYPNCvsSIFwjj+6q9fm5dsNMTFoiQtjUG6MjA WfqvZl/JOONNBqfNJOSXINJWeWcgQy/GotnqGP2X8RViO6eK76SUC9jYiLxSXVbM/5kAznimNZSH yok1vSZIMoHPUfJfdOz0A6Xv/F6VZWWWkZSd/d4xyz3rUcSHmW7qOBk0SRII0SoRQb+kvWcWlw2w zFLaYvDccQ28TuI5HORO/oJHh4pkyNWCBk01wssYSm+9IULZPFfNWxTMhvGJ3S6zy7R5DDP5q8Jy NVzKBaJi9EwnBUazPznagnNGkhZYYm7MeIiS/aLHSUlCqZplwTJb03ZEvY3P1PgNrpiUI1DMoVmr USQMwVqiDD0J4jIQ1Ic16ik7N2jFMjnrZDO7EuBxH4HAKDVtJjUY6zfC8EmE/q4lGGTbJq4S+9Ub 7Qpk2HhrmcU1Jk8r7RT5wHujblNH59s5McejtONjtwDKF7e1fHsIoIUFPUcjelHwa5rMV6IZcpU8 hGkpP33YBviI5hs4E4mGEMmWRO5QNQnhX2K/Huwzz09VD3qdkmSMfXA0XZRDZZR2DI47N6JYmWW4 chbVLzR47Ce9GZCrjKaHxe7n8i/NIxxMmBPHo6Y6yYWg9fYsnOSZ4134eoobkl6pDL1OvK5plq3+ fFPPKIgvUOSc/SrbAVTCGBgPd6HHNTKdefKiHkDqq5qTV7v/AtRN31UycrmTQcFqGUFccB4TMjgE TmoPuHicwUTTG0gKKZmswnmFXs1xpL0zKgZJgcFJ1S24IGOX29z503iIz2K8XpJ01/owLjQEMzyp iraFkcxXEbRgKstYr/UsV6ILJ1oY0Z66yRYs4jQqjgYmBmrDr6+pEMzB1mSYWBYxhnGITZ/Hi+yw Z9vYIqC0zlUNHMI0aQD2HPmxPBW69F/+mcuhl4snJFwxAXfA5aIP2EpxFYa/ZatjJ7RCTfMdK3nw gWKdlud5fpI56dIYUnaMUJkVMScOavjA52NJOxeX9NQjdXIad1GPAz5uJm234nTOWeLw0BV+d51M A0FtXsxRx9wczMzN6d6SuMtzhE/mZhTwS80Pwfobg2A+oPKaLhxjq1AvuDm5p6T3IWKp7xNcwVNV +Q5XUGzdRg4G66jdd6ExiFoVITFK7Fj90UvGkqVkCHBNYWrLkI4DOmclS06sQqFwJ8SECZwOJmRd WnmjZxkMx0seS7jRVjD716tt69sk5Hne7N4aeWweIY2aT2mCwGD1GwuPffxBF2k+0RVPRz68V8G2 wHLIzlmdgpnyeZDDvIcl9jdg2zq1vH0jQe7gQIxA1Wz5JK8fqtDqfTP1HNPW7/4u5IpwoSDRmO18 --===============0300366395==--