Will MariaDb preserves subquery result order in outer query ? - Source

Revision: 118368
User: Swastik K
Date: 2022-08-17 04:20

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?

https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/

MariaDb Version - 10.4.22
 
<<code>>
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
<</code>>

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Will MariaDb preserves subquery result order in outer query ? - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB