[MDEV-12519] NOT IN subquery predicate with recursive reference is ignored - Jira

Igor Babaev (Inactive) created issue - 2017-04-18 05:51

Sergei Golubchik made changes - 2017-04-18 06:46

Field	Original Value	New Value
Fix Version/s		10.2 [ 14601 ]

Igor Babaev (Inactive) made changes - 2017-04-18 16:26

Description

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference than this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');
with recursive
   reachables(p) as
   (
      select lp from t1 where lp = 'p1'
      union
      select t1.rp from reachables, t1 where t1.lp = reachables.p
   )
select * from reachables;

with recursive
  reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select *'p3' not in (select * from reachables) and
                              t1.lp = reachables.lp)
select * from reachables;

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select * t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.lp
)
select * from reachables;
{noformat}

Igor Babaev (Inactive) made changes - 2017-04-18 16:29

Description

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select * t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.lp
)
select * from reachables;
{noformat}

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.lp
)
select * from reachables;
{noformat}

Igor Babaev (Inactive) made changes - 2017-04-18 16:29

Description

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.lp
)
select * from reachables;
{noformat}

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.p
)
select * from reachables;
{noformat}

Igor Babaev (Inactive) made changes - 2017-04-18 16:31

Description

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.p
)
select * from reachables;
{noformat}

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

set standard_compliant_cte=0;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.p
)
select * from reachables;
{noformat}

While for the first query the result set is correct:
{noformat}

Igor Babaev (Inactive) made changes - 2017-04-18 16:34

Description

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

set standard_compliant_cte=0;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.p
)
select * from reachables;
{noformat}

While for the first query the result set is correct:
{noformat}

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

set standard_compliant_cte=0;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.p
)
select * from reachables;
{noformat}

While for the first query the result set is correct:
{noformat}
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
| p4 |
| p5 |
+----+
{noformat}
the output from the second query is not what is expected
{noformat}
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where 'p3' not in (select * from reachables) and
    -> t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
| p4 |
| p5 |
+----+
{noformat}
Expected:
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where 'p3' not in (select * from reachables) and
    -> t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
+----+
{noformat}

Igor Babaev (Inactive) made changes - 2017-04-18 16:35

Description

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

set standard_compliant_cte=0;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.p
)
select * from reachables;
{noformat}

While for the first query the result set is correct:
{noformat}
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
| p4 |
| p5 |
+----+
{noformat}
the output from the second query is not what is expected
{noformat}
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where 'p3' not in (select * from reachables) and
    -> t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
| p4 |
| p5 |
+----+
{noformat}
Expected:
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where 'p3' not in (select * from reachables) and
    -> t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
+----+
{noformat}

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

set standard_compliant_cte=0;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.p
)
select * from reachables;
{noformat}

While for the first query the result set is correct:
{noformat}
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
| p4 |
| p5 |
+----+
{noformat}
the output from the second query is not what is expected:
{noformat}
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where 'p3' not in (select * from reachables) and
    -> t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
| p4 |
| p5 |
+----+
{noformat}
Expected:
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where 'p3' not in (select * from reachables) and
    -> t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
+----+
{noformat}

Igor Babaev (Inactive) made changes - 2017-04-18 16:36

Description

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

set standard_compliant_cte=0;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.p
)
select * from reachables;
{noformat}

While for the first query the result set is correct:
{noformat}
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
| p4 |
| p5 |
+----+
{noformat}
the output from the second query is not what is expected:
{noformat}
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where 'p3' not in (select * from reachables) and
    -> t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
| p4 |
| p5 |
+----+
{noformat}
Expected:
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where 'p3' not in (select * from reachables) and
    -> t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
+----+
{noformat}

If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

This problem can be demonstrated with the following simple test case.
{noformat}
create table t1 (lp char(4) not null, rp char(4) not null);
insert into t1 values
   ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');

set standard_compliant_cte=0;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
       where t1.lp = reachables.p
)
select * from reachables;

with recursive
reachables(p) as
(
   select lp from t1 where lp = 'p1'
   union
   select t1.rp from reachables, t1
      where 'p3' not in (select * from reachables) and
                 t1.lp = reachables.p
)
select * from reachables;
{noformat}

While for the first query the result set is correct:
{noformat}
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
| p4 |
| p5 |
+----+
{noformat}
the output from the second query is not what is expected:
{noformat}
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where 'p3' not in (select * from reachables) and
    -> t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
| p4 |
| p5 |
+----+
{noformat}
Expected:
{noformat}
MariaDB [test]> with recursive
    -> reachables(p) as
    -> (
    -> select lp from t1 where lp = 'p1'
    -> union
    -> select t1.rp from reachables, t1
    -> where 'p3' not in (select * from reachables) and
    -> t1.lp = reachables.p
    -> )
    -> select * from reachables;
+----+
| p |
+----+
| p1 |
| p2 |
| p3 |
+----+
{noformat}

Igor Babaev (Inactive) made changes - 2017-04-18 16:59

Status

Open [ 1 ]

In Progress [ 3 ]

Igor Babaev (Inactive) added a comment - 2017-04-18 17:13

A possible cause of the problem can be seen here:

MariaDB [test]> explain format=json

    -> with recursive

    -> reachables(p) as

    -> (

    ->    select lp from t1 where lp = 'p1'

    ->    union

    ->    select t1.rp from reachables, t1

    ->       where 'p3' not in (select * from reachables) and

    ->                  t1.lp = reachables.p

    -> )

    -> select * from reachables;

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| {

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "<derived2>",

      "access_type": "ALL",

      "rows": 4,

      "filtered": 100,

      "materialized": {

        "query_block": {

          "recursive_union": {

            "table_name": "<union2,3>",

            "access_type": "ALL",

            "query_specifications": [

                "query_block": {

                  "select_id": 2,

                  "table": {

                    "table_name": "t1",

                    "access_type": "ALL",

                    "rows": 4,

                    "filtered": 100,

                    "attached_condition": "t1.lp = 'p1'"

},

                "query_block": {

                  "select_id": 3,

                  "const_condition": "<cache>(!<in_optimizer>('p3',<exists>(subquery#4)))",

                  "table": {

                    "table_name": "t1",

                    "access_type": "ALL",

                    "rows": 4,

                    "filtered": 100

},

                  "table": {

                    "table_name": "<derived2>",

                    "access_type": "ref",

                    "possible_keys": ["key0"],

                    "key": "key0",

                    "key_length": "4",

                    "used_key_parts": ["p"],

                    "ref": ["test.t1.lp"],

                    "rows": 2,

                    "filtered": 100

},

                  "subqueries": [

                      "query_block": {

                        "select_id": 4,

                        "table": {

                          "table_name": "<derived2>",

                          "access_type": "ALL",

                          "rows": 4,

                          "filtered": 100,

                          "attached_condition": "<cache>(convert('p3' using latin1)) = reachables.p"

} |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The predicate

'p3' not in (select * from reachables)

is considered as a constant condition. It is evaluated only once at the first iteration. Then its result is cached and reused by each next iteration.

Igor Babaev (Inactive) added a comment - 2017-04-18 17:13 A possible cause of the problem can be seen here: MariaDB [test]> explain format=json -> with recursive -> reachables(p) as -> ( -> select lp from t1 where lp = 'p1' -> union -> select t1.rp from reachables, t1 -> where 'p3' not in (select * from reachables) and -> t1.lp = reachables.p -> ) -> select * from reachables; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "table": { "table_name": "<derived2>", "access_type": "ALL", "rows": 4, "filtered": 100, "materialized": { "query_block": { "recursive_union": { "table_name": "<union2,3>", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 2, "table": { "table_name": "t1", "access_type": "ALL", "rows": 4, "filtered": 100, "attached_condition": "t1.lp = 'p1'" } } }, { "query_block": { "select_id": 3, "const_condition": "<cache>(!<in_optimizer>('p3',<exists>(subquery#4)))", "table": { "table_name": "t1", "access_type": "ALL", "rows": 4, "filtered": 100 }, "table": { "table_name": "<derived2>", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "4", "used_key_parts": ["p"], "ref": ["test.t1.lp"], "rows": 2, "filtered": 100 }, "subqueries": [ { "query_block": { "select_id": 4, "table": { "table_name": "<derived2>", "access_type": "ALL", "rows": 4, "filtered": 100, "attached_condition": "<cache>(convert('p3' using latin1)) = reachables.p" } } } ] } } ] } } } } } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ The predicate 'p3' not in (select * from reachables) is considered as a constant condition. It is evaluated only once at the first iteration. Then its result is cached and reused by each next iteration.

Igor Babaev (Inactive) made changes - 2017-04-20 03:34

Assignee	Igor Babaev [ igor ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Oleksandr Byelkin added a comment - 2017-04-21 16:14

OK to push

Oleksandr Byelkin added a comment - 2017-04-21 16:14 OK to push

Oleksandr Byelkin made changes - 2017-04-21 16:14

Assignee	Oleksandr Byelkin [ sanja ]	Igor Babaev [ igor ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Igor Babaev (Inactive) added a comment - 2017-04-22 16:15

The fix for this bug was pushed into the 10.2 tree

Igor Babaev (Inactive) added a comment - 2017-04-22 16:15 The fix for this bug was pushed into the 10.2 tree

Igor Babaev (Inactive) made changes - 2017-04-22 16:15

Fix Version/s		10.2.6 [ 22527 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:44

Workflow

MariaDB v3 [ 80346 ]

MariaDB v4 [ 151963 ]

MariaDB Server

NOT IN subquery predicate with recursive reference is ignored

Details

Description

Attachments

Activity

People

Dates

Git Integration