If I try to do the following:
SELECT *,1 AS b FROM tbl_a WHERE tbl_a.a < b;
I get an error basically saying that b is not available, but if I do it from a subquery:
SELECT * (SELECT *,1 AS b FROM tbl_a) AS foo WHERE tbl_a.a < b;
Yeah, here b is a constant, but b could also be a formula which I don't want evaluated multiple times. It would actually be preferable if I could get b evaluated only if everything prior to the comparison with b required that b be evaluated. Having b inside of the subquery requires that b be evaluated for each row with no logical short circuiting possible (assuming that any of the DBs actually do that).
I'm using MySQL 5.5. Does this occur in other DBs?
I have never used MySQL; however, SQL Server will not let you access columns created entirely in the select clause in your where clause, and would give a "Invalid Column name" error if you try. This is because it is looking explicit at the columns available from entities in the FROM clause when it matches to the where clause.
Using a subquery will not always make SQL Server evaluate a static formula multiple times though, depending on how you write the query and exactly what the engine has to do. If you look at the execution plan for something like:
It will show that the compute scalar part of the plan has an I/O cost of 0 and an estimated number of executions of 0, regardless of how large the test table is.
Incidentally, it seems that there should be little reason to use a static formula (one which does not actually reference any columns from the base tables) in the where clause and a formula that does reference a column really would need to be evaluated seperately for each row.
answered Jul 18 '12 at 10:26 PM
While exploring this problem further, I wrapped my head around the syntax more and see why access to the select clause is not available from the where clause. This is so that functions called from the select clause to generate a row are not called if a row is not generated.
The the main reason that I am using a static formula is so that my prepared statement doesn't need to have reference to the same variable several times. A simple example would be:
answered Jul 19 '12 at 06:44 PM