Imagine this: I have a piece of software which went through a vigorous validation process and one of its aspects failed when it was deployed to production. Logs indicated a problem with a query like this:
select Id, Name from ComplexDetailView where ComputedField = 0;
That is an innocent looking query, but it is executed agains a view:
Create View ComplexDetailView AS
Select q.Id, q.Name,
SomeStoredFunction(q.Value, o.Value, o.OtherValue)
as ComputedField
From MyTable q JOIN OtherTable o ON q.cid = o.cid;
Running that query in command line client I see a warning from MySQL:
Incorrect integer value: 'Totally unrelated string value' for column 'v' at row 1
The 'Totally unrelated string value' is from some column in OtherTable, and that column is not even referenced in the view. The SomeStoredFunction expects an INT as its first argument and does not work with that string. Why did it got the string in the first place?
Oddly enough, if I reword this query it works properly:
Select * from
(Select q.Id, q.Name,
SomeStoredFunction(q.Value, o.Value, o.OtherValue)
as ComputedField
From MyTable q JOIN OtherTable o ON q.cid = o.cid) as ttt
where ttt.ComputedField = 0;
select Id, Name from ComplexDetailView
where ComputedField < 1 AND ComputedField > -1;
All of those "re-worded" queries should be no different in any regard. Well, they may be different in terms of which indicies they use, but they all should produce the same result set.
After 3 hours of testing I was pretty sure it was some bit of code deep inside MySQL that was giving me such an error. Using a normal database I would be right there, deep inside the DB source code already, but Amazon did not share its modifications to MySQL...
That was a frustrating experience and I got two points out of it: 1. Never use computed fields on the WHERE part of the SQL query with MySQL on RDS 2. Try to avoid RDS (or any other system you do not have access to the source code)