Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-38

NOT EXISTS to IN (part of exists2in transformation for 10.0)

        Details

        • Type: Task
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Fix Version/s: 10.0.2
        • Component/s: None
        • Labels:
          None

          Description

          Transform queries like:

          ... WHERE EXISTS (SELECT 1 FROM inner_table WHERE inner_table.field = 
2*outer_table.field AND maybe_something_else)...

          and

          ... WHERE NOT EXISTS (SELECT 1 FROM inner_table WHERE inner_table.field = 
2*outer_table.field AND maybe_something_else)...

          into

          ... WHERE 2*outer_table.field IN (SELECT inner_table.field FROM inner_table 
WHERE 1 = 1 AND maybe_something_else)..

          ... WHERE NOT( 2*outer_table.field IS NOT NULL AND NOT 2*outer_table.field IN 
(SELECT 
inner_table.field FROM inner_table WHERE inner_table.field IS NOT NULL AND 
maybe_something_else)...

          To allow optimizations made for IN/ALL/ANY subqueries.

          Conversion is possible only if:
          1)real NULL is not important (top element of WHERE/ON AND/OR list, i.e. NULL
          equal to FALSE)
          2a)subquery has dependences in the WHERE clause and they are equalities and in is not "NOT EXISTS" (this kind of queries could be converted to semijoin)
          OR
          2b)subquery has the only dependences (after bringing them out subquery become independent) in the WHERE clause and they are equalities (could be materialized)
          3)the subquery is simple (has no aggregate function, GROUp BY, ORDER BY, LIMIT
          HAVING and so on)

          For NOT EXISTS conversion the subquery should be marked that its left part can't
          be NULL.

          Note: Number of equalities could be limited by already allocated space for select list for current implementation.

            Gliffy Diagrams

              Attachments

                Issue Links

                includes

                Bug - A problem which impairs or prevents the functions of the product. MDEV-3903 Exists2In: Server crashes in Item_cond::fix_fields on 2nd execution of a prepared stmt with exists_to_in+materialization+semijoin, EXISTS subquery, STRAIGHT_JOIN

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-3904 Exists2In: Assertion `in_subs->has_strategy()' failed in JOIN::choose_subquery_plan on 2nd execution of PS with exists_to_in+semijoin, EXISTS subquery, MERGE view or FROM subquery

                • Major - Major loss of function.
                • Closed
                is blocked by

                Bug - A problem which impairs or prevents the functions of the product. MDEV-159 Exists2In: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in String* Field_varstring::val_str(String*, String*)

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-160 Exists2In: Crash in in hp_movelink with subquery_cache=ON

                • Major - Major loss of function.
                • Closed
                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-245 Exists2In: Wrong result (extra rows) with exists_to_in=ON, materialization=OFF, NOT EXISTS subquery

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-403 Exists2In: Wrong result (missing rows) with subquery in EXISTS and an OR condition outside

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-404 Exists2In: Wrong result (extra rows) with STRAIGHT_JOIN, EXISTS subquery, NOT NULL column

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-3906 Exists2In: Server crashes in Dependency_marker::visit_field on 2nd execution of PS with exists_to_in and NOT EXISTS subquery

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-243 Exists2In: Wrong result (extra or missing rows) with exists_to_in + materialization, EXISTS subquery

                • Major - Major loss of function.
                • Closed

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  I checked that current implementation uses full match (that is what we wanted).

                  Show
                  sanja Oleksandr Byelkin added a comment - I checked that current implementation uses full match (that is what we wanted).
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  Added multi-item support. Waiting for test results.

                  Show
                  sanja Oleksandr Byelkin added a comment - Added multi-item support. Waiting for test results.
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  Start after review work...

                  Show
                  sanja Oleksandr Byelkin added a comment - Start after review work...
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  Problem with view test could be problem of unset flag due to absence of real execution of the query (no result after reading constant tables).

                  Show
                  sanja Oleksandr Byelkin added a comment - Problem with view test could be problem of unset flag due to absence of real execution of the query (no result after reading constant tables).
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  Move to 10.0

                  Show
                  sanja Oleksandr Byelkin added a comment - Move to 10.0
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  It seems, it is easy to support EXISTS->IN conversion anywhere (not only on top level of WHERE or under NOT). EXISTS's NULLs handling policy is easier than that of IN, so we'll just need to introduce/use NULL-oblivious-IN. Details in the email. need to discuss it.,

                  Show
                  psergey Sergei Petrunia added a comment - It seems, it is easy to support EXISTS->IN conversion anywhere (not only on top level of WHERE or under NOT). EXISTS's NULLs handling policy is easier than that of IN, so we'll just need to introduce/use NULL-oblivious-IN. Details in the email. need to discuss it.,
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

                  DBT-3 has EXISTS subqueries. In Q4 and Q22 - optimization is applicable, in Q21 optimization not applicable (non-equality correlated conditions).

                  I don't expect these queries to benefit from this optimization (they all seem to be better served by IN-to-EXISTS strategy), though. We'll also need to check for regressions, just in case.

                  Show
                  psergey Sergei Petrunia added a comment - DBT-3 has EXISTS subqueries. In Q4 and Q22 - optimization is applicable, in Q21 optimization not applicable (non-equality correlated conditions). I don't expect these queries to benefit from this optimization (they all seem to be better served by IN-to-EXISTS strategy), though. We'll also need to check for regressions, just in case.
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  pushed to 10.0-base

                  Show
                  sanja Oleksandr Byelkin added a comment - pushed to 10.0-base

                    People

                    • Assignee:
                      sanja Oleksandr Byelkin
                      Reporter:
                      ratzpo Rasmus Johansson
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      3 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved:

                        Time Tracking

                        Estimated:
                        Original Estimate - Not Specified
                        Not Specified
                        Remaining:
                        Remaining Estimate - 0 minutes
                        0m
                        Logged:
                        Time Spent - 1 hour, 30 minutes
                        1h 30m