[MDEV-31011] `COUNT()` function returns random values ocassionally Created: 2023-04-05  Updated: 2023-04-26

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Hunter LaFaille Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Attachments: PNG File Explain Extended Query.png    

 Description   

Hello,

If there is an error in my query, please let me know. Our internal ERP software runs this query to fetch a list of all SKUs shared between our two companies, and uses `COUNT(CASE)` functions to get the the count of rows in the inventory table of both companies.

Pastebin to Query

The issue comes up the first 27 SKUs return quantity perfectly, and then from there the in stock quantity for both companies get seemingly multiplied by itself 2 or 3 times randomly . If needed, I can potentially provide a mariadbdump for both companies inventory tables (in different databases) and the central products table.

 Comments   
Comment by Alice Sherepa [ 2023-04-06 ]

What version do you use?
You can upload mysqldump to ftp.askmonty.org/private (https://mariadb.com/kb/en/meta/mariadb-ftp-server/), so we will try to repeat it on our side. Please provide also your .cnf file(s)

Comment by Hunter LaFaille [ 2023-04-06 ]

Hello Alice,

I had this issue on v10.9, and updated our company to 10.11 hoping that maybe it would fix it. It did not.

I have uploaded the files to ftp://ftp.mariadb.org/private/ with the file name MDEV-31011_sql_dump.tgz

e: here is an updated query with the proper database names: https://pastebin.com/Wkrs3NUB

Comment by Hunter LaFaille [ 2023-04-11 ]

I apologize for my urgency, but our company relies on this query for our inventory. Is there any updates on this?

Comment by Alice Sherepa [ 2023-04-11 ]

Thank you for the dump, I'm trying to repeat the problem on my side, but currently I'm getting 454rows every time. Have you tried to run "analyze table" for all involved tables? Could you please describe the load, etc. or more info on how to repeat?

Comment by Hunter LaFaille [ 2023-04-11 ]

Thank you for taking the time to respond to me, Alice. The MariaDB server runs on a 12 core VPS from a cloud hosting provider in Docker, with very minimal load as our company only has a few employees.

The issue is when you run this query: https://pastebin.com/Wkrs3NUB

We have a table in the central_products database called products, that defines the SKUs shared between the Viixim and Evrymm databases (each database is its own company in real life). The query provided in this pastebin counts the inventory from both databases.

e: I just ran an ANALYZE statement on all three databases involved and nothing changed with this query.

Comment by Alice Sherepa [ 2023-04-14 ]

Could you please provide the output of EXPLAIN EXTENDED query , ANALYZE FORMAT=JSON query for both correct and incorrect cases? After you've got the wrong results - is it constantly wrong afterwards or is it random all the time?

Comment by Hunter LaFaille [ 2023-04-14 ] 

{
 
  "query_optimization": {
 
    "r_total_time_ms": 0.255984894
 
  },
 
  "query_block": {
 
    "select_id": 1,
 
    "r_loops": 1,
 
    "r_total_time_ms": 7948.956532,
 
    "const_condition": "1",
 
    "filesort": {
 
      "sort_key": "central_products.products.incremental",
 
      "r_loops": 1,
 
      "r_total_time_ms": 0.137216722,
 
      "r_limit": 200,
 
      "r_used_priority_queue": false,
 
      "r_output_rows": 456,
 
      "r_buffer_size": "10Kb",
 
      "r_sort_mode": "sort_key,rowid",
 
      "temporary_table": {
 
        "nested_loop": [
 
          {
 
            "table": {
 
              "table_name": "products",
 
              "access_type": "ALL",
 
              "r_loops": 1,
 
              "rows": 455,
 
              "r_rows": 456,
 
              "r_table_time_ms": 0.26928268,
 
              "r_other_time_ms": 0.106282082,
 
              "filtered": 100,
 
              "r_filtered": 100
 
            }
 
          },
 
          {
 
            "block-nl-join": {
 
              "table": {
 
                "table_name": "inventory",
 
                "access_type": "ALL",
 
                "r_loops": 1,
 
                "rows": 4725,
 
                "r_rows": 4842,
 
                "r_table_time_ms": 1.481154951,
 
                "r_other_time_ms": 186.0375653,
 
                "filtered": 100,
 
                "r_filtered": 100
 
              },
 
              "buffer_type": "flat",
 
              "buffer_size": "256Kb",
 
              "join_type": "BNL",
 
              "attached_condition": "trigcond(Viixim.inventory.product_code = central_products.products.incremental)",
 
              "r_filtered": 0.224280238
 
            }
 
          },
 
          {
 
            "block-nl-join": {
 
              "table": {
 
                "table_name": "inventory",
 
                "access_type": "ALL",
 
                "r_loops": 1,
 
                "rows": 10770,
 
                "r_rows": 11384,
 
                "r_table_time_ms": 5.897663477,
 
                "r_other_time_ms": 7754.936196,
 
                "filtered": 100,
 
                "r_filtered": 100
 
              },
 
              "buffer_type": "incremental",
 
              "buffer_size": "256Kb",
 
              "join_type": "BNL",
 
              "attached_condition": "trigcond(Evrymm.inventory.product_code = central_products.products.incremental)",
 
              "r_filtered": 0.373660933
 
            }
 
          }
 
        ]
 
      }
 
    }
 
  }
 
}

When running the problematic query, the COUNT function seems to multiply the actual inventory by 2 or 3 times seemingly randomly, and it's always on the same SKU (central_products.products.incremental [PK]). I can provide a couple of screenshots with actual count from the inventory tables versus what the problematic query is reporting.

Generated at Thu Feb 08 10:20:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.