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

Multi-UPDATE does not report truncated fields correctly

        Details

        • Type: Bug
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.1, 10.0, 5.5
        • Fix Version/s: 10.0
        • Component/s: Data Manipulation - Update
        • Labels:
          None

          Description

          This script:

          DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
UPDATE t1 SET t1.a='';

          correctly reports 1 warning:

          Rows matched: 1  Changed: 1  Warnings: 1

          Looks correct so far.

          Now I change the script slightly, to use a multi-UPDATE statement:

          DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a INT);
CREATE TABLE t2(a INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
UPDATE t1,t2 SET t1.a='';

          it reports no warnings:

          Rows matched: 1  Changed: 1  Warnings: 0

          A related problem:

          DROP FUNCTION IF EXISTS f1;
DELIMITER //
CREATE FUNCTION f1 (a VARCHAR(20)) RETURNS INT
BEGIN
  DECLARE d INT;
  SET d= a;
  SET d= a;
  SET d= a; // Assign 3 times, to have multiple truncations on a bad string argument
  RETURN d;
END;
//
DELIMITER ;
SELECT f1('123x');

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (0);
UPDATE t1 SET a=f1('123x');
UPDATE t1, t1 t2 SET t1.a=f1('123x');

          The above script returns:

          MariaDB [test]> UPDATE t1 SET a=f1('123x');
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> UPDATE t1, t1 t2 SET t1.a=f1('123x');
Query OK, 0 rows affected (0.04 sec)
Rows matched: 1  Changed: 0  Warnings: 3

          The multi-table UPDATE incorrectly says "Warnings: 3" , that is it counted the truncations that happened inside SP. It should only count the number of truncations that happened in fields, which is 0.

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                bar Alexander Barkov added a comment -

                The proposed plan is:

                • Make sure the code does not to increment thd->cuted_fields if thd->count_cuted_fields is false in all cases, for all data types
                • Change this code in mysql_update(): 
                      my_snprintf(buff, sizeof(buff), ER(ER_UPDATE_INFO), (ulong) found,
                (ulong) updated,
                (ulong) thd->get_stmt_da()->current_statement_warn_count());

                  regular UPDATE to use thd->cuted_fields instead of current_statement_warn_count()(
                  to use thd->cuted_fields instead of current_statement_warn_count().

                • Change all Field_xxx::store() to make sure to update thd->cuted_fields properly for all kinds of truncation, e.g. stroring an empty string to a number, storing '1x' to a number, storing 'x' to a number.
                Show
                bar Alexander Barkov added a comment - The proposed plan is: Make sure the code does not to increment thd->cuted_fields if thd->count_cuted_fields is false in all cases, for all data types Change this code in mysql_update(): my_snprintf(buff, sizeof(buff), ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated, (ulong) thd->get_stmt_da()->current_statement_warn_count()); regular UPDATE to use thd->cuted_fields instead of current_statement_warn_count()( to use thd->cuted_fields instead of current_statement_warn_count(). Change all Field_xxx::store() to make sure to update thd->cuted_fields properly for all kinds of truncation, e.g. stroring an empty string to a number, storing '1x' to a number, storing 'x' to a number.

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    bar Alexander Barkov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated: