Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25231

A subquery stoped returning correct data after mariadb upgrade from 10.4.10 onwards

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.4.11, 10.4.12, 10.4.13, 10.4.14, 10.4.15, 10.4.16, 10.4.17, 10.4.18
    • Fix Version/s: 10.4
    • Component/s: Optimizer, Server
    • Environment:
      Ubuntu focal, Debian buster

      Description

      We have a complex query that used to work fine and return correct data, but after upgrade from 10.4.10 onwards it NULL for column 2 and 3.

      The query is:

      SELECT SQL_CALC_FOUND_ROWS BA.ARTICLE_ID,AMOUNT_LAST_UPDATE,STORE_AMOUNT_ALL
        FROM b_articles BA
      LEFT JOIN ( SELECT ARTICLE_ID,MAX(AMOUNT_LAST_UPDATE) AMOUNT_LAST_UPDATE,SUM(TBAV.AMOUNT) STORE_AMOUNT_ALL
                      FROM b_availability TBAV
                      WHERE TBAV.BARSY_ID IN (1,194,314,134,376,164,346,284,361,344,224,254,391)
                     GROUP BY TBAV.ARTICLE_ID
                  ) BAV USING (ARTICLE_ID)
      WHERE
      BA.PARENT_ID IS NULL 
      AND BA.PICTURE IS NOT NULL 
      AND BA.ARTICLE_ID IN ( SELECT DISTINCT ARTICLE_ID FROM b_article_cats ) 
      AND BA.ARTICLE_ID IN ( SELECT DISTINCT IBA.ARTICLE_ID
                               FROM b_articles IBA
                              INNER JOIN b_availability IBAV USING (ARTICLE_ID)
                               LEFT JOIN (b_article_details IBAD  
                                         INNER JOIN b_article_detail_key_values IBADKV USING (DETAIL_ID) ) ON IBA.ARTICLE_ID = IBAD.ARTICLE_ID AND IBAD.KEY_ID = -1                                   
                               WHERE (IBADKV.DETAIL_VALUE IS NULL 
                                     OR IBADKV.DETAIL_VALUE = "" 
                                     OR IBA.LAST_UPDATE > IBADKV.DETAIL_VALUE 
                                     OR IBAV.AMOUNT_LAST_UPDATE > IBADKV.DETAIL_VALUE
                                    ) 
                                AND IBAV.BARSY_ID IN (1,194,314,134,376,164,346,284,361,344,224,254,391)
                            )
      

      Using mariadb 10.4.10 binary the query would return something like:
      173984 2018-11-01 13:10:25 0.000000000
      174044 2018-11-01 13:10:25 0.000000000
      .....
      But if I upgrade the package it start returning:
      173984 NULL NULL
      174044 NULL NULL
      ....

      I cannot provide recreation steps since the data is private, but I'm attaching the result from running ANALYZE on the query.
      the result from 10.4.11 and 10.4.18 are identical, but I'm attaching both of them for completenes

        Attachments

        1. msql-10.4.10.txt
          3 kB
        2. msql-10.4.11.txt
          3 kB
        3. msql-10.4.18.txt
          3 kB

          Activity

            People

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            lukav Anton Avramov
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: