I just installed 4.0.18-nt on Windows 2000 Pro to utilize the new UNION feature and am experiencing weirdness which makes me think that MySQL is caching result sets when it shouldn't.
Here's a simple query from an events table and a UNION with a recurring events table, and its (correct) results (16 rows):
(SELECT SQL_NO_CACHE A.EVENT_ID AS REVENT_ID,
A.EVENT_ID as VEVENT_ID,
A.DTSTART,
A.DTEND,
A.SUMMARY
FROM VEVENTS A
WHERE 0=0
AND ((A.DTSTART >= '2004-02-01 00:00:00' AND A.DTEND <= '2004-03-01 00:00:00'))
)UNION DISTINCT
(SELECT SQL_NO_CACHE B.REVENT_ID,
B.VEVENT_ID,
B.DTSTART,
B.DTEND,
C.SUMMARY
FROM RVEVENTS B,VEVENTS C
WHERE B.VEVENT_ID = C.EVENT_ID
)ORDER BY 3 DESC
+-----------+-----------+---------------------+---------------------+-----------------------------------------------+
| REVENT_ID | VEVENT_ID | DTSTART | DTEND | SUMMARY |
+-----------+-----------+---------------------+---------------------+-----------------------------------------------
| 51 | 51 | 2004-02-29 14:00:00 | 2004-02-29 15:00:00 | Test
| 33 | 33 | 2004-02-21 15:45:00 | 2004-02-22 00:45:00 | An Event Added from Michael's Machine
| 25 | 25 | 2004-02-19 14:25:00 | 2004-02-19 14:32:00 | MLK II
| 54 | 54 | 2004-02-16 13:00:00 | 2004-02-17 00:00:00 | Fixing Errors in the Code
| 4 | 50 | 2004-02-14 05:00:00 | 2004-02-14 05:00:00 | An Event To End All Events
| 3 | 50 | 2004-02-13 05:00:00 | 2004-02-13 05:00:00 | An Event To End All Events
| 2 | 50 | 2004-02-12 05:00:00 | 2004-02-12 05:00:00 | An Event To End All Events
| 45 | 45 | 2004-02-11 16:30:00 | 2004-02-11 17:30:00 | A simultaneous event
| 32 | 32 | 2004-02-11 13:00:00 | 2004-02-11 14:00:00 | Just an ordinary event, typical in many ways.
| 46 | 46 | 2004-02-11 13:00:00 | 2004-02-11 22:00:00 | Requirements Definition
| 1 | 50 | 2004-02-11 05:00:00 | 2004-02-11 05:00:00 | An Event To End All Events
| 50 | 50 | 2004-02-10 05:00:00 | 2004-02-10 05:00:00 | An Event To End All Events
| 44 | 44 | 2004-02-10 13:00:00 | 2004-02-11 22:00:00 | Multiday event with end date specified
| 43 | 43 | 2004-02-10 01:00:00 | 2004-02-10 02:00:00 | Late Night with Letterman
| 41 | 41 | 2004-02-09 05:00:00 | 2004-02-11 05:00:00 | "Meeting of the Minds"
| 42 | 42 | 2004-02-09 05:00:00 | 2004-02-11 05:00:00 | On this day there are only other events
+-----------+-----------+---------------------+---------------------+-----------------------------------------------+
However, when I replace the SUMMARY column with LOCATION instead of just getting LOCATION for these 16 rows I get a mixture of LOCATION and SUMMARY, which now gives me 32 rows...(SNIPPED FOR BREVITY]
New query:
(SELECT SQL_NO_CACHE A.EVENT_ID AS REVENT_ID,
A.EVENT_ID as VEVENT_ID,
A.DTSTART,
A.DTEND,
A.LOCATION
FROM VEVENTS A
WHERE 0=0
AND ((A.DTSTART >= '2004-02-01 00:00:00' AND A.DTEND <= '2004-03-01 00:00:00'))
)UNION DISTINCT
(SELECT SQL_NO_CACHE B.REVENT_ID,
B.VEVENT_ID,
B.DTSTART,
B.DTEND,
B.LOCATION
FROM RVEVENTS B,VEVENTS C
WHERE B.VEVENT_ID = C.EVENT_ID
)ORDER BY 3 DESC
New results:
+-----------+-----------+---------------------+---------------------+-----------------------------------------------+
| REVENT_ID | VEVENT_ID | DTSTART | DTEND | LOCATION |
+-----------+-----------+---------------------+---------------------+-----------------------------------------------
| 51 | 51 | 2004-02-29 14:00:00 | 2004-02-29 15:00:00 | Test
| 51 | 51 | 2004-02-29 14:00:00 | 2004-02-29 15:00:00 |
| 33 | 33 | 2004-02-21 15:45:00 | 2004-02-22 00:45:00 | An Event Added from Michael's Machine
| 33 | 33 | 2004-02-21 15:45:00 | 2004-02-22 00:45:00 | Living Room Sofa
| 25 | 25 | 2004-02-19 14:25:00 | 2004-02-19 14:32:00 | MLK II
| 25 | 25 | 2004-02-19 14:25:00 | 2004-02-19 14:32:00 | tbd
| 54 | 54 | 2004-02-16 13:00:00 | 2004-02-17 00:00:00 | Fixing Errors in the Code
| 54 | 54 | 2004-02-16 13:00:00 | 2004-02-17 00:00:00 |
| 4 | 50 | 2004-02-14 05:00:00 | 2004-02-14 05:00:00 | An Event To End All Events
| 4 | 50 | 2004-02-14 05:00:00 | 2004-02-14 05:00:00 | Loc D
| 3 | 50 | 2004-02-13 05:00:00 | 2004-02-13 05:00:00 | An Event To End All Events
| 3 | 50 | 2004-02-13 05:00:00 | 2004-02-13 05:00:00 | Loc C
| 2 | 50 | 2004-02-12 05:00:00 | 2004-02-12 05:00:00 | An Event To End All Events
| 2 | 50 | 2004-02-12 05:00:00 | 2004-02-12 05:00:00 | Loc B
| 45 | 45 | 2004-02-11 16:30:00 | 2004-02-11 17:30:00 | A simultaneous event
| 45 | 45 | 2004-02-11 16:30:00 | 2004-02-11 17:30:00 | On top of old smokey
SNIP...
Is there a cache setting that I should be turning off here...I should get 16 rows for each query. (SQL_NO_CACHE has no effect).
Thanks, in advance, Bruce Altner
