Question 285383 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – can I rely 100% on logical processing to know which objects (columns aliases, tables, etc) I have "available" in subsequent query clauses?

The documentation says that logical processing is used to know what I have available for the next clauses of a query, but later it contradicts itself by saying that the order can change, that confuses me.

Here, logical processing

Logical Processing Order of the SELECT statement

The following steps show the logical processing order, or binding
order, for a SELECT statement. This order determines when the objects
defined in one step are made available to the clauses in subsequent
steps. For example, if the query processor can bind to (access) the
tables or views defined in the FROM clause, these objects and their
columns are made available to all subsequent steps. Conversely,
because the SELECT clause is step 8, any column aliases or derived
columns defined in that clause cannot be referenced by preceding
clauses. However, they can be referenced by subsequent clauses such as
the ORDER BY clause. The actual physical execution of the statement is
determined by the query processor and the order may vary from this
list.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

Warning

The preceding sequence is usually true. However, there are uncommon
cases where the sequence may differ.

For example, suppose you have a clustered index on a view, and the
view excludes some table rows, and the view’s SELECT column list uses
a CONVERT that changes a data type from varchar to integer. In this
situation, the CONVERT may execute before the WHERE clause executes.
Uncommon indeed. Often there is a way to modify your view to avoid the
different sequence, if it matters in your case.

  1. Does this mean that, for example, there will be times when the WHERE clause is executed before the FROM clause and times when the FROM clause is executed first and then the WHERE clause?
  2. Can I rely 100% on logical processing to know which objects (columns aliases, tables, etc) I have "available" in subsequent query clauses?

I also read this from a StackOverflow expert on SQL:

SQL, by the way, is a descriptive language not a procedural language.
A query does not specify the "order of execution". That is determined
by the compiler and optimizer. What you are referring to is the "order
of parsing", which explains how identifiers are resolved in the query.

  1. So, what is the point of logical processing if physical processing (the database, optimizer, indexes, etc at the end of the day) decides the order of execution in a query?
  2. Does this mean that physical processing can "skip" (alter, modify, I don’t know what the right word is) the logical processing steps?
  3. Does the logical processing is executed first and then the physical processing?, or how?
Tags:

2

Answers


  1. 0
    1. Does this mean that, for example, there will be times when the WHERE clause is executed before the FROM clause and times when the FROM clause is executed first and then the WHERE clause?

    No, because in order to filter something (WHERE) you need to know what you are filtering (FROM). So some sort of FROM will certainly happen before some sort of WHERE.
    I am saying "some sort" because the exact FROM that actually happens may not be the exact FROM you have specified in your query. This includes not only the order of the FROMed objects, but even their composition (the RDBMS may substitute an object with an equivalent object (table/view) or completely omit FROMing an object if it does not affect the final result).

    1. Can I rely 100% on logical processing to know which objects (columns aliases, tables, etc) I have "available" in subsequent query clauses?

    No, you rely on the SQL grammar for that. The available aliases depend on the nesting level at each textual point of your query.

    1. So, what is the point of logical processing if physical processing (the database, optimizer, indexes, etc at the end of the day) decides the order of execution in a query?

    The point of logical processing is to establish the correct result that needs to be achieved. Having done that, you can come up with a completely different way of actually achieving that result, that involves accessing and processing data in very unexpected ways.

    1. Does this mean that physical processing can "skip" (alter, modify, I don’t know what the right word is) the logical processing steps?

    The physical processing is concerned with only producing the result that the logical processing would have produced. It does not care in the slightest about preserving any sort of the order of operations, as long as the final result is the same as the logical processing would have achieved. See What exactly is the "as-if" rule?.

    1. Does the logical processing is executed first and then the physical processing?, or how?

    That is a philosophical question.
    You may say that it is, because the query optimizer is written in such way that its result will match that of the logical processing, so in this sense the logical processing is "executed first" (as it influences the decisions of the optimizer from step 1).
    Or you may say that the logical processing is not executed at all.

    Login or Signup to reply.
  2. 0

    1. Does this mean that, for example, there will be times when the WHERE clause is executed before the FROM clause and times when the FROM clause is executed first and then the WHERE clause?
    If you mean at the logical stage: no, FROM always comes logically before a WHERE. It is certainly possible for a WHERE filter physically to happen first, for example startup filters which do not depend on the tables. But the result will be the same.
    That (rather badly written) Warning in the documentation is warning about runtime errors, especially in the case of CONVERT (although it can also happen with divide-by-zero and others). What can happen is that the compiler does not take into account possible error conditions, and assumes the query will finish correctly, so it reorders conditions in a way that can cause an error.
    The way around such errors is to use constructs which force the ordering, such as TRY_CONVERT, CASE or NULLIF.

    2. Can I rely 100% on logical processing to know which objects (columns aliases, tables, etc) I have "available" in subsequent query clauses?
    Yes, the SQL grammar enforces what the result of the logical processing should be.

    3. So, what is the point of logical processing if physical processing (the database, optimizer, indexes, etc at the end of the day) decides the order of execution in a query?
    They are two separate things: logical defines the what, physical defines the how.

    4. Does this mean that physical processing can "skip" (alter, modify, I don’t know what the right word is) the logical processing steps?
    Yes, it may. There are many ways your query can be transformed, using various optimizations and simplifications. The result should be the same.

    5. Does the logical processing is executed first and then the physical processing?, or how?
    The compiler will analyze the query according to the grammar to make a logical query plan, then analyze what access methods are available to execute it. Again, two different things.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search