Hello,
Martin Hansson a écrit, Le 30.08.2010 11:33:
> Guilhem Bichot skrev 2010-08-25 19.50:
>> Hello Martin,
>>
>> Martin Hansson a écrit, Le 06.08.2010 15:26:
>>> #At file:///data0/martin/bzr/bug51070/5.1bt-guilhem/ based on
>>> revid:bjorn.munch@stripped
>>>
>>> 3478 Martin Hansson 2010-08-06
>>> Bug#51070: Query with a NOT IN subquery predicate returns a
>>> wrong result set
>> The code itself is ok to push. Tests' results seem ok (I guess they
>> would fail without the code fix?).
> That is your job to make sure ;-)
ok, will check.
>> If QA had a RQG grammar about partial matches, or could quickly write
>> one, it would be good to run it... it's hard to be 100% sure about
>> fixes to this subselect code.
> Good idea. Who is doing that stuff nowadays?
John Embretsen is the RQG guy nowadays.
> Actually I think most of
> this code should be removed and replaced with semijoin, flattening, or
> materialization because they are all superior.
yes, but it seems that at least semijoin and materialization cannot be
applied in certain cases _in_the_current_state_of_code_.
For example, materialization doesn't yet work with partial matches,
fixing this is todo:
https://intranet.mysql.com/worklog/Server-BackLog/?tid=3830
also described in "1.1 Subquery engine for hash semi-joins"
of
https://intranet.mysql.com/worklog/Server-Sprint/?tid=1110
I don't know whether there is a theoretical impossibility to replace all
IN->EXISTS with the new optimizer features (assuming we would implement
all the missing parts like WL#3830).
> IMHO there's no need to
> have an inferior solution hanging about that is near impossible to
> maintain and just as buggy. I'll ask the team about it.
>
> Best Regards
>
> Martin
--
Mr. Guilhem Bichot <guilhem.bichot@stripped>
Oracle / MySQL / Optimizer team, Lead Software Engineer
Bordeaux, France
www.oracle.com / www.mysql.com
