Inconsistency with Lateral
Hi Guys,
I'm using MySQL 8.0.20 on Linux, I'm trying to use lateral on a query.
I have extrange behaviour because I run this query:
select p.id, p.nombre, p.descripcion, p.imgSrc, p.imgAlt, p.precio,
p.precioRegular, p.precioOferta, p.onSale, p.stockStatus, p.stockQuantity,
p.link, p.destacado, p.fechaCreacion, p.fechaModifica, p.tags, t.nombre as fk_tienda_nombre,
t.id as fk_tienda_id,
t.isDelivery, t.isCreditCard, t.isPickup, th.status as storeStatus,
th.startTime, th.endTime
from productos2 p
inner join tiendas t on p.fk_tienda = t.id
inner join products_categories_rel_2 pcr on p.link = pcr.fk_product
inner join products_category_2 pc on pcr.fk_productCategory = pc.codigo
left join lateral(
select * from tienda_horario
where tienda_horario.fk_tienda = p.fk_tienda
and tienda_horario.dayOfWeek = "X"
and (time(now()) between startTime and endTime or time(now()) < startTime)
order by tienda_horario.startTime limit 1
)
th on th.fk_tienda = p.fk_tienda
where p.fk_tienda in (17,18) group by p.link order by fechaCreacion DESC limit 0, 12
and return null on storeStatus, startTime, endTime (tienda_horario table lateral join) but I'm centainly the table has corresponding data. If I run this query (note the only difference on the where)
select p.id, p.nombre, p.descripcion, p.imgSrc, p.imgAlt, p.precio,
p.precioRegular, p.precioOferta, p.onSale, p.stockStatus, p.stockQuantity,
p.link, p.destacado, p.fechaCreacion, p.fechaModifica, p.tags, t.nombre as fk_tienda_nombre,
t.id as fk_tienda_id,
t.isDelivery, t.isCreditCard, t.isPickup, th.status as storeStatus,
th.startTime, th.endTime
from productos2 p
inner join tiendas t on p.fk_tienda = t.id
inner join products_categories_rel_2 pcr on p.link = pcr.fk_product
inner join products_category_2 pc on pcr.fk_productCategory = pc.codigo
left join lateral(
select * from tienda_horario
where tienda_horario.fk_tienda = p.fk_tienda
and tienda_horario.dayOfWeek = "X"
and (time(now()) between startTime and endTime or time(now()) < startTime)
order by tienda_horario.startTime limit 1
)
th on th.fk_tienda = p.fk_tienda
where p.precio >= 1800 and p.precio <= 1800 group by p.link order by fechaCreacion DESC limit 0, 12
storeStatus, startTime and endTime return with the corrrect values. It looks to me like a MySQL problem, but I'm not certain.
Any insights?