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

Provide index of error causing error in array INSERT

Details

    Description

      When executing a multi-row INSERT using array notation, i.e.:

      INSERT INTO sometable VALUES(1,'a'),(2,'b'),(3,'c);

      If there is an error in this statement for example a PRIMARY KEY error, then there is no way of knowing which element in the array caused the error. This really limits the usefulness of array INSERTs which is an issue as this is a really good way to increase INSERT performance.

      The suggestion is to add the ability to find the index of the value with an issue by adding a new function to the API. This also need to be made available through Stored Procedures so maybe it should be complemented with a SQL-function. In addition, one such function will also be necessary, for the same reason, for prepared statements.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26606 ROW_NUMBER property value isn't passed from inside a stored procedure

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26610 ERROR_INDEX isn't seen in the error message

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26611 ERROR_INDEX isn't intuitively clear

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26635 ROW_NUMBER is not 0 for errors not caused because of rows

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26654 ROW_NUMBER is wrong upon INSERT into Federated table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26681 ROW_NUMBER is not available within compound statement blocks

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26684 Unexpected ROW_NUMBER in a condition raised by a diagnostics statement

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26693 ROW_NUMBER is wrong upon INSERT or UPDATE on Spider table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26695 Number of an invalid row is not calculated for table value constructor

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26698 Incorrect row number upon INSERT .. SELECT from the same table: rows are counted twice

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26699 Row counter does not work consistently for Columnstore tables

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26702 Unexpected ROW_NUMBER upon SELECT from JSON_TABLE with erroneous JSON doc

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26830 Wrong ROW_NUMBER in diagnostics upon INSERT IGNORE with CHECK violation

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26832 ROW_NUMBER in SIGNAL/RESIGNAL causes a syntax error

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26836 ROW_NUMBER differs from the number in the error message upon ER_WARN_DATA_OUT_OF_RANGE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26841 ROW_NUMBER is not set and differs from the message upon ER_WRONG_VALUE_COUNT_ON_ROW for the 1st row

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26842 ROW_NUMBER is not set and differs from the message upon WARN_DATA_TRUNCATED produced by inplace ALTER

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26843 Inconsistent behavior of ROW_NUMBER upon resignalling from function

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • In Review

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26844 DELETE returns ROW_NUMBER=1 for every row upon ER_TRUNCATED_WRONG_VALUE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26845 For unsupported statements ROW_NUMBER shows different values depending on previous events

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26848 ROW_NUMBER is wrong and differs from error message upon ER_CUT_VALUE_GROUP_CONCAT

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26878 Query failing with syntax error sets ROW_NUMBER to non-zero

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26909 ROW_NUMBER and DELETE/UPDATE with ORDER BY

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            View 5 older comments
            elenst Elena Stepanova added a comment -

            A note unrelated to the previous one
            The initially suggested ROW_NUMBER was intuitively understandable, but ERROR_INDEX in the final implementation is not. It is not necessarily an error, and it is certainly not its index. 

            MariaDB [test]> insert ignore into t1 (pk) values (1),(1);
            		 
            Query OK, 1 row affected, 1 warning (0.017 sec)
            		 
            Records: 2  Duplicates: 1  Warnings: 1
            		 
             
            		 
            MariaDB [test]> show warnings;
            		 
            +---------+------+---------------------------------------+
            		 
            | Level   | Code | Message                               |
            		 
            +---------+------+---------------------------------------+
            		 
            | Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
            		 
            +---------+------+---------------------------------------+
            		 
            1 row in set (0.000 sec)
            		 
             
            		 
            MariaDB [test]>  get diagnostics condition 1 @num = ERROR_INDEX, @msg = MESSAGE_TEXT; select @num, @msg;
            		 
            Query OK, 0 rows affected (0.000 sec)
            		 
             
            		 
            +------+---------------------------------------+
            		 
            | @num | @msg                                  |
            		 
            +------+---------------------------------------+
            		 
            |    2 | Duplicate entry '1' for key 'PRIMARY' |
            		 
            +------+---------------------------------------+
            		 
            1 row in set (0.000 sec)

            If something is an "index" here, it's the condition number (1), but the failing row number is 2. And it's a warning.

            elenst Elena Stepanova added a comment - A note unrelated to the previous one The initially suggested ROW_NUMBER was intuitively understandable, but ERROR_INDEX in the final implementation is not. It is not necessarily an error, and it is certainly not its index. MariaDB [test]> insert ignore into t1 (pk) values (1),(1); Query OK, 1 row affected, 1 warning (0.017 sec) Records: 2 Duplicates: 1 Warnings: 1   MariaDB [test]> show warnings; + ---------+------+---------------------------------------+ | Level | Code | Message | + ---------+------+---------------------------------------+ | Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' | + ---------+------+---------------------------------------+ 1 row in set (0.000 sec)   MariaDB [test]> get diagnostics condition 1 @num = ERROR_INDEX, @msg = MESSAGE_TEXT; select @num, @msg; Query OK, 0 rows affected (0.000 sec)   + ------+---------------------------------------+ | @num | @msg | + ------+---------------------------------------+ | 2 | Duplicate entry '1' for key 'PRIMARY' | + ------+---------------------------------------+ 1 row in set (0.000 sec) If something is an "index" here, it's the condition number (1), but the failing row number is 2. And it's a warning.
            rucha174 Rucha Deodhar added a comment -

            Hi elenst , Error messages are not very specific for every statement. Example we can have Data truncated for col 'a' at row i for ALTER too and not just INSERT. Plus not all error messages reflect which row gave error or warning. ERROR_INDEX works only for INSERT and also makes it possible to know which row has problem for all INSERT-related errors/warnings.

            ROW_NUMBER looked a little non-specific to me, so I named it ERROR_INDEX from the task title. I agree it isn't really an "index", but it indicates the i-th row, so it made sense to start from 1 instead of 0. (But I can change it if it is not too late, the closer it is to its actual function the better). It seemed better to also have warning along with errors if there are problematic rows especially in case of INSERT...IGNORE where errors are ignored but user might still want to know the row that gave warning.

            rucha174 Rucha Deodhar added a comment - Hi elenst , Error messages are not very specific for every statement. Example we can have Data truncated for col 'a' at row i for ALTER too and not just INSERT. Plus not all error messages reflect which row gave error or warning. ERROR_INDEX works only for INSERT and also makes it possible to know which row has problem for all INSERT-related errors/warnings. ROW_NUMBER looked a little non-specific to me, so I named it ERROR_INDEX from the task title. I agree it isn't really an "index", but it indicates the i-th row, so it made sense to start from 1 instead of 0. (But I can change it if it is not too late, the closer it is to its actual function the better). It seemed better to also have warning along with errors if there are problematic rows especially in case of INSERT...IGNORE where errors are ignored but user might still want to know the row that gave warning.
            serg Sergei Golubchik added a comment -

            This issue was closed too early, before all comments were addressed. So they were moved to separate, linked, issues.

            serg Sergei Golubchik added a comment - This issue was closed too early, before all comments were addressed. So they were moved to separate, linked, issues.
            serg Sergei Golubchik added a comment -

            let's keep it open until bb-10.7-row_number is pushed

            serg Sergei Golubchik added a comment - let's keep it open until bb-10.7-row_number is pushed
            elenst Elena Stepanova added a comment -

            In my opinion the functionality as of bb-10.7-row_number d555ae3 can be merged into 10.7 main branch and released with 10.7.1.

            It is not perfect due to inherited legacy issues, but

            • the mechanism for extracting the row number has been introduced and it is consistent with already existing functionality;
            • for common and presumably most important use cases it returns reasonable values;
            • there are no known major discrepancies between the new value and the existing warning/error messages reporting a row number (remaining open issues of this kind are corner cases).

            There will be a fair amount of wrong or questionable results for SQL statements which weren't the target of this task. Some are already reported as bugs, and there are more to come. In many cases it is not even obvious what the result should be. I expect that depending on the feedback we will be able to determine whether the functionality is employed by a sufficient part of users, and if it is, in future releases it will be adjusted to a more predictable behavior with a wider range of statements.

            elenst Elena Stepanova added a comment - In my opinion the functionality as of bb-10.7-row_number d555ae3 can be merged into 10.7 main branch and released with 10.7.1. It is not perfect due to inherited legacy issues, but the mechanism for extracting the row number has been introduced and it is consistent with already existing functionality; for common and presumably most important use cases it returns reasonable values; there are no known major discrepancies between the new value and the existing warning/error messages reporting a row number (remaining open issues of this kind are corner cases). There will be a fair amount of wrong or questionable results for SQL statements which weren't the target of this task. Some are already reported as bugs, and there are more to come. In many cases it is not even obvious what the result should be. I expect that depending on the feedback we will be able to determine whether the functionality is employed by a sufficient part of users, and if it is, in future releases it will be adjusted to a more predictable behavior with a wider range of statements.

            People

              rucha174 Rucha Deodhar
              karlsson Anders Karlsson
              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.