From: Alexander Barkov Date: March 18 2011 12:46pm Subject: bzr commit into mysql-trunk branch (alexander.barkov:3302) Bug#58329 Bug#11765369 List-Archive: http://lists.mysql.com/commits/133297 X-Bug: 58329,11765369 Message-Id: <201103181246.p2ICkKSr017121@bar.myoffice.izhnet.ru> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============8808552847492111221==" --===============8808552847492111221== 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.b58329/ based on revid:alexander.nozdrin@stripped 3302 Alexander Barkov 2011-03-18 Bug#11765369 - Bug#58329: BETWEEN DOES NOT USE INDEXES FOR DATE OR DATETIME FIELDS FOR UCS2+ Problem: WHERE date_column = 'ucs2 literal' was rewritten to WHERE CONVERT(date_column USING ucs2) = 'ucs2 literal' so index on date_column did not work any more. Fix: rewrite to WHERE date_column = CONVERT('ucs2 literal' USING latin1) modified: @ mysql-test/r/ctype_ucs.result @ mysql-test/t/ctype_ucs.test Adding tests @ sql/item_cmpfunc.cc Adding special character set aggregation rule for DATE/TIME modified: mysql-test/r/ctype_ucs.result mysql-test/t/ctype_ucs.test sql/item_cmpfunc.cc === modified file 'mysql-test/r/ctype_ucs.result' --- a/mysql-test/r/ctype_ucs.result 2011-03-04 14:59:32 +0000 +++ b/mysql-test/r/ctype_ucs.result 2011-03-18 12:43:19 +0000 @@ -4684,5 +4684,18 @@ DO CAST(CONVERT('' USING ucs2) AS UNSIGN Warnings: Warning 1292 Truncated incorrect INTEGER value: '' # +# Bug#11765369 - 58329: BETWEEN DOES NOT USE INDEXES FOR DATE OR DATETIME FIELDS FOR UCS2+ +# +SET NAMES utf8, character_set_connection=ucs2; +CREATE TABLE t1 (id INT DEFAULT NULL, date_column DATE DEFAULT NULL, KEY (date_column)) engine=myisam; +INSERT INTO t1 VALUES (1,'2010-09-01'), (2,'2010-10-01'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE date_column= '2010-09-01'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref date_column date_column 4 const 1 100.00 +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`date_column` AS `date_column` from `test`.`t1` where (`test`.`t1`.`date_column` = '2010-09-01') +DROP TABLE t1; +SET NAMES latin1; +# # End of 5.6 tests # === modified file 'mysql-test/t/ctype_ucs.test' --- a/mysql-test/t/ctype_ucs.test 2011-03-04 14:59:32 +0000 +++ b/mysql-test/t/ctype_ucs.test 2011-03-18 12:43:19 +0000 @@ -779,5 +779,15 @@ DO IFNULL(CHAR(NULL USING ucs2), ''); DO CAST(CONVERT('' USING ucs2) AS UNSIGNED); --echo # +--echo # Bug#11765369 - 58329: BETWEEN DOES NOT USE INDEXES FOR DATE OR DATETIME FIELDS FOR UCS2+ +--echo # +SET NAMES utf8, character_set_connection=ucs2; +CREATE TABLE t1 (id INT DEFAULT NULL, date_column DATE DEFAULT NULL, KEY (date_column)) engine=myisam; +INSERT INTO t1 VALUES (1,'2010-09-01'), (2,'2010-10-01'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE date_column= '2010-09-01'; +DROP TABLE t1; +SET NAMES latin1; + +--echo # --echo # End of 5.6 tests --echo # === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2011-03-17 11:33:17 +0000 +++ b/sql/item_cmpfunc.cc 2011-03-18 12:43:19 +0000 @@ -484,10 +484,31 @@ void Item_bool_func2::fix_length_and_dec DTCollation coll; if (args[0]->result_type() == STRING_RESULT && - args[1]->result_type() == STRING_RESULT && - agg_arg_charsets_for_comparison(coll, args, 2)) - return; - + args[1]->result_type() == STRING_RESULT) + { + if (args[0]->is_datetime() || args[1]->is_datetime()) + { + /* + For DATE/TIME we always convert to my_charset_numeric (latin1), + to make this: + WHERE date_column = 'ucs2 literal'; + + rewrite to + WHERE date_column = CONVERT('ucs2 literal' USING latin1); + + instead of + WHERE CONVERT(date_column USING ucs2) = 'ucs2 literal'; + + This makes it possible to use index on date_column for optimization. + */ + coll.set(&my_charset_numeric); + if (agg_item_set_converter(coll, func_name(), args, 2, 0, 1)) + return; + } + else if (agg_arg_charsets_for_comparison(coll, args, 2)) + return; + } + args[0]->cmp_context= args[1]->cmp_context= item_cmp_type(args[0]->result_type(), args[1]->result_type()); // Make a special case of compare with fields to get nicer DATE comparisons --===============8808552847492111221== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/alexander.barkov@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: alexander.barkov@stripped\ # 04nv7f0pr7hoq043 # target_branch: file:///home/bar/mysql-bzr/mysql-trunk.b58329/ # testament_sha1: 9216df111609036d0fa00f9e35cff86ad504767c # timestamp: 2011-03-18 15:46:20 +0300 # base_revision_id: alexander.nozdrin@stripped\ # j5ln8oj7htz8pdzy # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWfkf6BAABCpfgFUwWf///3/v 3+r////+YAmH33z3e7daaFtowsT3c4XtoUHRoAQ0RMQU0eJD1NPUZA8mKep6jQYg0AA9INAAJKag m0amE0yI9QE0DI9TQMTT1NNGgGgBkMhzRo0NMIBpgTTQBkNDEAaMRoYIyACRERU81MQaNIMJ5Qek B6gNMgAyaAANAMlRpo9QAhgAm0TIYAAAmTEYIwTTAkkJo0AIBMARkRkE2kj1HpNNDTRsoAPSaLIg LOTO6sfWmNPZkH0xalW26lj+uk6dxkklA0bSAj1e8flwTxaz/6h99NE7SpYAg5rdMJ3FY0NNGxwU Qtogr1AKqtg3XSMyTFdzjWeEwBx3mipnQs1km4QIeJWkHblcJBMgTANQn4EUMNjpDgx8wiGmWZjl AiIhRFe24DzaKaLMp15m9Umqq0694ndYTGf45dJtPIpGZV+sxdsmUvHGrBqTYPYiFzxZyFoKmwMK phReUug4y+qrMYy8yJxOfvWNv9VHX0McNYMQGHYEwNNxa2KS/uNP7S84CmhJ37TqpH/jePWVg5Sw m+Esp8j5DiEhYlDVNiMyEN5q44jm3RYmymCkbOCkyVgOYEkwRXrAMG8B064EqKZhjJJJKDMOcuCQ Obt0JFbrqe/vdQuqMiMhpiDszz4Un20KSbP2nyMZrR4+snNtKzrzVQwJkqi4NABPOflbPEHAsU5m aM2FoOjIb9ZHStfYokIXJeyKFlCdAdr1rClg9+3sXeW30qWalsqbB2qkD4sNZw1LaChWfPTnDyBZ n3adBKqV4kgAYYpcMDAE6+giwhOI0yKTXSVlVFCKhXB6U0y/17PP/hydEpntyERGH3dNOkM1Lx6z 1TQCZLZFj0mUfjmJUoHzYoVrhhwlua5g3NWpQCbVBlhyqZ+JInRcpzadjPgrW1QmLWRjdrGUD6WT P1A2cqXByFtOvJ4jsIZic2G0wXEcF67C7ZRAVJpMDqA73puBgYB7EmALzWSWiwRrKXIZlZSGd4rT DhWH0+JUajQZzRWcLd5d0cDkyuuH3TgPJ3CURETkFM3bL5amWKaGKuZpcyjiJh/X1kvGYeardnsi GRkLEjARMa5ELqwau0tmVjJgcQMNg2898wGlZgjr4RGQl67Lnk9Eh9hKhfnIiVz+iB6ZYbiJE21l xxceeD/cdqjpuwLnKrGnBCQKcqw7VxFGPlz2KMnotRAx98L7JiFLU5ykcxjjZeVFgxOpsEWTyRec DUSLuIhxkN4jQvIvzYcyd5kIL1MIxDVRFzmfofMNkcZcpVI0mXLJxqXRTQVdCKhFg+nAwiFc2qcw mJGqp1F8VOsDUMMlpqlDIW4d6u5Erc0yy6MQ6pq6InHrRZEBNJQ7HULRVtfW33bsy4sKUMBQnX0A 7Ri8dcuRRVa0kuBWQUM/ArsysWZWJpHCHCcpxBYcSOAaPwEmjHpjTbUr7g7WVaKnKqBIi8MGmqFw fv/AtHL9ach4oBB48rgWn5Jg+ioK1QfQB5iFR1L5KkKZQmW+oKssOo9FQRJEfTci0DOqEQzkyYKX kQrw+xBOrBjKgeWmZDA6NOJgYYBk4TgjB8C1QOoHFSxJysUxeXzxIIAPPzrkVkyQyZWjBFXKwsKg zwCEYkS4eGQLU8HRClBeMuE6agzPWidUEwThGCB4YJ35MyIgeqIFU0vQfmbUEnAMEgY/cqM9kViu hLXQsV8rYKogtAvHZelDVA85qNsHyfSL/g7W+X5xmIDkIPEerkJjLpMsZyjDHKc3pnqovlMXPaCM dBO0Qry7VOeMWY3TkBybrcpMTzYCt/sQElmCsQcEZW76x8dyFpmW/MakIb70ddu7Anq2Hz5/gMdO MwcjE7TccxxroQojpy4cUmQdI/HjgXj2PCbyEO5vccwvsqMYYA/5n34mZ8auK5qIUQZAPDmc3JBU gMzl8ZOqEFJJMN3+Fw5zjmTK1M0fJbNOnaTmpxRmRAIoduHwQaj7S0WYcOHH3VxjOErMClAZHGgf vBtXjraGhKVlSSpMTspkSBGK2BK3oHLpOrAIbcoWy8jSmrCbURCrpQ71FXTvxLdSxWw8e5locyQZ wrFoA0aCb17jOZCUrN1PoKT4l1thA3jbw3qdWxpZyw2Wp2Y89Dj/ONs6psupDN3FQZ0yNkCJmcMg NzkGxuNe8pNmXshOCZrETpOGckVFyAglOo4HdJZRaNQjpE7O4dUTBuCgkGDea3rsXj1NJwgehjr4 Dto+sH9ZxqA1cdrZnaUqWTqDfm60TEzLl1YbkAwqcdh8Nsy2iLbBltGCUtWRjMPr3CsZZH6CTHgl oGvhh4u7MiQRIiRkVrWJ5iWTlrfZdy1xqxZgYbWeBxPQeJtFjgx7fXpOzyFN5fwa5Q1HnQuS5PJc djOOtTektzsKc93nQJOL1QleVgzWBAT+Dwzb2Xh6l4uyIB4nRGv87ACgTZlxpvUnIpvLp0g4y6dI ICRAc5FZlUikiFR2vOxqqZkg8r6pTSDKFc8fz+Y4juLS+5wFQw0uBqUaDEY1gLCxFicLJcrUadgm gGG0Wwqhq6Z8hElHas880JJMyPcWH1CIKYt5V9i8Fxcr8lvF1D8Zz61sEiDJBAtmZBzgia8aNgi3 uY/2gLxW5vGZfVMmNf26O3ISMyiyRMXvVcUF/BkyHokXrQkSvjkDylL+9hJsCIRYE844RTbYZTP5 yxK42mMfgcx5NWuLjkwB62MRpCIuLnICdMJ4vJndo7SWxQdwe33e/s9W4IMT01uQ5Y2J/aptI8Sc teUf6fxzbKtGedby5V3y0j1fzg0Z6NR6BA9wMROf990C6XlmwNeO7Ku9Q4snqsrE5e2wvRt3yJ6T zXNmZmtm0pzEjYZdZvchNiJwm2mTQ2zZHDCmeeZw4z1YvNOAH6Q7j67qlJMUdRNfY4KmtvIySCD0 ZdxLF7iAqdoaRxgGNQwn7h2SF1eBoNsyVpGhdzGIsymyuvhVXBLEeZtJIDuWNpvmA/sK49xf76D7 Av4YoxNSQWkETcI6PI3Fy6wLqW7ywQ8SpHIs9+oXqiPOh3xeWXjTDUKfocR5tJcOEEBeZhyX3ezS PbIx4j8UxqJEWeJHgNw0SqSnKnCsfDULTsrNp6WG/4u5IpwoSHyP9AgA --===============8808552847492111221==--