mysqlsqlsubquerycorrelated-subqueryderived-table

Correlated queries - In derived tables, can I have many nesting levels?

The main question and the question itself is: In derived tables, can I have many nesting levels?

Context:

I know that in MySQL you can't have correlated subqueries with two levels deep, but, using derived tables it seems that you can, is this normal or is there something I don't know, or does this 2 level thing only and STRICTLY affect correlated subqueries?

SELECT table3.field1, table3.field2, (SELECT table1.field1 FROM table1 WHERE table1.a = table3.field2) AS calculated,
        (SELECT COUNT(*) FROM (SELECT * FROM table2 WHERE table2.c = table3.field2) AS derived) AS calculated2
FROM table3;
Solution

  • MySQL's limit on levels of nesting subqueries is 63, the same as the number of tables joined.

    https://github.com/mysql/mysql-server/blob/8.0/sql/sql_lex.cc#L85

    static constexpr const int MAX_SELECT_NESTING{sizeof(nesting_map) * 8 - 1};

    nesting_map is a type defined as uint64_t a 64-bit integer. Therefore sizeof(nesting_map) is 8 bytes, and MAX_SELECT_NESTING works out to 64-1.

    If you do exceed that number of levels of nesting subqueries, you get this error:

    ERROR 1473 (HY000): Too high level of nesting for select