I've just signed on to the list, but no amount of searching in archives or Google has helped me. Hopefully you all can.
I upgraded my OS X installation of MySQL to 4.0 today for the express reason of taking advantage of the new UNION syntax. It seems to be working....for the most part. However, I'm getting something strange.
When I perform both of my sub-select statements and view the results, everything is just fine. However, when I do a UNION, rows which are affected by some LEFT JOINs appear to be losing their database values, and simply inheriting values from previous rows.
For example, my first query returns (hope the formatting is preserved):
apptID noteID noteState EvalDate treatmentID treatmentStart planID
37 0 0 2003-03-26 18:01:02 97 2003-03-26 13:01:09 189
38 0 0 2003-03-26 18:01:02 98 2003-03-26 15:02:18 190
39 0 0 2003-03-26 18:01:02 99 2003-03-26 15:05:57 190
my second query returns:
apptID noteID noteState EvalDate treatmentID treatmentStart planID
37 31 1 2003-03-26 13:00:00 97 2003-03-26 13:01:09 189
38 32 1 2003-03-26 15:01:00 98 2003-03-26 15:02:18 190
35 1 2003-03-26 15:40:00
36 1 2003-03-26 15:47:00
but my UNION returns:
apptID noteID noteState EvalDate treatmentID treatmentStart planID
37 0 0 0000-00-00 00:00:00 97 2003-03-26 13:01:09 189
38 0 0 0000-00-00 00:00:00 98 2003-03-26 15:02:18 190
39 0 0 0000-00-00 00:00:00 99 2003-03-26 15:05:57 190
37 31 1 2003-03-26 13:00:00 97 2003-03-26 13:01:09 189
38 32 1 2003-03-26 15:01:00 98 2003-03-26 15:02:18 190
0 32 1 2003-03-26 15:01:00 98 2003-03-26 15:02:18 190
0 32 1 2003-03-26 15:01:00 98 2003-03-26 15:02:18 190
notice the last 2 lines of the last two result sets. The second query has a <null> result in the first column. In the union, ALL values are ignored from the DB, and appear to simply be a repeat of the last 3rd-to-last line.
This sure looks like a bug. I'd be happy to send a dump script to anyone who could help me troubleshoot this.
here is my SQL:
#filler
(
select
0 as apptID,
0 as noteID,
patientEvals.state as noteState,
patientEvals.EvalDate,
0 as treatmentID,
NOW() as treatmentStart,
0 as planID
from
patientEvals,
Appointments,
PaTreatments,
TrPlans
where 1=0
)
UNION ALL
#treatments
(
select
Appointments.ID as apptID,
0 as noteID,
0 as noteState,
0 as EvalDate,
PaTreatments.ID as treatmentID,
PaTreatments.StartDateTime as treatmentStart,
TrPlans.ID as planID
from
TrPlans,
PaTreatments,
Appointments
where
PaTreatments.AppID = Appointments.ID and
TrPlans.EvalID = $currentEval and
TrPlans.ID = PaTreatments.TrPlanID
)
UNION
#progress notes
(
select
Appointments.ID as apptID,
patientEvals.id as noteID,
patientEvals.state as noteState,
patientEvals.EvalDate,
PaTreatments.ID as treatmentID,
PaTreatments.StartDateTime as treatmentStart,
TrPlans.ID as planID
from
patientEvals LEFT JOIN Appointments ON patientEvals.appID = Appointments.ID
LEFT JOIN PaTreatments ON patientEvals.appID = PaTreatments.AppID
LEFT JOIN TrPlans ON PaTreatments.TrPlanID = TrPlans.ID
where
patientEvals.parentID = $currentEval
#order by patientEvals.EvalDate asc
)
Thanks very much,
michael
----------------------------------------------- michael geary The Treeline Group, Inc. 560 S. State Street, E-2 Orem, UT 84058
(801) 225-1414 x 1002 http://www.treelinegroup.com
