SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: root
To: [EMAIL PROTECTED]
Subject: Bug with CASE expression in Update statement
>Description:
CASE WHEN expression used in UPDATE statement gives wrong result.
>How-To-Repeat:
DROP TABLE IF EXISTS Eric;
CREATE TABLE Eric (ID INTEGER NOT NULL PRIMARY KEY,
Company_ID INTEGER NULL);
INSERT INTO Eric VALUES ( 1, -1);
INSERT INTO Eric VALUES ( 2, -1);
INSERT INTO Eric VALUES ( 3, 1);
INSERT INTO Eric VALUES ( 4, 1);
INSERT INTO Eric VALUES ( 5, 10);
INSERT INTO Eric VALUES ( 6, 10);
INSERT INTO Eric VALUES ( 7, 1000000);
INSERT INTO Eric VALUES ( 8, 1000000);
INSERT INTO Eric VALUES ( 9, 10);
INSERT INTO Eric VALUES (10, 10);
SELECT Company_ID,
COUNT(*)
FROM Eric
GROUP BY Company_ID;
+------------+----------+
| Company_ID | COUNT(*) |
+------------+----------+
| -1 | 2 |
| 1 | 2 |
| 10 | 4 |
| 1000000 | 2 |
+------------+----------+
4 rows in set (0.00 sec)
mysql> UPDATE Eric SET Company_ID =
CASE WHEN Company_ID <= 0 OR Company_ID >= 500000000
THEN NULL
ELSE Company_ID + 1000000000
END;
Query OK, 10 rows affected (0.01 sec)
Rows matched: 10 Changed: 10 Warnings: 0
mysql> SELECT Company_ID,
COUNT(*)
FROM Eric
GROUP BY Company_ID;
+------------+----------+
| Company_ID | COUNT(*) |
+------------+----------+
| NULL | 10 |
+------------+----------+
1 row in set (0.00 sec)
>Fix:
UPDATE Eric
SET Company_ID = IF(Company_ID <= 0 OR Company_ID >= 500000000,
NULL, Company_ID + 1000000000);
>Submitter-Id: <submitter ID>
>Originator: Robert Berman
>Organization:
Peregrine Systems, Inc.
Ottawa, ON, Canada K2C 3V4
Tel: (613) 274-6813, FAX: (513) 723-7209
>MySQL support: [none | licence | email support | extended email support ]
email support
>Synopsis: Bug with CASE expression in Update statement
>Severity: non-critical
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.52 (Source distribution)
>Server: /usr/mysql/bin/mysqladmin Ver 8.23 Distrib 3.23.52, for
pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.52
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 3 days 2 hours 17 min 6 sec
Threads: 6 Questions: 4801651 Slow queries: 3 Opens: 134778 Flush tables:
129 Open tables: 128 Queries per second avg: 17.955
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux 172-22-5-24.localdomain 2.4.20 #1 Fri Nov 29 09:37:13 EST 2002
i686 unknown
Architecture: i686
Some paths: /usr/bin/perl
Compilation info: CC='gcc' CFLAGS='-mcpu=pentiumpro' CXX='g++'
CXXFLAGS='-mcpu=pentiumpro -fno-exceptions -fno-rtti' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Dec 6 11:21 /lib/libc.so.6 ->
libc-2.2.3.so
-rwxr-xr-x 1 root root 1193256 Sep 26 2001 /lib/libc-2.2.3.so
-rw-r--r-- 1 root root 24976516 Aug 31 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 190 Aug 31 2001 /usr/lib/libc.so
Configure command: ./configure --enable-assembler CFLAGS=-mcpu=pentiumpro
'CXXFLAGS=-mcpu=pentiumpro -fno-exceptions -fno-rtti' CXX=g++
---------------------------------------------------------------------
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
