Schrödinger's correlated subquery
-
So, I was working on a query where I was using a correlated subquery down in the
where
clause. It used three different values from the outer query, where one of the values would change almost not at all, another fairly frequently and the third a lot. The two more frequent values were passed to a stored function. Then the value of the query was used to compare to today's date.At first this query didn't work at all. So I pulled the subquery into a select field, and viola! It worked. Sort of. It worked on one DB (my development instance) but not another (our testing instance...how embarrassing).
Eventually I pulled the subquery into a
select
field and and made an outer query that did the critical comparison.I think the problem was that it didn't notice the changing values due to them only being passed to a function combined with the actual fetch order of the data. I don't have the patience or the energy to test this theory, though.
-
Wow that's awful.
-
@boomzilla Databases are full of spooky magic.
-
@boomzilla said in Schrödinger's correlated subquery:
and and
-
@pie_flavor I was pretty pissed off.