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

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            kjoiner Kyle Joiner
            Votes:
            1 Vote for this issue
            Watchers:
            11 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: