MySQL Bugs: #55779: select does not work properly in mysql server Version "5.1.42 SUSE MySQL RPM"

Bug #55779	select does not work properly in mysql server Version "5.1.42 SUSE MySQL RPM"
Submitted:	5 Aug 2010 11:49	Modified:	7 Oct 2010 23:05
Reporter:	Welf Wustlich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.39+	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	regression

Description:

Data (ttime is a timestamp):
+---------------------+
| ttime               |
+---------------------+
| 2009-12-24 20:53:24 |
| 2009-12-24 20:53:24 |
| 2009-12-24 20:53:24 |
| 2009-12-24 20:53:44 |
| 2009-12-24 20:54:44 |
| 2009-12-24 20:54:44 |
+---------------------+

The following error does NOT occur in older versions of mysqld (e.g. 5.1.38)
select does NOT provide the correct result set when using the statement:

select ttime from mytable where ttime >= '20091224205324';
+---------------------+
| ttime               |
+---------------------+
| 2009-12-24 20:53:44 |
| 2009-12-24 20:54:44 |
| 2009-12-24 20:54:44 |
+---------------------+
==> FALSE
Remark: The strange thing is, that only exactly this statement goes wrong (only with the new mysqld - version)

EXPECTED WAS
+---------------------+
| ttime               |
+---------------------+
| 2009-12-24 20:53:24 |
| 2009-12-24 20:53:24 |
| 2009-12-24 20:53:24 |
| 2009-12-24 20:53:44 |
| 2009-12-24 20:54:44 |
| 2009-12-24 20:54:44 |
+---------------------+

select ttime from mytable where ttime >= 20091224205324;
==> CORRECT

select ttime from mytable where ttime >= '2009-12-24-20:53:24';
==> CORRECT

select ttime from mytable where ttime='20091224205324';
+---------------------+
| ttime               |
+---------------------+
| 2009-12-24 20:53:24 |
| 2009-12-24 20:53:24 |
| 2009-12-24 20:53:24 |
+---------------------+
==> CORRECT

select ttime from fh_aud_nzd_ticl2 where ttime>'20091224205324'
+---------------------+
| ttime               |
+---------------------+
| 2009-12-24 20:53:44 |
| 2009-12-24 20:54:44 |
| 2009-12-24 20:54:44 |
+---------------------+
==> CORRECT

Short Summary:
select ttime from mytable where ttime >= '20091224205324'; // FALSE
select ttime from mytable where ttime = '20091224205324'; // CORRECT
select ttime from mytable where ttime >= 20091224205324; // CORRECT
select ttime from mytable where ttime >= '2009-12-24 20:53:24'; // CORRECT

How to repeat:
install mysqld Version "5.1.42 SUSE MySQL RPM"

create a short table with Timestamp field (multiple key)

perform above mentioned select statements

Thank you for writing us.

Can you please be more specific about which MySQL version are you using?
Are you using 32 or 64 bit? Binaries provided by MySQL or by SuSE ?

I tried with the generic Linux Community Edition mysql-5.1.42-linux-i686-glibc23 and I am not able to reproduce the issue:

CREATE TABLE mytable (ttime TIMESTAMP);
INSERT INTO mytable VALUES ('2009-12-24 20:53:24'), ('2009-12-24 20:53:24'), ('2009-12-24 20:53:24'), ('2009-12-24 20:53:44'), ('2009-12-24 20:54:44'), ('2009-12-24 20:54:44');

select ttime from mytable where ttime >= '20091224205324';
+---------------------+
| ttime               |
+---------------------+
| 2009-12-24 20:53:24 |
| 2009-12-24 20:53:24 |
| 2009-12-24 20:53:24 |
| 2009-12-24 20:53:44 |
| 2009-12-24 20:54:44 |
| 2009-12-24 20:54:44 |
+---------------------+
6 rows in set (0.00 sec)

Thanks to Val and Rene for trying to reproduce the issue, here are the additional information (I just found out) which are necessary to reproduce the issue:
The mysqld-version is the 64-bit version
The point is: The Timestamp field has to be a multiple key, otherwise the bug does not occur.
Please try:
CREATE TABLE mytable (ttime TIMESTAMP, KEY (ttime) );

INSERT INTO mytable VALUES ('2009-12-24 20:53:24'), ('2009-12-24
20:53:24'), ('2009-12-24 20:53:24'), ('2009-12-24 20:53:44'), ('2009-12-24 20:54:44'), ('2009-12-24 20:54:44');

select ttime from mytable where ttime = '20091224205324';

select ttime from mytable where ttime >= '20091224205324';

select ttime from mytable where ttime >= 20091224205324;

The mysqld - version what has the bug is:

mysqld  Ver 5.1.42 for suse-linux-gnu on x86_64 (SUSE MySQL RPM)
Copyright (C) 2000-2008 MySQL AB, by Monty and others
Copyright (C) 2008 Sun Microsystems, Inc.

Thank you for the feedback.

But version 5.1.42 is old and several timestamp related bugs were fixed since. Please upgrade to current version 5.1.49, try with it and if problem still exists indicate timezone you are using (output of query SHOW VARIABLES LIKE 'time_zone'; and UNIX command locale)

Hi Sveta,

version 5.1.42 is the newest version what is installed by yast (installation tool from OpenSuse). Maybe I could download a newer RPM anywhere, but usually Im using only standard ways to update my system.
If you have a newer version installed and you are not able to reproduce the bug, an information would be nice, could save me some trouble.
It might be, that any timestamp related bugs could cause that problem.
The problem can not have a reason in any locale or timezone settings, I do not need to check this.

Im fine by myself, using an older mysqld.
The reason why I have reported the bug, is, that this could cause some serious trouble in some application, because you get wrong results without having an error message, for us, this was critical for a productive software. I do now have a solution, but Im afraid, others could get into the same trouble.

Best regards, Welf

Thank you for the feedback.

I forgot to mention in previous comment: problem is not repeatable in my environment too. You can install our linux *tar.gz package. You can install in separate directory, then remove it without making any harm to your standard environment. We don't fix bugs repeatable only in ports anyway.

Hello Sveta,

thank you for your comments and hints.
I have tried to follow your instructions to verify the problem with 
mysql-5.1.49-linux-x86_64-icc-glibc23
downloading and unpacking the corresponding package.

Unfortunately it does not run an my systems:
> ./bin/mysqld: error while loading shared libraries: libstdc++.so.5: cannot open shared object file: No such file or directory

and other error messages.

Since I have a solution with the older server and you say, that you dont have the bug in the brandnew system, I assume, the problem is solved.

You really made sure, that the ttime field is a multiple key (see my last comments with the exact queue of instructions to reproduce the bug?

Thanks for looking into the problem, best regards, Welf

Thank you for the feedback.

Please use *glibc23* packages.

How can I use glibc23 packages without taking risk to destroy my proper running operating system?

Sveta, your wrote:
"Please use glibc23 packages."

You also wrote:
"You can install our linux *tar.gz package. You can
install in separate directory, then remove it without making any harm
to your standard environment."

Thank you for the feedback.

In the same way as I described in comment "[9 Aug 18:37] Sveta Smirnova" Just take *glibc23* tar.gz package instead of *icc* one

Well, I had not realized the diff between 
  mysql-5.1.49-linux-x86_64-glibc23.tar.gz
and
  mysql-5.1.49-linux-x86_64-icc-glibc23.tar.gz
both have a glibc23.tar.gz at the end

Now I have tried the first version, same result, it does not run, just the error messages have changed (see at the end of the message)

It was my intention to help, but now I have already spent much more time than my job does allow me ... I really have to finish with that try and error.
It seems Im too stupid to solve that problem ;-(.

./bin/mysqld --console --skip-external-locking
100811 13:32:58 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
100811 13:32:58 [ERROR] Can't find messagefile '/usr/local/mysql/share/english/errmsg.sys'
100811 13:32:58 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

100811 13:32:58 [ERROR] Aborting

100811 13:32:58 [Note]

Well, finally I was successful with running the new Version:
  mysql-5.1.49-linux-x86_64-glibc23

Also the new version has the described bug, older mysqld - servers does not have the bug. The following procedure can reproduce the bug:

I have un-packed the mysql-5.1.49-linux-x86_64-glibc23.tar.gz package in my tmp/ directory and started the deamon as follows:
 ./bin/mysqld_safe

Then you can do the following commands ro reproduce the bug:

CREATE TABLE mytable (ttime TIMESTAMP, KEY (ttime) );

INSERT INTO mytable VALUES ('2009-12-24 20:53:24'), ('2009-12-24
20:53:24'), ('2009-12-24 20:53:24'), ('2009-12-24 20:53:44'),
('2009-12-24 20:54:44'), ('2009-12-24 20:54:44');

select ttime from mytable where ttime >= '20091224205324';
// FALSE

select ttime from mytable where ttime = '20091224205324';
// CORRECT

select ttime from mytable where ttime >= 20091224205324;
// CORRECT

Please let me know if you are able to reproduce the bug now.
Best regards, Welf

Thank you for the feedback.

Verified as described. for some reason it started to be repeatable with current development sources too.

Just some additional remarks:
it is a strange bug caused by the concrete spelling of the Timestamp.
mysql is using an integer for the timestamp comparison, what means that the conversion of the timestamp into the integer is probably buggy, what is very strange that this conversion depends on what type of comparator (> or >=) Im using.
I guess, the bug is for industrial application crucial, because it leads to a wrong dataset without having an information that something went wrong, just some data are lost.
The concrete spelling what leads to the bug is recommended by the mysql documentation, what makes the situation even worst.
I can only guess about the reason, but I would be interested about knowing what really happened ;-)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117428

3498 Gleb Shchepa	2010-09-02
      Bug #55779: select does not work properly in mysql server
                  Version "5.1.42 SUSE MySQL RPM"
      
      When a query was using a DATE or DATETIME value formatted
      using different formatting than "yyyy-mm-dd HH:MM:SS", a
      query with a greater-or-equal '>=' condition matched only
      greater values in an indexed TIMESTAMP column.
      
      The problem was introduced by the fix for the bug 46362
      and partially solved (for DATE and DATETIME columns only)
      by the fix for the bug 47925.
      
      The stored_field_cmp_to_item function has been modified
      to take into account TIMESTAMP columns like we do for
      DATE and DATETIME columns.
     @ mysql-test/r/type_timestamp.result
        Test case for bug #55779.
     @ mysql-test/t/type_timestamp.test
        Test case for bug #55779.
     @ sql/item.cc
        Bug #55779: select does not work properly in mysql server
                    Version "5.1.42 SUSE MySQL RPM"
        
        The stored_field_cmp_to_item function has been modified
        to take into account TIMESTAMP columns like we do for
        DATE and DATETIME.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)

Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)

Noted in 5.5.7, 5.6.1 changelogs.

If a query specified a DATE or DATETIME value in a format different
from 'YYYY-MM-DD HH:MM:SS', a greater-than-or-equal (>=) condition
matched only greater-than values in an indexed TIMESTAMP column.

Setting report to Need Merge pending push to 5.1.x.

Setting to Need Merge pending push to 5.1.x.

Noted in 5.1.52 changelog.

Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)

While the ticket is closed it might be useful to point out that the same issue has been seen on RHEL/CentOS 5.1.x-enterprise-gpl-advanced x86_64 rpms and verified resolved in a version after 5.1.52.