Shulou(Shulou.com)06/01 Report--

SELECT 45, COUNT (0)

FROM ac_dealer_sales ds

WHERE ds.user_id in (SELECT du.user_id FROM ac_dept_user du

WHERE du.is_main = 1 AND fun_get_team_id (du.dept_id) = 23)

The expected return value of this sql is 5000.

But the return value of the actual execution is 8000.

Split the sql into a select count (*) from tab where id in (selectd id from tab).

Tab = (create table tab as (subquery))

Sub query contains a UDF fun_get_team_id (int) return int

The result is consistent with the expected result.

Four versions were tested. 5.5.9 5.6.32 5.7.26 8.0.19 found that such a statement is only correct on 5.5.9

If UDF is in selectd * from tab where id in (select * from tab where UDF ()). )

In this form of sql statement. The mysql execution plan ignores the existence of UDF. As a result, the result is not accurate.

Rewrite the statement as a tab associated with a subquery. The result is correct.

SELECT COUNT (1)

FROM ac_dealer_sales a, (SELECT du.user_id FROM ac_dept_user du

WHERE du.is_main = 1 AND fun_get_team_id (du.dept_id) = 23) b

WHERE a.user_id = b.user_id

In this case, it can be executed in all four versions of mysql. But it is slow to execute on 5.6.32 5.7.26. 5.5.9 and 8.19 are relatively fast.

There is no problem with UDF alone in the select section or in the where section.

It has nothing to do with the implementation logic of UDF itself. But executing the plan in the IN () clause ignores the filter condition of UDF.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.