Hi!
>>>>> "Braxton" == Braxton Robbason <robbason@stripped>
> writes:
Braxton> hi there. I think it would be possible to implement derived tables in MySQL
Braxton> without too much difficulty, and it would make a lot of user problems easier
Braxton> to solve.
Braxton> There is a clear mapping between derived table queries and queries using
Braxton> temp tables, but in many applications, it is easier to use derived tables in
Braxton> a single pass of sql than to create multiple sqls and then to run them
Braxton> sequentially. The algorithm I would use to convert derived table sql to
Braxton> temporary table sql is as follows:
Braxton> 1) does the query contain a derived table?
-> if no, then process it normally.
Braxton> 2) for each derived table sql: remove it and put it into a temporary table.
Braxton> the name of the temp table and any calculated columns (sum(x) etc) in it
Braxton> would have to be generated in such a way as not to conflict with user
Braxton> objects. Then recurse on these sqls in case they contain derived table sql
Braxton> in turn.
Braxton> 3) run all sqls in order. If there is an error, the standard error message
Braxton> wouldn't make much sense without some more complex processing.
Braxton> This could be implemented at a layer above the optimizer even.
Braxton> What do you think of this? Has there been an effort to implement derived
Braxton> table sql yet? I'd be happy to participate if that would help.
Braxton> Braxton
Exactly what do you mean with derived tables?
Sub selects like the following ?
SELECT * from table1 where salary= (select max(salary) from table1)
Yes, this could be done by creating a table for the sub-selects as
long as there aren't any reference to outer tables.
I would however like to delay this to 4.1 when we will start coding
the base to handle optimized sub-select queries.
(This is actually not that hard, we would just need find a couple of
weeks of development time...)
Regards,
Monty
