Hi Guilhem,
Guilhem Bichot wrote:
> Hello Evgeny,
>
> Evgeny Potemkin a écrit, Le 13.10.2009 11:38:
>> #At file:///work/bzrroot/45174-bug-azalea/ based on
>> revid:alik@stripped
>>
>> 2814 Evgeny Potemkin 2009-10-13
>> Bug#45174: Incorrectly applied equality propagation caused wrong
>> result
>> on a query with a materialized semi-join.
>
>> === modified file 'mysql-test/t/subselect_sj.test'
>> --- a/mysql-test/t/subselect_sj.test 2009-03-19 17:03:58 +0000
>> +++ b/mysql-test/t/subselect_sj.test 2009-10-13 09:38:46 +0000
>> @@ -216,4 +216,42 @@ WHERE HAVING X > '2012-12-12';
>> drop table t1, t2;
>>
>> +--echo #
>> +--echo # Bug#45174: Incorrectly applied equality propagation caused
>> wrong
>> +--echo # result on a query with a materialized semi-join.
>> +--echo #
>> +
>> +CREATE TABLE `CC` (
>> + `pk` int(11) NOT NULL AUTO_INCREMENT,
>> + `varchar_key` varchar(1) NOT NULL,
>> + `varchar_nokey` varchar(1) NOT NULL,
>> + PRIMARY KEY (`pk`),
>> + KEY `varchar_key` (`varchar_key`)
>> +);
>> +
>> +INSERT INTO `CC` VALUES
>>
> (11,'m','m'),(12,'j','j'),(13,'z','z'),(14,'a','a'),(15,'',''),(16,'e','e'),(17,'t','t'),(19,'b','b'),(20,'w','w'),(21,'m','m'),(23,'',''),(24,'w','w'),(26,'e','e'),(27,'e','e'),(28,'p','p');
>
>>
>> +
>> +CREATE TABLE `C` (
>> + `varchar_nokey` varchar(1) NOT NULL
>> +);
>> +
>> +INSERT INTO `C` VALUES
>>
> ('v'),('u'),('n'),('l'),('h'),('u'),('n'),('j'),('k'),('e'),('i'),('u'),('n'),('b'),('x'),(''),('q'),('u');
>
>>
>> +
>> +EXPLAIN EXTENDED SELECT varchar_nokey
>> +FROM C +WHERE ( `varchar_nokey` , `varchar_nokey` ) IN ( +SELECT
>> `varchar_key` , `varchar_nokey` +FROM CC +WHERE `varchar_nokey` <
>> 'n' XOR `pk` ) ;
>> +
>> +SELECT varchar_nokey
>> +FROM C +WHERE ( `varchar_nokey` , `varchar_nokey` ) IN ( +SELECT
>> `varchar_key` , `varchar_nokey` +FROM CC +WHERE `varchar_nokey` <
>> 'n' XOR `pk` ) ;
>
> Errrr here I block: the commit comment says the bug is about wrong
> equality propagation, but I don't see any equality or comparisons
> between columns of different tables, to me the query above is
>
> SELECT stuff_from_C WHERE stuff_from_C IN (uncorrelated SELECT FROM CC
> WHERE condition_involving_only_CC)...?
>
> What are the equality propagations in the testcase?
This query has an IN subselect which optimizer converts to a semi-join.
Beside pulling up subquery tables it appends subselect's WHERE expr to the
WHERE expr of the upper query and adds following conditions:
"C.`varchar_nokey`=CC.`varchar_key` and C.`varchar_nokey`= CC.`varchar_nokey`"
to the WHERE clause.
All this is done by the JOIN::flatten_subqueries. After that WHERE expr is
optimized and equality propagation is applied.
>
> Thanks!
>
Regards, Evgen.
