From: Zhixuan Zhu
Date: March 4 2010 5:00pm
Subject: Question about the subquery transform
List-Archive: http://lists.mysql.com/internals/37773
Message-Id: <35dc5cef1003040900p6e6bd450l1184b333c0581830@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Hello,
We're using mysql-5.1.40.
I have a question about the subquery transform in MySQL's optimizer.
Given the=A0following query:
select 1 from sub1 where (c1,c2) in (select min(c1),min(c2) from sub2
group by c3 having count(c1) > 4);
It generates the following extended plan output:
mysql> show warnings;
+-------+------+-----------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------------------------------------+
| Level | Code | Message
=A0=A0=A0=A0=A0=A0=A0 |
+-------+------+-----------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------------------------------------+
| Note=A0 | 1003 | select 1 AS `1` from `qrefd01m`.`sub1` where
((`qrefd01m`.`sub1`.`c1`,`qrefd01m`.`sub1`.`c2`),(sel=
ect
min(`qrefd01m`.`sub2`.`c1`) AS `min(c1)`,min(`qrefd01m`.`sub2`.`c2`)
AS `min(c2)` from `qrefd01m`.`sub2` group by `qrefd01m`.`sub2`.`c3`
having ((count(`qrefd01m`.`sub2`.`c1`) > 4) and
(((`qrefd01m`.`sub1`.`c1`) =3D min(`qrefd01m`.`sub2`.`c1`)) or
isnull(min(`qrefd01m`.`sub2`.`c1`))) and
(((`qrefd01m`.`sub1`.`c2`) =3D min(`qrefd01m`.`sub2`.`c2`)) or
isnull(min(`qrefd01m`.`sub2`.`c2`))) and
(min(`qrefd01m`.`sub2`.`c1`)) and
(min(`qrefd01m`.`sub2`.`c2`))))) |
+-------+------+-----------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------------------------------------+
1 row in set (0.00 sec)
I found this transformation very confusing. MySQL pushs the join
condition down to the subquery, which is smart. But I don't understand
why a "or isnull" has to be attached to the join. This is in the=A0WHERE
clause, and it's a IN variant (not NOTIN), can't NULL result be safely
ignored? Even if it can't,=A0the optimizer=A0again=A0adds a
checker to the having clause, which exactly
contradict the "or isnull" filter it adds earlier.
So each of the item=A0on the SELECT clause of the IN subquery is transforme=
d to
(cache=3Ditem or item is null) having item is not null,
which to my understanding is equivalent to (cache =3D item).
I must have missed something or/and misunderstand something=A0about this
transformation. Please advice.
Thanks,
Grace
--
Zhixuan Zhu
http://yunpingzhou.spaces.live.com/