From: Michael Widenius
Date: April 27 1999 1:08am
Subject: Strange problem (bug?) with CONCAT / UNIX_TIMESTAMP
List-Archive: http://lists.mysql.com/mysql/2437
Message-Id: <14117.3516.525117.889252@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Oliver" == Oliver Billmann <oliver@stripped> writes:

>> Description:
Oliver> 	I just created a simple table and ran a select query which gave a
Oliver> 	wrong result.

>> How-To-Repeat:
Oliver> 	create table test(
Oliver> 	  Zeit time,
Oliver> 	  Tag tinyint not null,
Oliver> 	  Monat tinyint not null,
Oliver> 	  Jahr smallint not null,
Oliver> 	  index(Tag),
Oliver> 	  index(Monat),
Oliver> 	  index(Jahr)
Oliver> 	);
Oliver> 	insert into test values ("09:26:00",16,9,1998);
Oliver> 	insert into test values ("09:26:00",16,9,1998);

Oliver> 	SELECT CONCAT(Jahr,'-',Monat,'-',Tag,' ',Zeit) AS Date,
Oliver> 	       UNIX_TIMESTAMP(CONCAT(Jahr,'-',Monat,'-',Tag,' ',Zeit)) AS Unix
Oliver> 	FROM test;
Oliver> 	+--------------------+-----------+
Oliver> 	| Date               | Unix      |
Oliver> 	+--------------------+-----------+
Oliver> 	| 1998-9-16 09:26:00 |         0 |
Oliver> 	| 1998-9-16 09:26:00 | 905930760 |
Oliver> 	+--------------------+-----------+
Oliver> 	2 rows in set (0.00 sec)

Oliver> 	The column Unix should have the same result in both rows, because of
Oliver> 	the same date...

>> Fix:
Oliver> 	don't know :-(

Better fix:

(/my/monty/sql-3.22) diff -c /my/monty/master/mysql-3.22.21/sql/item_strfunc.cc .
*** /my/monty/master/mysql-3.22.21/sql/item_strfunc.cc  Sun Jan 31 21:41:42 1999
--- ./item_strfunc.cc   Tue Apr 27 04:04:49 1999
***************
*** 91,98 ****
        }
        else if (str->alloced_length() >= res->length()+res2->length())
        {
!       str->copy(*res);
!       str->append(*res2);
        res=str;
        }
        else if (res == &tmp_value)
--- 91,103 ----
        }
        else if (str->alloced_length() >= res->length()+res2->length())
        {
!       if (str == res2)
!         str->replace(0,0,*res);
!       else
!       {
!         str->copy(*res);
!         str->append(*res2);
!       }
        res=str;
        }
        else if (res == &tmp_value)

Regards,
Monty