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/