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?
Thanks!
