0

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.

Nikhil Lende
  • 129
  • 7
  • In what way does it give the wrong result? – Nick Apr 25 '19 at 13:06
  • First query returns two hotel_id 125,126 but when placed under `IN` like in second query does not give any result. But when placed that values statically like `id IN (125,126)` then it gives result – Nikhil Lende Apr 25 '19 at 13:09
  • Using the function in the subquery should not affect that. You should be able to verify that by removing the function from the `WHERE` clause. – Nick Apr 25 '19 at 13:13
  • the `hotel_service.id` is the `hotel_season_detail.hotel_id`? not `hotel_service.hotel_id`? just to make sure the relationship is correct. the query looks fine and the functions should work as expected. – Sebastian Brosch Apr 25 '19 at 13:18
  • @SebastianBrosch `hotel_service` has multiple `hotel_season` with `hotel_id` as foreign key – Nikhil Lende Apr 25 '19 at 13:20
  • Is the `id` column of table `hotel_service` a hotel ID or the ID of a hotel service? Please show your table columns of `hotel_service` and `hotel_season_detail`. – Sebastian Brosch Apr 25 '19 at 13:22
  • @Nick Already try with this but no success. and Interestingly subquery giving me result if run individually – Nikhil Lende Apr 25 '19 at 13:22
  • @SebastianBrosch It's `id` – Nikhil Lende Apr 25 '19 at 13:23
  • 1
    `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 – Nikhil Lende Apr 25 '19 at 13:26
  • define "fail". Is there an error or you just get wrong data? – Thomas G Apr 25 '19 at 13:56

1 Answers1

0

I think it's an issue in Mysql when working with function because if same second query condition divided into two subqueries then My problem gets solved.

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 id in (select distinct(hsd.hotel_id) from hotel_season_detail hsd where getHotelMarkupManagementId(hsd.id,'AFG') is not NULL)

I have separated MySQL function in different subquery but combine same condition then not gives proper result. So i suggest use function of mysql different from other conditions, If conditions were BETWEEN OR > Operator.

Nikhil Lende
  • 129
  • 7