I have created Mysql function which gives result integer or NULL. It works fine for all values. If use this function in simple query it aslo works as expected like following query :
select distinct(hsd.hotel_id)
from hotel_season_detail hsd
where '2019-04-26' BETWEEN hsd.season_fromdate and hsd.season_todate and hsd.stop_sale >'2019-04-26' and getHotelMarkupManagementId(hsd.id,'AFG') is NOT NULL
getHotelMarkupManagementId()
is user defined mysql function. As above query return two hotel ids which is already exist in hotel table.
But when this query added as subquery like
select * from hotel_service
where id in
(select distinct(hsd.hotel_id)
from hotel_season_detail hsd where '2019-04-26' BETWEEN hsd.season_fromdate and hsd.season_todate and hsd.stop_sale >'2019-04-26' and getHotelMarkupManagementId(hsd.id,'AFG')
is NOT NULL)
It gives Wrong result. It does not work as expected.
select * from hotel_service where id in (125,126)
gives result and subquery also gives id 125 and 126 as result if run individually. but when added as subquery it fails
Should this behavior due to mysql function
in IN
clause? Please Suggest.