Optimization error in BETWEEN command
I have a function that calculates a number: CREATE FUNCTION CP2 (iNum1 INT, iNum2 INT) RETURNS bigint (20) LANGUAGE SQL DETERMINISTIC BEGIN RETURN iNum1 * 10000 + iNum2; END I also tried to rewrite it in a DLL, but it's the same thing. If this function is called in the main query the optimization works, if instead it is called in a JOIN or in a SUBQUERY it doesn't work. Examples: SELECT * FROM TABLE2 WHERE id BETWEEN CP2 (1,0) AND CP2 (1,9999) /* work */ SELECT TABLE1.id,TABLE1.data,TABLE2.id,TABLE2.data FROM TABLE1 LEFT JOIN TABLE2 ON TABLE2.id BETWEEN CP2(TABLE1.id,1) AND CP2(TABLE1.id,9999) /* not work */
Thank you very much for the attention you will want to give me.