[MDEV-38] NOT EXISTS to IN (part of exists2in transformation for 10.0) - Jira

XML

Word

Printable

Details

Type: Task
Status: Closed (View Workflow)
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.

Attachments

Issue Links

includes

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

Closed

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

Closed

is blocked by

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*)

Closed

MDEV-160 Exists2In: Crash in in hp_movelink with subquery_cache=ON

Closed

relates to

MDEV-245 Exists2In: Wrong result (extra rows) with exists_to_in=ON, materialization=OFF, NOT EXISTS subquery

Closed

MDEV-403 Exists2In: Wrong result (missing rows) with subquery in EXISTS and an OR condition outside

Closed

MDEV-404 Exists2In: Wrong result (extra rows) with STRAIGHT_JOIN, EXISTS subquery, NOT NULL column

Closed

MDEV-3906 Exists2In: Server crashes in Dependency_marker::visit_field on 2nd execution of PS with exists_to_in and NOT EXISTS subquery

Closed

MDEV-243 Exists2In: Wrong result (extra or missing rows) with exists_to_in + materialization, EXISTS subquery

Closed

(4 relates to)

Sub-Tasks

1.	Exists2In: Wrong result (extra row) and unexpected warning with exists_to_in=on and a NOT EXISTS subquery	Closed	Oleksandr Byelkin
2.	Exists2In: Wrong result (missing rows) with exists_to_in=on, LEFT JOIN and NOT EXISTS subquery	Closed	Oleksandr Byelkin
3.	Endless loop for query with EXISTS predicate using outer reference to view	Closed	Oleksandr Byelkin
4.	Server crashes in Item_field::Item_field with exists_to_in=on and NOT EXISTS from a merge view	Closed	Oleksandr Byelkin

Activity

People

Assignee:: Oleksandr Byelkin

Reporter:: Rasmus Johansson (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2012-01-04 14:26

Updated:: 2013-02-26 12:52

Resolved:: 2013-02-26 12:52

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.