Re: Weird problem with differences MySQL 3.23.38 und 4.0.12

[Paul DuBois]

Some of the values passed to the CASE expressions are NULL.
There was a bug in handling NULL in CASE that was fixed in
MySQL 4.0.8:
http://www.mysql.com/doc/en/News-4.0.8.html

Note the last item on the page.  I believe this explains the
differences that you are seeing.
At 16:28 +0200 6/12/03, Axel Tietje wrote:
Hi everyone...

I actually have two servers:

1. Server: MySQL 4.0.12
2. Server: MySQL 3.23.38
This query:

SELECT o_obj,
       MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431,
       MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431,
       MAX(CASE WHEN o_key = 'AA0887CB' THEN o_val END) AS _AA0887CB,
       MAX(CASE WHEN o_key = 'AA0887CB' THEN o_typ END) AS T_AA0887CB
FROM   TBL_32BF90B0
WHERE (
       (o_key = '69B96431' AND o_val = '01') OR
       (o_key = 'AA0887CB' AND o_val = '1')
      )
GROUP BY o_obj
HAVING (_69B96431 = '01')
shows on server 1 (4.0.12):
+----------+-----------+------------+-----------+------------+
| o_obj    | _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB |
+----------+-----------+------------+-----------+------------+
| 1672BE70 | 01        | S          | 1         | B          |
| D27518B1 | 01        | S          | 1         | B          |
+----------+-----------+------------+-----------+------------+
2 rows in set (0.01 sec)
but on server 2 (3.23.38):
+----------+-----------+------------+-----------+------------+
| o_obj    | _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB |
+----------+-----------+------------+-----------+------------+
| 1672BE70 | 01        | S          | NULL      | NULL       |
| D27518B1 | 01        | S          | NULL      | NULL       |
+----------+-----------+------------+-----------+------------+
Please note the differences in fourth and fifth column while having
absolutely identical tables and data.
The following query works on 1. Server (4.0.12), but not on 2. 
Server (3.23.38):

SELECT o_obj,
       MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431,
       MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431,
       MAX(CASE WHEN o_key = 'AA0887CB' THEN o_val END) AS _AA0887CB,
       MAX(CASE WHEN o_key = 'AA0887CB' THEN o_typ END) AS T_AA0887CB
FROM   TBL_32BF90B0
WHERE  (
        (o_key = '69B96431' AND o_val = '01') OR
        (o_key = 'AA0887CB')
       )
GROUP BY o_obj
HAVING (_69B96431 = '01');
1. Server says:
+----------+-----------+------------+-----------+------------+
| o_obj    | _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB |
+----------+-----------+------------+-----------+------------+
| 1672BE70 | 01        | S          | 1         | B          |
| D27518B1 | 01        | S          | 1         | B          |
+----------+-----------+------------+-----------+------------+
2 rows in set (0.01 sec)
2. Server says:
Empty set (0.00 sec)
Explain shows the folowing in column 'Extra':

1. Server:
Using where; Using temporary; Using filesort
2. Server:
where used; Using temporary
All other columns are identical:

table         TBL_32BF90B0
type          ALL
possible_keys o_key,o_val
key           NULL
key_len       NULL
ref           NULL
rows          312
The following query shows the same result on both servers:

SELECT o_obj,
       MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431,
       MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431
FROM   TBL_32BF90B0
WHERE  (
        (o_key = '69B96431' AND o_val = '01')
       )
GROUP BY o_obj
HAVING (_69B96431 = '01')
Both servers say:
+----------+-----------+------------+
| o_obj    | _69B96431 | T_69B96431 |
+----------+-----------+------------+
| 1672BE70 | 01        | S          |
| D27518B1 | 01        | S          |
+----------+-----------+------------+
2 rows in set (0.01 sec)
Now, why that?

TIA, Axel.


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]