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

LP:613029 - Wrong result with materialization and semijoin, and valgrind warnings in Protocol::net_store_data with materialization for implicit grouping

        Details

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

          Description

          Queries such as

          SELECT table1 .`col_varchar_nokey` , MAX( table1 .`col_int_key` )
          FROM C table1 JOIN (
          SELECT *
          FROM B ) table2 ON table2 .`pk` = table1 .`pk`
          WHERE ( table1 .`col_int_nokey` , table2 .`pk` ) IN (
          SELECT `col_int_nokey` , `col_int_key`
          FROM C ) ;

          SELECT *
          FROM (
          SELECT table1 .`col_varchar_nokey` , MAX( table1 .`col_int_key` )
          FROM C table1 JOIN (
          SELECT *
          FROM B ) table2 ON table2 .`pk` = table1 .`pk`
          WHERE ( table1 .`col_int_nokey` , table2 .`pk` ) IN (
          SELECT `col_int_nokey` , `col_int_key`
          FROM C ) ) AS
          FROM_SUBQUERY /* TRANSFORM_OUTCOME_UNORDERED_MATCH */;

          cause valgrind warnings when run with semijoin=off,partial_match_rowid_merge=off. With a suitable client, one can also observe garbage data being sent to the client.

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                9 older comments
                Hide
                Permalink
                psergey Sergei Petrunia added a comment -

                Re: Wrong result with materialization and semijoin, and valgrind warnings in Protocol::net_store_data with materialization for implicit grouping
                > Assigned to Sergey because there is a wrong result with a semijoin plan.

                The problem has nothing to do with semi-joins, or subqueries:

                create table t11 (a int primary key, b int);
                insert into t11 values (1,1),(2,2);

                create table t10 (a int, b int, c int);
                insert into t10 values (10, NULL, NULL), (10, NULL, NULL);

                MariaDB [j12]> explain select t11.b, max(t10.b) from t10, t11 where t10.c<1 and t11.a=2;
                ------------------------------------------------------------------------+

                id select_type table type possible_keys key key_len ref rows Extra

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

                1 SIMPLE t11 const PRIMARY PRIMARY 4 const 1  
                1 SIMPLE t10 ALL NULL NULL NULL NULL 2 Using where

                ------------------------------------------------------------------------+
                2 rows in set (2.63 sec)

                MariaDB [j12]> select t11.b, max(t10.b) from t10, t11 where t10.c<1 and t11.a=2;
                ----------------+

                b max(t10.b)

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

                2 NULL

                ----------------+
                1 row in set (3.31 sec)

                MariaDB [j12]> alter table t11 drop primary key;
                Query OK, 2 rows affected (0.01 sec)
                Records: 2 Duplicates: 0 Warnings: 0

                MariaDB [j12]> select t11.b, max(t10.b) from t10, t11 where t10.c<1 and t11.a=2;
                ----------------+

                b max(t10.b)

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

                NULL NULL

                ----------------+
                1 row in set (3.66 sec)

                Show
                psergey Sergei Petrunia added a comment - Re: Wrong result with materialization and semijoin, and valgrind warnings in Protocol::net_store_data with materialization for implicit grouping > Assigned to Sergey because there is a wrong result with a semijoin plan. The problem has nothing to do with semi-joins, or subqueries: create table t11 (a int primary key, b int); insert into t11 values (1,1),(2,2); create table t10 (a int, b int, c int); insert into t10 values (10, NULL, NULL), (10, NULL, NULL); MariaDB [j12] > explain select t11.b, max(t10.b) from t10, t11 where t10.c<1 and t11.a=2; --- ----------- ----- ----- ------------- ------- ------- ----- ---- ------------+ id select_type table type possible_keys key key_len ref rows Extra --- ----------- ----- ----- ------------- ------- ------- ----- ---- ------------+ 1 SIMPLE t11 const PRIMARY PRIMARY 4 const 1   1 SIMPLE t10 ALL NULL NULL NULL NULL 2 Using where --- ----------- ----- ----- ------------- ------- ------- ----- ---- ------------+ 2 rows in set (2.63 sec) MariaDB [j12] > select t11.b, max(t10.b) from t10, t11 where t10.c<1 and t11.a=2; ----- -----------+ b max(t10.b) ----- -----------+ 2 NULL ----- -----------+ 1 row in set (3.31 sec) MariaDB [j12] > alter table t11 drop primary key; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [j12] > select t11.b, max(t10.b) from t10, t11 where t10.c<1 and t11.a=2; ----- -----------+ b max(t10.b) ----- -----------+ NULL NULL ----- -----------+ 1 row in set (3.66 sec)
                Hide
                Permalink
                psergey Sergei Petrunia added a comment -

                Re: Wrong result with materialization and semijoin, and valgrind warnings in Protocol::net_store_data with materialization for implicit grouping
                The real problem is: when the query has implicit grouping, and it produces no rows, all columns of non-const tables get NULL values. However, columns of const tables remain non-NULL, and that is the cause of incorrect results.

                Show
                psergey Sergei Petrunia added a comment - Re: Wrong result with materialization and semijoin, and valgrind warnings in Protocol::net_store_data with materialization for implicit grouping The real problem is: when the query has implicit grouping, and it produces no rows, all columns of non-const tables get NULL values. However, columns of const tables remain non-NULL, and that is the cause of incorrect results.
                Hide
                Permalink
                psergey Sergei Petrunia added a comment -

                Re: Wrong result with materialization and semijoin, and valgrind warnings in Protocol::net_store_data with materialization for implicit grouping
                .. and this can be repeated on the current MySQL 5.1

                Show
                psergey Sergei Petrunia added a comment - Re: Wrong result with materialization and semijoin, and valgrind warnings in Protocol::net_store_data with materialization for implicit grouping .. and this can be repeated on the current MySQL 5.1
                Hide
                Permalink
                psergey Sergei Petrunia added a comment -

                Re: Wrong result with materialization and semijoin, and valgrind warnings in Protocol::net_store_data with materialization for implicit grouping
                Moving off the last issue to separate bug, bug #844997

                Show
                psergey Sergei Petrunia added a comment - Re: Wrong result with materialization and semijoin, and valgrind warnings in Protocol::net_store_data with materialization for implicit grouping Moving off the last issue to separate bug, bug #844997
                Hide
                Permalink
                ratzpo Rasmus Johansson added a comment -

                Launchpad bug id: 613029

                Show
                ratzpo Rasmus Johansson added a comment - Launchpad bug id: 613029

                  People

                  • Assignee:
                    psergey Sergei Petrunia
                    Reporter:
                    philipstoev Philip Stoev
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    0 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: