> 1. Check if you really need an unsigned column - if not just alter the column to
>signed
this is the most appropriate answer. i.e your table design should cater for the
"limited set" or "modulus" arithmatics.
Casting wouldn't work simply because you may really want the actual unsigned value of
2^64 - 5 which is 18446744073709551611 in one case and the 5 - 10 = -5 in another. If
you cast both, it will return -5 (not a pleasant view for a very rich bank
customer!!).
And by the way this is not a MySQL limitation, it is a computer Science issue as
computers can not implement infinite sets (N, R, Z, Q etc).
Luckily, this doesn't happen in practice because database designers do allow for such
issues in their table designs... Octavian's table is, however designed to show this
issue and not to solve a commerical problem.
Enough is enough! Let's stop this nonsense.
Nasser.
---------- "Dobromir Velev" <[EMAIL PROTECTED]> writes:
Return-Path: <[EMAIL PROTECTED]>
Received: from mx12.lax.untd.com (mx12.lax.untd.com [10.130.24.72])
by maildeliver03.nyc.untd.com with SMTP id AAA9C2PGCAFYS4JA
for <[EMAIL PROTECTED]> (sender
<[EMAIL PROTECTED]>);
Mon, 20 Jan 2003 12:41:22 -0500 (EST)
Received: from web.mysql.com (web.mysql.com [213.136.49.183])
by mx12.lax.untd.com with SMTP id AAA9C2PGBAM2YKK2
for <[EMAIL PROTECTED]> (sender
<[EMAIL PROTECTED]>);
Mon, 20 Jan 2003 09:41:21 -0800 (PST)
Received: (qmail 30395 invoked by uid 7797); 20 Jan 2003 17:12:03 -0000
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org)
List-ID: <mysql.mysql.com>
Precedence: bulk
List-Help: <mailto:[EMAIL PROTECTED]>
List-Unsubscribe: <mailto:[EMAIL PROTECTED]>
List-Post: <mailto:[EMAIL PROTECTED]>
List-Subscribe: <mailto:[EMAIL PROTECTED]>
Delivered-To: mailing list [EMAIL PROTECTED]
Received: (qmail 30024 invoked from network); 20 Jan 2003 17:11:38 -0000
Message-ID: <05b801c2c0a7$8c882800$1d00a8c0@Dobromir>
From: "Dobromir Velev" <[EMAIL PROTECTED]>
To: "Octavian Rasnita" <[EMAIL PROTECTED]>
Cc: "MySQL" <[EMAIL PROTECTED]>
References: <001901c2bfe4$58e8dda0$[EMAIL PROTECTED]>
<[EMAIL PROTECTED]>
<00ad01c2c010$45ee73a0$dc4fc7d9@mephisto>
<006001c2c09a$9a4fc1d0$[EMAIL PROTECTED]>
Subject: Re: Substraction
Date: Mon, 20 Jan 2003 19:15:33 +0200
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2720.3000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Virus-Scanned: by AMaViS perl-11
Hi,
There are several ways to solve the problem -
1. Check if you really need an unsigned column - if not just alter the
column to signed
2. A simple workaround for substracting unsigned values is to add a floating
point number to the operation - then all values will be converted to float
and the negative value will be displayed. For example this should give you
what you need
select 15.0 - id from test;
3. Use CAST function. Example:
select cast(15-id as signed) from test;
HTH
Dobromir Velev
[EMAIL PROTECTED]
----- Original Message -----
From: "Octavian Rasnita" <[EMAIL PROTECTED]>
To: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]>; "Brian
Lindner" <[EMAIL PROTECTED]>
Cc: "MySQL" <[EMAIL PROTECTED]>
Sent: Monday, January 20, 2003 08:22
Subject: Re: Substraction
> But I don't want to perform a 15 - id, meaning 15 - 10.
> I want to calculate 5 - id, meaning 5 - 10.
>
> It should give me -5 or 5 but not | 18446744073709551611 |
>
>
> Teddy,
> Teddy's Center: http://teddy.fcc.ro/
> Email: [EMAIL PROTECTED]
>
> ----- Original Message -----
> From: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]>
> To: "Brian Lindner" <[EMAIL PROTECTED]>; "Octavian Rasnita"
> <[EMAIL PROTECTED]>
> Cc: "MySQL" <[EMAIL PROTECTED]>
> Sent: Monday, January 20, 2003 1:09 AM
> Subject: Re: Substraction
>
>
> Brian,
>
> > > create table test(id int unsigned);
> > > insert into test values(10);
> > > select 15 - id from test;
> > > The result is 18446744073709551611 instead of -5.
>
> Trying to confirm this with MySQL 4.0.7 on a Win2K box:
>
> mysql> create table test(id int unsigned);
> Query OK, 0 rows affected (0.56 sec)
>
> mysql> insert into test values(10);
> Query OK, 1 row affected (0.08 sec)
>
> mysql> select 15 - id from test;
> +---------+
> | 15 - id |
> +---------+
> | 5 |
> +---------+
> 1 row in set (0.06 sec)
>
> Now, once again with phpMyAdmin 2.3.3pl1:
>
> SELECT 15 - id
> FROM test LIMIT 0, 30
> 15 - id
> 5
>
> But with the following statement (same with phpMyAdmin):
>
> mysql> select 5 - id from test;
> +----------------------+
> | 5 - id |
> +----------------------+
> | 18446744073709551611 |
> +----------------------+
> 1 row in set (0.00 sec)
>
> Check the "1" in your "15". Maybe there's something wrong.
>
> Regards,
> --
> Stefan Hinz <[EMAIL PROTECTED]>
> Gesch䦴sfí±£í²¥r / CEO iConnect GmbH <http://iConnect.de>
> Heesestr. 6, 12169 Berlin (Germany)
> Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
>
> ----- Original Message -----
> From: "Brian Lindner" <[EMAIL PROTECTED]>
> To: "Octavian Rasnita" <[EMAIL PROTECTED]>
> Cc: "MySQL" <[EMAIL PROTECTED]>
> Sent: Sunday, January 19, 2003 10:28 PM
> Subject: Re: Substraction
>
>
> > Octavian,
> >
> > Sunday, January 19, 2003, 7:31:57 AM, you wrote:
> > > Hi all,
> >
> > > I've tried the following query:
> >
> > > create table test(id int unsigned);
> > > insert into test values(10);
> > > select 15 - id from test;
> >
> > > The result is 18446744073709551611 instead of -5.
> >
> > I ran this.. and it worked for me
> >
> > MySql 4.0.7 on Linux... ran it through phpMyAdmin
> >
> > > Am I doing something wrong?
> >
> > > Thank you.
> >
> > > Teddy,
> > > Teddy's Center: http://teddy.fcc.ro/
> > > Email: [EMAIL PROTECTED]
> >
> >
> >
> >
> > ---------------------------------------------------------------------
> > > 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 <[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > > Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> >
> >
> >
> >
> >
> > --
> > Brian
> > Email: <[EMAIL PROTECTED]>
> >
> >
> >
> >
> > ---------------------------------------------------------------------
> > 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 <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
>
>
>
> ---------------------------------------------------------------------
> 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 <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
