MySQL Forums
Forum List  »  Newbie

Inconsistency with Lateral
Posted by: jose Concha
Date: July 08, 2020 02:29PM

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?

Options: ReplyQuote


Subject
Written By
Posted
Inconsistency with Lateral
July 08, 2020 02:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.