Does MariaDB kill the magic query?!

I’m sure that many of you use the magic query from Shlomi.
This query retrieves the difference between two status values with a single query.
I’m really fan of this kind of query and about this query in particular. This query is very useful to retrieve an information without to have to store a middle result.

Now, I tried this query on a MariaDB 5.5 server and I had a surprise :

Magic_query

The result was a bit strange, with negative or null values.

I’ve just had these server for few hours in my hands and I had no more time to analyze the situation.
I assume that the subquery optimisations in MariaDB can be involve here but it’s just an assumption.

Does anyone encountered the same issue? (with MariaDB or other distro)

Congratulations, you've read this post till the end !
If you enjoyed this article, please leave a comment


You can also subscribe to RSS feed or by email to automatically receive real-time notification of new posts

Thanks for your visit on MySQL+. Cédric

5 Comments

  1. I can only testify that when I presented this query in Percona Live London 2011, Sergei Golubchik was in the crowd and was skeptic about my reliance on optimizer behavior. Now I can see why. I’ll try and make this work on MariaDB.

    Reply
  2. Doesn’t work on MySQL 5.6, either.

    I don’t get NULL values, but I get negative values. It seems, the table gs1 is read before SLEEP(10) is executed. As for possible reasons, both MariaDB 5.3+ and MySQL 5.6+ try to delay derived table materialization. It seems, they have delayed it so much that it happens after the data is fetched into table gs1.

    It seems, one can get the query to work if they use a FROM(SELECT … UNION …) construct for taking both “snapshots”: https://gist.github.com/spetrunia/5055059 .

    Reply
    • @Sergei @Shlomi : Thx for these answers and the possible solution.
      I’m surprise to not had more comments about that, am I the only one to use this query? (sure I’m not)

      Reply
    • Thanks, grammatical error corrected.
      English is not my mother tongue and I like to learn more.
      Now, you are free to be unpleasant like you are also free to not read my blog.

      Reply

Leave a Comment.