Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.3.12, 5.5.40, 10.0.12, 10.0.14
    • 5.5.43, 10.0.18
    • Optimizer
    • None
    • Ubunut/LXC

    Description

      Thx to Andreas Kretschmer for pointing out.

      create table t1 (id int);
      create table t2 (id int);
      insert into t1 values(1),(2),(3);
      insert into t2 values(4),(5),(6)
      select x.id, message from (select id from t1) x left join
      (select id, 1 as message from t2) y on x.id=y.id
      where coalesce(message,0) <> 0;
      # 5.6.19-0ubuntu0.14.04.1
      Empty set (0,01 sec)
      # 10.0.12-MariaDB-1~trusty-wsrep-log
      +------+---------+
      | id   | message |
      +------+---------+
      |    1 |    NULL |
      |    2 |    NULL |
      |    3 |    NULL |
      +------+---------+

      Attachments

        Issue Links

          Activity

            The fix for this bug may be useful for MDEV-7893

            psergei Sergei Petrunia added a comment - The fix for this bug may be useful for MDEV-7893

            The first bit of review feedback is the same as during the previous iteration:
            why does Item_direct_view_ref::update_used_tables() call check_null_ref()?

            check_null_ref analyzes null_ref_table->null_row which is generally not set
            to a meaningful value during query optimization.

            psergei Sergei Petrunia added a comment - The first bit of review feedback is the same as during the previous iteration: why does Item_direct_view_ref::update_used_tables() call check_null_ref()? check_null_ref analyzes null_ref_table->null_row which is generally not set to a meaningful value during query optimization.

            Sorry, I just forget to remove the line

            revision-id: 21f2a0e32bca94bdacc0c56a9b3531e7f264886c
            parent(s): cc84ac3be41d9d6ac480d55449d5bf4e324cca10
            committer: Oleksandr Byelkin
            branch nick: server
            timestamp: 2015-04-22 10:39:13 +0200
            message:

            MDEV-6892: WHERE does not apply

            Taking into account implicit dependence of constant view field from nullable table of left join added.

            Fixed finding real table to check if it turned to NULL (materialized view & derived taken into account)

            Removed incorrect uninitialization.

            —

            sanja Oleksandr Byelkin added a comment - Sorry, I just forget to remove the line revision-id: 21f2a0e32bca94bdacc0c56a9b3531e7f264886c parent(s): cc84ac3be41d9d6ac480d55449d5bf4e324cca10 committer: Oleksandr Byelkin branch nick: server timestamp: 2015-04-22 10:39:13 +0200 message: MDEV-6892 : WHERE does not apply Taking into account implicit dependence of constant view field from nullable table of left join added. Fixed finding real table to check if it turned to NULL (materialized view & derived taken into account) Removed incorrect uninitialization. —

            Ok to push.

            psergei Sergei Petrunia added a comment - Ok to push.
            igor Igor Babaev added a comment -

            It looks like still have some problems with a variant of the reported query:

            MariaDB [test]> explain extended select x.id, message from (select id from t1) x left join (select id, 1 as message from t2) y on x.id=y.id where message <> 0;
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
            | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
            |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |                                                 |
            |    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (flat, BNL join) |
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
             
            MariaDB [test]> show warnings;
            +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
            | Level | Code | Message                                                                                                                                      |
            +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
            | Note  | 1003 | select `test`.`t1`.`id` AS `id`,1 AS `message` from `test`.`t1` left join (`test`.`t2`) on(`test`.`t2`.`id` = `test`.`t1`.`id`) where 1 <> 0 |
            +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
            

            I would expect conversion of left join into inner join here as the condition message <> 0 rejects null complements.

            I will fix the problem in the patch for MDEV-19778.

            igor Igor Babaev added a comment - It looks like still have some problems with a variant of the reported query: MariaDB [test]> explain extended select x.id, message from (select id from t1) x left join (select id, 1 as message from t2) y on x.id=y.id where message <> 0; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | | | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+   MariaDB [test]> show warnings; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`id` AS `id`,1 AS `message` from `test`.`t1` left join (`test`.`t2`) on(`test`.`t2`.`id` = `test`.`t1`.`id`) where 1 <> 0 | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ I would expect conversion of left join into inner join here as the condition message <> 0 rejects null complements. I will fix the problem in the patch for MDEV-19778 .

            People

              sanja Oleksandr Byelkin
              erkules erkan yanar
              Votes:
              3 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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