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

Complex query in Store procedure corrupts results

    XMLWordPrintable

Details

    Description

      When running the complex query repeatedly in the stored procedure random garbage or empty results are returned on occasion.

      Data dump with SP and test are contained in attachment. With the SP rewritten to store the output in a table the results that are correct and incorrect are as follows:

      +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
      | customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
      +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
      | reference1        |            1 | NULL            | NULL       | NULL                 | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | NULL           |                   0 |
      +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
      

      Under high concurrency (should be a static result) from 10000 iterations the following rows were inserted:

      mysql ds2 -e "select * from results where customerReference<>'reference1'" 
      +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
      | customerReference              | policyNumber | schemeReference | schemeName | applicationReference                               | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate      | isBusinessException |
      +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
      | w  (       ??      0??K    |            0 | NULL            | NULL       | NULL                                               | 2020-01-01 10:10:10     | NULL                  |   2147483647 |            NULL |   NULL |        0 | NULL                |                NULL |
      |                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      | w  rence1                     |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | NULL                |                NULL |
      | w  rence1                     |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |            NULL |   NULL |        0 | 0000-00-00 00:00:00 |                NULL |
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      | w  rence1                     |            1 |                 | NULL       | NULL                                               | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |            NULL |      1 |     NULL | NULL                |                NULL |
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      | w  rence1                     |            1 |                 |            | NULL                                               | NULL                    | 2020-01-01 10:10:10   |            1 |            NULL |   NULL |     NULL | 0000-00-00 00:00:00 |                   0 |
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      | w  rence1                     |            1 |                 | NULL       |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | NULL                |                NULL |
      |                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      | w  rence1                     |            1 |                 | NULL       |                                                    | NULL                    | NULL                  |            1 |               1 |      1 |        0 | NULL                |                NULL |
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      | w  (       ??      0??K    |            0 | NULL            | NULL       |                                                    | 2020-01-01 10:10:10     | NULL                  |   2147483647 |               0 |      1 |        0 | 7036-87-44 17:76:64 |                NULL |
      | w  rence1                     |            1 |                 |            | NULL                                               | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |   NULL |     NULL | 0000-00-00 00:00:00 |                   0 |
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      |                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      |                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      |                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |   2147483647 |               0 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      |                                |            0 |                 |            | ?K                                               | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
      

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              kjoiner Kyle Joiner (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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