From: Alexander Barkov
Date: May 5 2010 9:29am
Subject: bzr commit into mysql-trunk-bugfixing branch (bar:3033) Bug#52849 WL#2649
List-Archive: http://lists.mysql.com/commits/107428
X-Bug: 52849
Message-Id: <201005050929.o459TjN8013382@bar.myoffice.izhnet.ru>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============1988228899=="

--===============1988228899==
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:horst.hunger@stripped

 3033 Alexander Barkov	2010-05-05
      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-05-05 09:28:37 +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-05-05 09:28:37 +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-05-05 09:28:37 +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);


--===============1988228899==
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: 5d6a60171e3ac417a18f6cd7505fe5ef2096b05c
# timestamp: 2010-05-05 13:29:44 +0400
# base_revision_id: horst.hunger@stripped\
#   vn2klf6yszdzwo7l
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWZmFb4YABJ1fgFQweff//3/v
3+r////wYAon3l07mfQAAYQKAoAdHJ0oU4ZQU9U8aU09NTZTam1HpPJD1Bp6h6gDIMgBo0AGqnog
NPSPUNMgxBoDQ0AANADQAAOYTQGgNGjCNBiNMTJiaDCNAyAZMBIkSNTI9U3ohMyajAQ9GSbKMhkP
TUaGEDRoG1KapvUm9U09R5TQ00aPU0ZA0aaGTIyDIAAAEkgQARkaTJpk00AQTap4Qg2mmmIRpk/S
SwIUEnNbxnJkUybMP3OsZXNR3Awa8SL4SI89q6RGge/x6jLacfoevqnuzuUezIpzBuSzTKtBCxI7
ctqKxcKlHzjkLwCCxm72x5pyt8nE7FlTgn2sJdpsbuHvOiXe62PbpQFbCAkAt7BsIKjmu5f5yIpz
G4FJfOqKpRnFq4Q7lrJjbGXLJhDeokPbZMv4FDFgsUXAsw5RN8XjfbWWM4Ym6fmdpkfz1jzWPpoN
jtHwJyyw84uytXnKXHgInuhFUr4ZozghgUbglCiCSHxxCISB5UqBk1VBU8oeFOtGhvq/dx+NzXlM
542Te3nXj3nCUorQ+JPRYUwUjfNt+qlhXgM4g/02ynqVAyMq5RPkXDHzGHEPYE4q+qDyq9o2QSS2
HGHCFNwD6rqW82XC0cVfOl91ZTDOqneJCa31UhBAH7GwYMbbfXWsikjE6c/MLQJo84qNIsHTRnME
AucnzElt8/v9e7vEZHBMNVccFJXmti6a1oWAqOIliZHQX/fVtQSC1C7BdJ1I5gCpd20zLtJPEcnR
6nF6CuUBxnnd1nrbx8bZw7jgu+NObWtJ4BjsSyU1iYTITWaRaToIwVtZKvOe81cYoGaqioIIiSYR
E5vGPuIw4I0DgeIHwJjeA8EokRg1hzEVy/wRpmHWZjnPrKgUklJCjdEuVkQiSrg6AiAw4Jk8TA+p
lEzWFx8erZKhxgDDVjEDVAVkTtGQFA1zCXzYNLpYiwFEPdhxKKNWFTDf5DJxRiqgw1EKCL9CtYNe
dwdLSrwLyORkZgYGLi89wjXUrMVYC5CZWi9CEyS8vNDQQVhOGWIonOWdxySuIFHvpdREOUQ2xVwJ
zEiKJOKnmoLyYceWc8+xWTXTT5ELSWceVi3wu5I7CBEfnetJAtERHE8laMWlg6Rkdw6nk8dDy2kJ
OrKHpajNdFzs6eiYu8J/zibKXYvfVRQWD+Nk6xMhXlZSIfQU83cbBxxENhZ0hfvMAzXaAB7CM5si
soc6gZslMfWA80msibue0HG8pPfInJyByme+cjpOkppOBgra9O17nO1TBkaibd1bSBhhTBHMOKMj
b3l0cTSsFsBQ1D3J41xzmfSerR6Lilbm1EbRFpAkWGhDi28kWF5vLliY1V2umnoHvUAdnnJbiGJm
L6tNIlf+QzzohOdIilaGYeSEROQ8KprgjqIQHuEUGJhAgYkjbt0mkkWlZMXmkbq4e7fx/HyrEs4t
Sreshn7YjBmRCIQFG2bGm+UsjJEpcByLGwOtnmRmN62Fqkay6q3ChtpMirQPL0BfvSswWXGNTZam
xHANdAZzvOtGkeDht6DzHYiwaOIoF7jMtMvJjY/wFFrZgzh3rtAkFkrLIrvhBQXmagxrDSS1u7RD
qVslIcwZBYkLItFIj/IpgiRNR9vkRRBE6JKkrPIqca2QQa8+otIEgzAUoq1E6cUDggYTGw2ESrOv
uDUKsoFajEHodOmGRAiPRrGE0GaAf+ufcaGLxYGBSPPqpqSJX/w1VxUBRLaXioFYOQXDJgKg/ctK
h7GrOVkBhXlCGJiAU0l4yJIYiZ5qu9C2kQpLGLx44XCapBioe1/kbScdxEdqqUJGGJYeuSm0UGLH
BEKQfYqb34CI/cYy0BnKCtx8Cs/Q+B8D+rvbWU2FGYnPWIhEkL4n7+4hkFf5TzsOxcr60h5cI3/w
qPs8XL6T8KmIGsSpP2DOMjaez6UFmXxPzPp+ZwP0oPocp8iRnJvoMMdp5fkbUMuPYUCOooO029hj
x7F+2+2yR6gLe5ifiRMtRmOUyPMmNhSH+6qGt5zFBjA6Kj5r3zM9AOnPb9zwKSkrtHeghZb2+4aD
2hxZnPG3aVznK/I2COQnRFDbDWViLSkwXDkFseNWYlTa4vDgbwzbjvI6TgaBEj1LzdJSbOSZMjag
hMmmGRlTCRN1yFCuj2zSkcF3RsYD9ZyRvNQ8mv04K0KCPmyZQNAmLUSE4KjRvOtBI05EK6zwuJL/
t4jFx0FJQqmaZbUyDqgXMa31PwUR2mnGYpFoGYIlhRInQMwVqiDDxJ6RQG61QSdd1jGQinQb7GQz
O60txyj8DaUmq5REazkMdoWhq6D+V2LSHdebMJy5243NOHJ6eHJE3b0y5HumAcDoYXY0S8umckbe
c4nNa7YAyhmb+y+XSRQemRKkq4nau05TelM5HeydnYvd3Np5J6jiI1lB/G7QFO+QjgNqAnSYYQyZ
aCfoeomwUAtpz9+fRKjqne9TMMyGSMD0wNNWsR2O29BLeeET5Y2Lm9dB5iZcXNp7vWc1mk8x0eY4
bni9IcOwvzTmPZiaGBQII6payQsjlpKIkJLQgBKwiDHUNEtsghVBNTwQakwjQYGGTV3715iB3yEG
Ch0Jz9autQq2EMDAO9Xc84UzyX2caIHpB1VcqTZ6/6BayXhVTO5XMDQcntahk5eCvOggEzI6Akc1
K/AeJzBTKRtIBBRdbhCaY87BZt2TSUozEEwNPYcLKNehVpCTKx0zOWfMRmWFhaSeq3cySewkomJF
PYIMIsuiyAeKupFOtQRG6MlJTuknOtRnm0V1k5YsRFJuM5mL6g6+vqRAvYOsC82DGbQMQjj59766
xn2UhWYFPZox0AHib785Xpxt+jQttrE1CQ1pEExcMzaNSctcpsiMfA1sZfErzUnYChvIcUVXb8Ik
FMy5Xer9Zzpp1BhSdgyqVKcJzE7hzV7ueIaiZi8nv47XzDSdPhuNGbeX8t2BxmLPO8NIW+zT322A
5r1XEeXlDabbfHfSVRdvQraaVOU0xUwS8UPoWaGhuVqSuDB6pRcOMMHLLIF+Ac+6eo9iFoVHgJr2
CpqviOV9BsvozuBumo5bu1MYBaFQkwn0Dq2/u6JUaQjSOEM4gs7KuTkegngeEy5ZjAVC1k2BCBJw
OJMi6tPNOvdOMx0Mdhopz+l/vXo2XrxpsKSMmFVyucOvu3ysQy/PXMpUtQaMZfKzlubQjYWYuKpq
OfXcvvYogVJ6xMSzFgqnweYDDshyX2t1DcnGjUQIPGg920gRiBfbNiks6zEMtivUwhuMa4cRM5Zu
lX+LuSKcKEhMwrfDAA==


--===============1988228899==--