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.
