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

wrong result from subquery - too much optimization?

    Details

    • 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

        Attachments

          Issue Links

            Activity

              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: