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

Query returns incorrect [null] in one column, for all rows but one (dataset and query attached)

    XMLWordPrintable

Details

    Description

      First of all, my apologies if I've overlooked something trivial or if this has already been reported. I'm a little shaky on proper terminology when it comes to "behind the curtain" of MariaDB so I may have missed something.

      I attach here a mysqldump (problem_db.sql) of three tables, along with a query (query.sql) which is returning incorrect results.

      (please note: the table stock_history is intentionally empty)

      Expected result:

      code po_detail_id po_id qty company_id
      236ae23b1f 4828949 9936 100 null
      8a7e75b224 4828956 9936 20 null
      fb02266724 4828961 9936 6 null
      8c87f5ef33 4829293 9936 6 null
      274e049393 4829437 9936 12 null
      748ad89040 4829839 9936 6 null
      f04b3a1572 4829900 9936 6 null

      Returned result:

      code po_detail_id po_id qty company_id
      236ae23b1f 4828949 9936 100 null
      8a7e75b224 4828956 9936 null null
      fb02266724 4828961 9936 null null
      8c87f5ef33 4829293 9936 null null
      274e049393 4829437 9936 null null
      748ad89040 4829839 9936 null null
      f04b3a1572 4829900 9936 null null

      As you can see, the qty column is null for all but the first row, which is incorrect. It should have a value in all rows.

      (As an aside, variations of this query/dataset can result in this bug returning null for all but the first row regardless of ORDER BY clause; the query/dataset presented here as a minimised case do not seem to do this)

      I have found a number of actions that will each cause the bug to be hidden and the expected results to be returned:

      • Modifying the WHERE clause in query.sql to "po_id >= 9936" will return the correct results for the rows presented above. I'm not sure if other rows are incorrect (you can always add LIMIT 7 to avoid seeing the other rows anyway)
      • Deleting the index on po_detail.po_id
      • Deleting the foreign key from po_queue.po_detail_id to po_detail.po_detail_id
      • Removing the SUM aggregate and GROUP BY in the query's derived table "po_added"
      • Removing the MAX aggregate and GROUP BY in the query's derived table "stock"
      • Removing the derived table "stock" from the query

      I have verified this bug on two systems (one under WSL) although both were using the same platform and MariaDB package.

      Thanks in advance for your help!

      Attachments

        1. problem_db.sql
          31 kB
          Darth Editous
        2. query.sql
          0.7 kB
          Darth Editous

        Activity

          People

            psergei Sergei Petrunia
            dartheditous Darth Editous
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.