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

wrong result from subquery - too much optimization?

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 5.3.12, 5.5.33a, 5.5.40, 10.0.14
        • Fix Version/s: 10.0.18, 5.5.43
        • Component/s: Data Manipulation - Subquery, Optimizer
        • Labels:
          None
        • Environment:
          openSUSE 13.1, mariadb-5.5.33-2.2.x86_64 (from official distribution repo)
        • Sprint:
          5.5.45

          Description

          MariaDB gives me a wrong result when using a subquery.

          The affected query is (scroll down for full reproducer with table structure):

          SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias  
LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address;

          It looks like MariaDB optimizes the subquery to "always 1" if the other fields from the subquery are unused or only used in the JOIN condition.

          The workaround is to add __mailbox_username to the outer SELECT. This adds a superfluous column to the result, but gives the correct result for is_mailbox.

          This broke alias deletion in PostfixAdmin, see https://sourceforge.net/p/postfixadmin/bugs/325/

          The query works fine in MySQL (I tested on mysql-community-server-5.6.17-3.1.x86_64 on current openSUSE Factory), so this bug is specific to MariaDB.

          Full reproducer:

          a) create tables and insert some test rows:

          CREATE TABLE `alias` (
  `address` varchar(255) NOT NULL,
  `goto` text NOT NULL,
  `domain` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`address`),
  KEY `domain` (`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Aliases' ;

CREATE TABLE `mailbox` (
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 NOT NULL,
  `maildir` varchar(255) NOT NULL,
  `quota` bigint(20) NOT NULL DEFAULT '0',
  `local_part` varchar(255) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`username`),
  KEY `domain` (`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Mailboxes' ;

insert into mailbox (username,domain) values ('mailbox@example.com', 'example.com');

insert into alias (address, goto, domain) values ('alias@example.com', 'foo@example.com', 'example.com'), ('mailbox@example.com', 'mailbox@example.com', 'example.com');

          b) now run the query:

          SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias  
LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address;
| address             | is_mailbox | __is_mailbox |
| alias@example.com   |          1 |            1 |
| mailbox@example.com |          1 |            1 |
2 rows in set (0.00 sec)

          Needless to say that the result for alias@example.com is wrong.

          EXPLAIN clearly shows that the subquery is optimized away:

          EXPLAIN SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias
          LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address;

          id select_type table type possible_keys key key_len ref rows Extra
          1 SIMPLE alias index NULL PRIMARY 257 NULL 2 Using index

          Now add __mailbox_username to the outer SELECT:

          SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox, __mailbox_username FROM alias
          LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address;

          address is_mailbox __is_mailbox __mailbox_username
          alias@example.com 0 NULL NULL
          mailbox@example.com 1 1 mailbox@example.com

          2 rows in set (0.01 sec)

          The modified query contains the correct result (and a superfluous column in the resultset).

          For completeness, here's the EXPLAIN for the working query:

          EXPLAIN SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox, __mailbox_username FROM alias LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address;

          id select_type table type possible_keys key key_len ref rows Extra
          1 SIMPLE alias index NULL PRIMARY 257 NULL 2 Using index
          1 SIMPLE mailbox eq_ref PRIMARY PRIMARY 257 bugtest.alias.address 1 Using where; Using index

            Gliffy Diagrams

              Attachments

                Issue Links

                duplicates

                Bug - A problem which impairs or prevents the functions of the product. MDEV-6892 WHERE does not apply

                • Critical - Crashes, loss of data, severe memory leak.
                • Closed
                is duplicated by

                Bug - A problem which impairs or prevents the functions of the product. MDEV-6972 Left joined subquery gives wrong result

                • Critical - Crashes, loss of data, severe memory leak.
                • Closed

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  Thanks for the report. As a workaround, please try to set optimizer_switch='derived_merge=off'.

                  It is likely to be a duplicate of MDEV-6892, but I'm assigning it to Oleksandr Byelkin to make sure that the fix covers both test cases.

                  Show
                  elenst Elena Stepanova added a comment - Thanks for the report. As a workaround, please try to set optimizer_switch='derived_merge=off' . It is likely to be a duplicate of MDEV-6892 , but I'm assigning it to Oleksandr Byelkin to make sure that the fix covers both test cases.
                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  It looks like the bug is already fixed:

                  CREATE TABLE `alias` (
                  `address` varchar(255) NOT NULL,
                  `goto` text NOT NULL,
                  `domain` varchar(255) NOT NULL,
                  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
                  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
                  `active` tinyint(1) NOT NULL DEFAULT '1',
                  PRIMARY KEY (`address`),
                  KEY `domain` (`domain`)
                  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Aliases' ;
                  CREATE TABLE `mailbox` (
                  `username` varchar(255) NOT NULL,
                  `password` varchar(255) NOT NULL,
                  `name` varchar(255) CHARACTER SET utf8 NOT NULL,
                  `maildir` varchar(255) NOT NULL,
                  `quota` bigint(20) NOT NULL DEFAULT '0',
                  `local_part` varchar(255) NOT NULL,
                  `domain` varchar(255) NOT NULL,
                  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
                  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
                  `active` tinyint(1) NOT NULL DEFAULT '1',
                  PRIMARY KEY (`username`),
                  KEY `domain` (`domain`)
                  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Mailboxes' ;
                  insert into mailbox (username,domain) values ('mailbox@example.com', 'example.com');
                  Warnings:
                  Warning 1364 Field 'password' doesn't have a default value
                  Warning 1364 Field 'name' doesn't have a default value
                  Warning 1364 Field 'maildir' doesn't have a default value
                  Warning 1364 Field 'local_part' doesn't have a default value
                  insert into alias (address, goto, domain) values ('alias@example.com', 'foo@example.com', 'example.com'), ('mailbox@example.com', 'mailbox@example.com', 'example.com');
                  SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias
                  LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address;
                  address is_mailbox __is_mailbox
                  alias@example.com 0 NULL
                  mailbox@example.com 1 1
                  SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox, __mailbox_username FROM alias
                  LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address;
                  address is_mailbox __is_mailbox __mailbox_username
                  alias@example.com 0 NULL NULL
                  mailbox@example.com 1 1 mailbox@example.com
                  drop table alias, mailbox;

                  Show
                  sanja Oleksandr Byelkin added a comment - It looks like the bug is already fixed: CREATE TABLE `alias` ( `address` varchar(255) NOT NULL, `goto` text NOT NULL, `domain` varchar(255) NOT NULL, `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL DEFAULT '1', PRIMARY KEY (`address`), KEY `domain` (`domain`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Aliases' ; CREATE TABLE `mailbox` ( `username` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `name` varchar(255) CHARACTER SET utf8 NOT NULL, `maildir` varchar(255) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT '0', `local_part` varchar(255) NOT NULL, `domain` varchar(255) NOT NULL, `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `active` tinyint(1) NOT NULL DEFAULT '1', PRIMARY KEY (`username`), KEY `domain` (`domain`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Postfix Admin - Virtual Mailboxes' ; insert into mailbox (username,domain) values ('mailbox@example.com', 'example.com'); Warnings: Warning 1364 Field 'password' doesn't have a default value Warning 1364 Field 'name' doesn't have a default value Warning 1364 Field 'maildir' doesn't have a default value Warning 1364 Field 'local_part' doesn't have a default value insert into alias (address, goto, domain) values ('alias@example.com', 'foo@example.com', 'example.com'), ('mailbox@example.com', 'mailbox@example.com', 'example.com'); SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address; address is_mailbox __is_mailbox alias@example.com 0 NULL mailbox@example.com 1 1 SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox, __mailbox_username FROM alias LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address; address is_mailbox __is_mailbox __mailbox_username alias@example.com 0 NULL NULL mailbox@example.com 1 1 mailbox@example.com drop table alias, mailbox;
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  On revision 20109712aeb3d23e5e975780897ad236cbcd2ddc "(MDEV-6892: WHERE does not apply)":

                  MariaDB [test]> SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias   LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address;
+---------------------+------------+--------------+
| address             | is_mailbox | __is_mailbox |
+---------------------+------------+--------------+
| alias@example.com   |          0 |         NULL |
| mailbox@example.com |          1 |            1 |
+---------------------+------------+--------------+
2 rows in set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias   LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address;
+------+-------------+---------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+
| id   | select_type | table   | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra                    |
+------+-------------+---------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+
|    1 | SIMPLE      | alias   | index  | NULL          | PRIMARY | 257     | NULL               |    2 |   100.00 | Using index              |
|    1 | SIMPLE      | mailbox | eq_ref | PRIMARY       | PRIMARY | 257     | test.alias.address |    1 |   100.00 | Using where; Using index |
+------+-------------+---------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

                  On 8cbaafd22b145512cc91f7b512290320849e77bd (the revision right before 20109712aeb3d23e5e975780897ad236cbcd2ddc "(MDEV-6892: WHERE does not apply)")

                  MariaDB [test]> SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias  
    -> LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address;
+---------------------+------------+--------------+
| address             | is_mailbox | __is_mailbox |
+---------------------+------------+--------------+
| alias@example.com   |          1 |            1 |
| mailbox@example.com |          1 |            1 |
+---------------------+------------+--------------+
2 rows in set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias   LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address;
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
|    1 | SIMPLE      | alias | index | NULL          | PRIMARY | 257     | NULL |    2 |   100.00 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

                  So, I think we can be reasonably sure the bug was fixed along with MDEV-6892.

                  Show
                  elenst Elena Stepanova added a comment - On revision 20109712aeb3d23e5e975780897ad236cbcd2ddc "( MDEV-6892 : WHERE does not apply)": MariaDB [test]> SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address; +---------------------+------------+--------------+ | address | is_mailbox | __is_mailbox | +---------------------+------------+--------------+ | alias@example.com | 0 | NULL | | mailbox@example.com | 1 | 1 | +---------------------+------------+--------------+ 2 rows in set (0.00 sec) MariaDB [test]> EXPLAIN EXTENDED SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address; +------+-------------+---------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+---------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+ | 1 | SIMPLE | alias | index | NULL | PRIMARY | 257 | NULL | 2 | 100.00 | Using index | | 1 | SIMPLE | mailbox | eq_ref | PRIMARY | PRIMARY | 257 | test.alias.address | 1 | 100.00 | Using where; Using index | +------+-------------+---------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.00 sec) On 8cbaafd22b145512cc91f7b512290320849e77bd (the revision right before 20109712aeb3d23e5e975780897ad236cbcd2ddc "( MDEV-6892 : WHERE does not apply)") MariaDB [test]> SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias -> LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address; +---------------------+------------+--------------+ | address | is_mailbox | __is_mailbox | +---------------------+------------+--------------+ | alias@example.com | 1 | 1 | | mailbox@example.com | 1 | 1 | +---------------------+------------+--------------+ 2 rows in set (0.00 sec) MariaDB [test]> EXPLAIN EXTENDED SELECT address,coalesce(__is_mailbox,0) as is_mailbox, __is_mailbox FROM alias LEFT JOIN ( SELECT 1 as __is_mailbox, username as __mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS __mailbox ON __mailbox_username = address; +------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | alias | index | NULL | PRIMARY | 257 | NULL | 2 | 100.00 | Using index | +------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) So, I think we can be reasonably sure the bug was fixed along with MDEV-6892 .

                    People

                    • Assignee:
                      sanja Oleksandr Byelkin
                      Reporter:
                      cboltz Christian Boltz
                    • Votes:
                      1 Vote for this issue
                      Watchers:
                      4 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved:

                        Agile