MySQL 4.0.12 bug?

Hello,
I have this problem with MySQL 4.0.12.
How-To-Repeat: Let's create a simple table and insert one row:

create table test_long
(id mediumint unsigned not null primary key,
some_date_time datetime not null,
some_tinyint tinyint unsigned not null);

insert into test_long values (1, '2003-03-10 08:45:27.0', 7);

When I run the following SQL query, I get this result:

mysql> select some_date_time, some_tinyint, (unix_timestamp(some_date_time)
+ 7
* 24 * 60 * 60 - 1048188722) as time_left from test_long;
+---------------------+--------------+-----------+
| some_date_time | some_tinyint | time_left |
+---------------------+--------------+-----------+
| 2003-03-10 08:45:27 | 7 | -301595 |
+---------------------+--------------+-----------+
1 row in set (0.04 sec)

The result is correct, of course. But when I replace the "7" number by
"some_tinyint" column [which contains the same value], it gives another
result for the "time_left" column:

mysql> select some_date_time, some_tinyint, (unix_timestamp(some_date_time)
+ so
me_tinyint * 24 * 60 * 60 - 1048188722) as time_left from test_long;
+---------------------+--------------+---------------------- +
| some_date_time | some_tinyint | time_left |
+---------------------+--------------+---------------------- +
| 2003-03-10 08:45:27 | 7 | 18446744073709250021 |
+---------------------+--------------+---------------------- +
1 row in set (0.00 sec)

I have no such problem on MySQL 3.23.53, in both cases I get the same
result. Is it a MySQL 4.x.x bug? And what can I do to avoid it in the
future?

Thanks for all help.
Filip Rachunek

http://BrainKing.com



____________________________________________________________ _____
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14008@lists.mysql.com
To unsubscribe, e-mail

Re: MySQL 4.0.12 bug?

On Fri, 2003-03-21 at 13:12, Filip Rachunek wrote:
> Hello,
> I have this problem with MySQL 4.0.12.
> How-To-Repeat: Let's create a simple table and insert one row:


Thank you for the excellent test case,

however I should tell this is not the bug but rather expected behavior.
Constants which you have here by default have "longlong" type which is
signed and can contain negative values. But as soon as you add "unsigned
tinyint" colum to expression it became unsigned and so "-" returns now
very large unsigned bigint value instead.

What is about 3.23 ?
MySQL 3.23 did not have support for internal unsigned bigint arithmetics
this is why this testcase worked.

What is the best workaround:
The best workaround is to use "tinyint" for the column or other signed
type - this will lead to expected signed result.



> create table test_long
> (id mediumint unsigned not null primary key,
> some_date_time datetime not null,
> some_tinyint tinyint unsigned not null);
>
> insert into test_long values (1, '2003-03-10 08:45:27.0', 7);
>
> When I run the following SQL query, I get this result:
>
> mysql> select some_date_time, some_tinyint, (unix_timestamp(some_date_time)
> + 7
> * 24 * 60 * 60 - 1048188722) as time_left from test_long;
> +---------------------+--------------+-----------+
> | some_date_time | some_tinyint | time_left |
> +---------------------+--------------+-----------+
> | 2003-03-10 08:45:27 | 7 | -301595 |
> +---------------------+--------------+-----------+
> 1 row in set (0.04 sec)
>
> The result is correct, of course. But when I replace the "7" number by
> "some_tinyint" column [which contains the same value], it gives another
> result for the "time_left" column:
>
> mysql> select some_date_time, some_tinyint, (unix_timestamp(some_date_time)
> + so
> me_tinyint * 24 * 60 * 60 - 1048188722) as time_left from test_long;
> +---------------------+--------------+---------------------- +
> | some_date_time | some_tinyint | time_left |
> +---------------------+--------------+---------------------- +
> | 2003-03-10 08:45:27 | 7 | 18446744073709250021 |
> +---------------------+--------------+---------------------- +
> 1 row in set (0.00 sec)
>
> I have no such problem on MySQL 3.23.53, in both cases I get the same
> result. Is it a MySQL 4.x.x bug? And what can I do to avoid it in the
> future?
>
> Thanks for all help.
> Filip Rachunek
>
> http://BrainKing.com
>
>
>
> ____________________________________________________________ _____
> STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
> http://join.msn.com/?page=features/junkmail
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread14008@lists.mysql.com
> To unsubscribe, e-mail
--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14009@lists.mysql.com
To unsubscribe, e-mail

Re: MySQL 4.0.12 bug?

Thanks Peter, I've changed the column type to signed and it works perfectly
:-)

Filip

http://BrainKing.com




>From: Peter Zaitsev
>To: Filip Rachunek
>CC: bugs@lists.mysql.com
>Subject: Re: MySQL 4.0.12 bug?
>Date: 21 Mar 2003 14:33:00 +0300
>MIME-Version: 1.0
>Received: from mysql.com ([213.136.49.178]) by mc10-f10.bay6.hotmail.com
>with Microsoft SMTPSVC(5.0.2195.5600); Fri, 21 Mar 2003 03:34:30 -0800
>Received: from localhost (mail.server [10.100.1.21])by mysql.com
>(8.12.8/8.12.8-1.80) with ESMTP id h2LBYSTj020958;Fri, 21 Mar 2003 12:34:28
>+0100
>X-Message-Info: JGTYoYF78jEHjJx36Oi8+Q1OJDRSDidP
>In-Reply-To:
>References:
>Organization: MySQL
>Message-Id: <1048246380.1356.58.camel@abyss.local>
>X-Mailer: Ximian Evolution 1.2.2 Return-Path: peter@mysql.com
>X-OriginalArrivalTime: 21 Mar 2003 11:34:30.0569 (UTC)
>FILETIME=[D665B990:01C2EF9D]
>
>On Fri, 2003-03-21 at 13:12, Filip Rachunek wrote:
> > Hello,
> > I have this problem with MySQL 4.0.12.
> > How-To-Repeat: Let's create a simple table and insert one row:
>
>
>Thank you for the excellent test case,
>
>however I should tell this is not the bug but rather expected behavior.
>Constants which you have here by default have "longlong" type which is
>signed and can contain negative values. But as soon as you add "unsigned
>tinyint" colum to expression it became unsigned and so "-" returns now
>very large unsigned bigint value instead.
>
>What is about 3.23 ?
>MySQL 3.23 did not have support for internal unsigned bigint arithmetics
>this is why this testcase worked.
>
>What is the best workaround:
>The best workaround is to use "tinyint" for the column or other signed
>type - this will lead to expected signed result.
>
>
>
> > create table test_long
> > (id mediumint unsigned not null primary key,
> > some_date_time datetime not null,
> > some_tinyint tinyint unsigned not null);
> >
> > insert into test_long values (1, '2003-03-10 08:45:27.0', 7);
> >
> > When I run the following SQL query, I get this result:
> >
> > mysql> select some_date_time, some_tinyint,
>(unix_timestamp(some_date_time)
> > + 7
> > * 24 * 60 * 60 - 1048188722) as time_left from test_long;
> > +---------------------+--------------+-----------+
> > | some_date_time | some_tinyint | time_left |
> > +---------------------+--------------+-----------+
> > | 2003-03-10 08:45:27 | 7 | -301595 |
> > +---------------------+--------------+-----------+
> > 1 row in set (0.04 sec)
> >
> > The result is correct, of course. But when I replace the "7" number by
> > "some_tinyint" column [which contains the same value], it gives another
> > result for the "time_left" column:
> >
> > mysql> select some_date_time, some_tinyint,
>(unix_timestamp(some_date_time)
> > + so
> > me_tinyint * 24 * 60 * 60 - 1048188722) as time_left from test_long;
> > +---------------------+--------------+---------------------- +
> > | some_date_time | some_tinyint | time_left |
> > +---------------------+--------------+---------------------- +
> > | 2003-03-10 08:45:27 | 7 | 18446744073709250021 |
> > +---------------------+--------------+---------------------- +
> > 1 row in set (0.00 sec)
> >
> > I have no such problem on MySQL 3.23.53, in both cases I get the same
> > result. Is it a MySQL 4.x.x bug? And what can I do to avoid it in the
> > future?
> >
> > Thanks for all help.
> > Filip Rachunek
> >
> > http://BrainKing.com
> >
> >
> >
> > ____________________________________________________________ _____
> > STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
> > http://join.msn.com/?page=features/junkmail
> >
> >
> > ------------------------------------------------------------ ---------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail bugs-thread14008@lists.mysql.com
> > To unsubscribe, e-mail
>--
> MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Peter Zaitsev
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Moscow, Russia
> <___/ www.mysql.com
>


____________________________________________________________ _____
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14010@lists.mysql.com
To unsubscribe, e-mail

Re: MySQL 4.0.12 bug?

Filip,

Filip Rachunek wrote:
> Hello,


> create table test_long
> (id mediumint unsigned not null primary key,
> some_date_time datetime not null,
> some_tinyint tinyint unsigned not null);
>
> insert into test_long values (1, '2003-03-10 08:45:27.0', 7);
>
> When I run the following SQL query, I get this result:
>
> mysql> select some_date_time, some_tinyint,
> (unix_timestamp(some_date_time) + 7
> * 24 * 60 * 60 - 1048188722) as time_left from test_long;
> +---------------------+--------------+-----------+
> | some_date_time | some_tinyint | time_left |
> +---------------------+--------------+-----------+
> | 2003-03-10 08:45:27 | 7 | -301595 |
> +---------------------+--------------+-----------+

Besides Peter excelent answer I have side note on this regardless of integer types
troubles you have.

MySQL has great set of fucntions to deal with date/time calculations and you can
get your result in more natural way.

If I read your formula correct you want to find "difference in seconds
From `some_date_time` + N days
To some_constant_date (in your case timestamp 1048188722)

Here is how to do it:

select UNIX_TIMESTAMP(some_date_time + INTERVAL 7 DAY) - 1048188722 FROM test_long;

This will owrk with both constant and column.

mysql> select some_date_time, some_tinyint, UNIX_TIMESTAMP(some_date_time +
INTERVAL 7 DAY) - 1048188722 AS time_left FROM test_long;
+---------------------+--------------+-----------+
| some_date_time | some_tinyint | time_left |
+---------------------+--------------+-----------+
| 2003-03-10 08:45:27 | 7 | -305195 |
+---------------------+--------------+-----------+
1 row in set (0.02 sec)

mysql> select some_date_time, some_tinyint, UNIX_TIMESTAMP(some_date_time +
INTERVAL some_tinyint DAY) - 1048188722 AS time_left FROM test_long;
+---------------------+--------------+-----------+
| some_date_time | some_tinyint | time_left |
+---------------------+--------------+-----------+
| 2003-03-10 08:45:27 | 7 | -305195 |
+---------------------+--------------+-----------+
1 row in set (0.02 sec)


+ INTERVAL I used is alternative syntax of DATE_ADD(), DATE_SUB() functions.
Read about them as they can save lot of time. And there are lot more usefull
functions.

Just for example for above time_left I would like to use it as:


mysql> select SEC_TO_TIME(UNIX_TIMESTAMP(some_date_time + INTERVAL some_tinyint
DAY) - 1048188722) AS time_left FROM test_long;
+-----------+
| time_left |
+-----------+
| -84:46:35 |
+-----------+

Best regards

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14011@lists.mysql.com
To unsubscribe, e-mail