Will MariaDb preserves subquery result order in outer query ?
For the below query I'm using ORDER BY in one of the subqueries to fetch recently added products for some E-Commerce use case. When the query gives results order of subquery is preserved in final query results also, though order in which rows are stored in shopify_variants are different.
I referred below link and it says subquery order will not be preserved, but in my case it is not so. Is this purely accidental?
MariaDb Version - 10.4.22
WITH products AS ( SELECT v.product_id, v.variant_id, v.title, v.price, v.position, v.image_url, i.url FROM shopify_variants v JOIN shopify_images i WHERE v.product_id = i.product_id AND v.product_id IN ( SELECT * FROM ( SELECT p.product_id FROM shopify_products p JOIN shopify_collections c WHERE p.product_id = c.product_id AND c.bot_ref = 27674 AND c.shop_domain = "mystore.com" AND p.status LIKE "ACTIVE" AND c.title="Casual" GROUP BY p.product_id ORDER BY p.created_at DESC LIMIT 30 ) AS limiter)) SELECT products.product_id, products.position, products.variant_id, products.title, products.price, products.url, products.image_url, pr.handle, pr.description, pr.title AS producttitle FROM products JOIN shopify_products pr WHERE products.product_id =pr.product_id