Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.33a, 5.5.40, 10.0.14
-
None
-
openSUSE 13.1, mariadb-5.5.33-2.2.x86_64 (from official distribution repo)
-
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
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;
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.
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 sanja to make sure that the fix covers both test cases.