WHERE statement causes problems...

[JCruz063]

Hello All

I upgraded from MySQL 4.1 to 5.0. Under 4.1, I had the following query, which ran without a glitch:

select parentTable.dateField, childTable.number from parentTable
inner join childTable on parentTable.parentTableId = childTable.parentTableId
where (
  childTable.parentTableId = 0
  AND
  childTable.number<=30
)
OR
(
  childTable.number = 1
  AND
  (
    childTable.parentTableId IN (4973)
  )
  OR
  (
    [red][b]parentTable.parentTableID IN (4973)[/b][/red]
    AND childTable.someField IN (1,2,3,4,5)
  )
)
order by parentTable.dateField

As you may see above, I am retrieving records from two tables: parentTable, and childTable which are linked by parentTableId. These records are sorted by a date field (parentTable.date).

Under 4.1, this query runs beautifully, but under 5.0, the records are not sorted properly. What happens is that the records that match the crietria childTable.parentTableId = 0 are shown first, even though their date is the latest. The rest of the records display afterwards, sorted asc by date.

They look like this:
[tt]2007-01-01 [green](must be at the bottom)[/green]
2007-01-01 [green](must be at the bottom)[/green]
2006-08-20
2006-08-21
2006-08-22[/tt]

I was going crazy with this, so I started doing all sorts of tests. The results were outrageous!!! I discovered that the cause of the problem is the line in red, [red]parentTable.parentTableID IN (4973)[/red]. Incredibly enough, if instead of using parentTableId, I would use a dateField for the criteria, the records would sort properly. Worse yet, if I changed that line to
[red]parentTable.parentTableID IN (0, 4973)[/red], the records would also sort properly.

Can anybody tell me what on earth is going on?

I have Windows 2000, all up-to date.
Also, I have all the latest updates and drivers from MySQL.

Thanks.

_________________________________
I think, therefore I am. [Rene Descartes]

 

[r937]

i'm pretty sure you have a parentheses problem

but to be 100% sure i'd have to know what those fields actually mean

however, i will venture a guess that you will get correct results if you change your WHERE clause to this, with the added parentheses --

where (
  childTable.parentTableId = 0
  AND
  childTable.number<=30
)
OR
(
  childTable.number = 1
  AND [b][COLOR=red]([/color][/b]
  (
    childTable.parentTableId IN (4973)
  )
  OR
  (
    parentTable.parentTableID IN (4973)
    AND childTable.someField IN (1,2,3,4,5)
  ) [b][COLOR=red])[/color][/b] 
)

r937.com | rudy.ca

 

[JCruz063]

Thanks for your reply, r937.

I do not think the problem is linked to the parentheses, otherwise it would not have worked in 4.1. Besides, if you change the red bold statement in my code above by either (1) adding a zero to the parentheses as in (0, 4973) or by (2) changing the statement to parentTable.dateField IN (date1, date2, etc) fixes the problem.

I did add that extra parentheses, though, but still I get the same problem.

I am pulling records from 2 tables which are related to each other in a parent-child relationship. Here's what the fields mean:

1. parentTableId - Field that exists in both tables. It is the primary key of the parent table and the foreign key in the child table. This is the field by which the two tables relate.

2. dateField - The records of the parent table have a date field which tells me when the record occurs. This field is parallel in meaning to, say an "order" or "invoice" table which contains a order/invoice date that says when the order or invoice occured. It's exactly the same thing in my case; the date field just says when the parentTable record occurs.

3. childTable.number - Each parent has children. childTable.number is simply the number assigned to the child in relation to its sibblings. It's either child 1, or child 2, or child 3, and so on. The statement that says [blue]childTable.number = 1 AND (childTable.parentTableId IN (4973)) OR ...[/blue] is just attempting to retrieve children that match a certain criteria, and it wants to ensure that at least one child is retrieved.

4. someField - This is a property of each child. It's a number field, which could have the values 0 - 9. In this instance, I'm interested in the children whose someFiled is between 1 and 5.

Thanks again.

JC

_________________________________
I think, therefore I am. [Rene Descartes]