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

Wrong result with materialization, IN subquery, MyISAM.

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.28a, 5.3.11
    • 5.5.30, 5.3.13
    • None
    • RedHat EL6.3 x86_64 using 5.3.11 bintar and HEAD of lp:maria/5.3

    Description

      Hello and thank you for mariadb-5.3.11-MariaDB-linux-x86_64,

      This query:

      SELECT 
        count(*)
      FROM 
        wives, cats 
      WHERE 
        cats.cat_id = wives.cat_id AND 
        wives.cat_id IN (SELECT cat_id FROM kits) AND 
        wives.sack_id = 33479 AND wives.kit_id = 6;

      with materialization on returns 0, with it off returns 94.

      Explain plan with materialization on:

      +----+--------------+-------------+--------+---------------+--------------+---------+-------------------+------+--------------------------+
      | id | select_type  | table       | type   | possible_keys | key          | key_len | ref               | rows | Extra                    |
      +----+--------------+-------------+--------+---------------+--------------+---------+-------------------+------+--------------------------+
      |  1 | PRIMARY      | wives       | index  | PRIMARY       | PRIMARY      | 9       | NULL              | 3690 | Using where; Using index |
      |  1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 4       | func              |    1 | Using where              |
      |  1 | PRIMARY      | cats        | eq_ref | PRIMARY       | PRIMARY      | 4       | test2.kits.cat_id |    1 | Using where; Using index |
      |  2 | MATERIALIZED | kits        | index  | cat_id        | cat_id       | 4       | NULL              | 7578 | Using index              |
      +----+--------------+-------------+--------+---------------+--------------+---------+-------------------+------+--------------------------+

      Schema and data attached (4MB). I tested with 5.3.11 and the HEAD of lp:maria/5.3

      Thank you.

      Attachments

        Activity

          People

            igor Igor Babaev
            thatsafunnyname Peter (Stig) Edwards
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.