2.18.5 Join Limitations

• Antijoins, with the exception of supported IN and EXISTS antijoin variants listed below.

• EXISTS semijoins and antijoins are supported in the following variants only:

  • SELECT ... WHERE ... EXISTS (...)

  • SELECT ... WHERE ... EXISTS (...) IS TRUE

  • SELECT ... WHERE ... EXISTS (...) IS NOT FALSE

  • SELECT ... WHERE ... NOT EXISTS (...) IS FALSE

  • SELECT ... WHERE ... NOT EXISTS (...) IS NOT TRUE

  Depending on transformations and optimizations performed by MySQL, other variants of EXISTS semijoins may or may not be offloaded.

• IN semijoins and antijoins other than the following variants:

  • SELECT ... WHERE ... IN (...)

  • SELECT ... WHERE ... IN (...) IS TRUE

  • SELECT ... WHERE ... NOT IN (...) IS FALSE

  Depending on transformations and optimizations performed by MySQL, other variants of IN semijoins may or may not be offloaded.

• A query with a supported semijoin or antijoin condition may be rejected for offload due to how MySQL optimizes and transforms the query.

• Semijoin and antijoin queries use the best plan found after evaluating the first 10000 possible plans, or after investigating 10000 possible plans since the last valid plan.

  The plan evaluation count is reset to zero after each derived table, after an outer query, and after each subquery. The plan evaluation limit is required because the DUPSWEEDOUT join strategy, which is not supported by HeatWave, may be used as a fallback strategy by MySQL during join order optimization (for related information, see FIRSTMATCH). The plan evaluation limit prevents too much time being spent evaluating plans in cases where MySQL generates numerous plans that use the DUPSWEEDOUT semijoin strategy.

• Outer join queries without an equality condition defined for the two tables.

• Some outer join queries with IN ... EXISTS sub-queries (semi-joins) in the ON clause.