2

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;
  • 1
    It's good programming practice to _qualify all columns_, at least when several tables/instances are involved. – jarlh Sep 06 '22 at 17:52

1 Answers1

0

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Hi @Bill Karwin, great info, I was unaware of this data, but then why does the second query not find the reference of vidios.video_id if only 2 nesting levels? –  Sep 06 '22 at 18:45
  • Does `videos` have a column `video_id`? It seems like it has a column `videos.id` based on your condition joining to `comentarios`. – Bill Karwin Sep 06 '22 at 19:38
  • Yes of course @Bill Karwin, that's my mistake, I didn't realize it, but here in StackOverFlow I read that "Oracle does not correlate nested subqueries more than one level deep (and neither does MySQL)." –  Sep 06 '22 at 20:25
  • "Many databases will still recognize c, even when nested multiple levels. However, MySQL (and Oracle and I think MS Access) is a database that limits correlation clauses to one level deep" I read this here in another question –  Sep 06 '22 at 20:29
  • 1
    I don't think that's true. I tested your query after correcting it to `vidios.id` and it does not return an error. – Bill Karwin Sep 06 '22 at 20:31
  • Yes, me too thank you very much, now I have demystified that statement, one last question, is a derived table a kind of subquery? I will mark this answer as the best one. –  Sep 06 '22 at 20:37
  • 1
    Yes, refer to https://dev.mysql.com/doc/refman/8.0/en/derived-tables.html where they describe it as a type of subquery. – Bill Karwin Sep 06 '22 at 20:39
  • Great, thanks so much for everything @Bill Karwin. Ready marked as best answer –  Sep 06 '22 at 20:42