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

NO_UNSIGNED_SUBTRACTION does not work for multiple unsigned integers

Details

    Description

      sql_mode='NO_UNSIGNED_SUBTRACTION' is supposed to allow the subtraction of two unsigned integers. However, in MariaDB it seems to allow only subtractions involving only ONE unsigned integer (if the result is negative).

      SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
       
      SELECT CAST(0 AS UNSIGNED) - 1;        
      +-------------------------+
      | CAST(0 AS UNSIGNED) - 1 |
      +-------------------------+
      |                      -1 |
      +-------------------------+
       
      SELECT 2-CAST(3 AS UNSIGNED);    
      +-----------------------+
      | 2-CAST(3 AS UNSIGNED) |
      +-----------------------+
      |                    -1 |
      +-----------------------+
       
      SELECT CAST(1 AS UNSIGNED) - CAST(2 AS UNSIGNED);
      ERROR 1690 (22003): BIGINT value is out of range in 'cast(1 as unsigned) - cast(2 as unsigned)'
      

      This works in MySQL (tested version 5.6.40 and 8.0.39)

      Error originally reported on https://mariadb.com/kb/en/scope-of-no_unsigned_subtraction/

      Attachments

        Issue Links

          Activity

            greenman Ian Gilfillan added a comment -

            This worked in MariaDB 10.3, stopped working in 10.4.

            greenman Ian Gilfillan added a comment - This worked in MariaDB 10.3, stopped working in 10.4.

            caused by

            commit 031f11717d9
            Author: Michael Widenius <monty@mariadb.org>
            Date:   Sun Apr 18 15:29:13 2021 +0300
             
                Fix all warnings given by UBSAN
                
                The easiest way to compile and test the server with UBSAN is to run:
                ./BUILD/compile-pentium64-ubsan
                and then run mysql-test-run.
                After this commit, one should be able to run this without any UBSAN
                warnings. There is still a few compiler warnings that should be fixed
                at some point, but these do not expose any real bugs.
                
                The 'special' cases where we disable, suppress or circumvent UBSAN are:
                - ref10 source (as here we intentionally do some shifts that UBSAN
                  complains about.
                - x86 version of optimized int#korr() methods. UBSAN do not like unaligned
                  memory access of integers.  Fixed by using byte_order_generic.h when
                  compiling with UBSAN
                - We use smaller thread stack with ASAN and UBSAN, which forced me to
                  disable a few tests that prints the thread stack size.
                - Verifying class types does not work for shared libraries. I added
                  suppression in mysql-test-run.pl for this case.
                - Added '#ifdef WITH_UBSAN' when using integer arithmetic where it is
                  safe to have overflows (two cases, in item_func.cc).
                
                Things fixed:
                - Don't left shift signed values
                  (byte_order_generic.h, mysqltest.c, item_sum.cc and many more)
                - Don't assign not non existing values to enum variables.
                - Ensure that bool and enum values are properly initialized in
                  constructors.  This was needed as UBSAN checks that these types has
                  correct values when one copies an object.
                  (gcalc_tools.h, ha_partition.cc, item_sum.cc, partition_element.h ...)
                - Ensure we do not called handler functions on unallocated objects or
                  deleted objects.
                  (events.cc, sql_acl.cc).
                - Fixed bugs in Item_sp::Item_sp() where we did not call constructor
                  on Query_arena object.
                - Fixed several cast of objects to an incompatible class!
                  (Item.cc, Item_buff.cc, item_timefunc.cc, opt_subselect.cc, sql_acl.cc,
                   sql_select.cc ...)
                - Ensure we do not do integer arithmetic that causes over or underflows.
                  This includes also ++ and -- of integers.
                  (Item_func.cc, Item_strfunc.cc, item_timefunc.cc, sql_base.cc ...)
                - Added JSON_VALUE_UNITIALIZED to json_value_types and ensure that
                  value_type is initialized to this instead of to -1, which is not a valid
                  enum value for json_value_types.
                - Ensure we do not call memcpy() when second argument could be null.
                - Fixed that Item_func_str::make_empty_result() creates an empty string
                  instead of a null string (safer as it ensures we do not do arithmetic
                  on null strings).
                
                Other things:
                
                - Changed struct st_position to an OBJECT and added an initialization
                  function to it to ensure that we do not copy or use uninitialized
                  members. The change to a class was also motived that we used "struct
                  st_position" and POSITION randomly trough the code which was
                  confusing.
                - Notably big rewrite in sql_acl.cc to avoid using deleted objects.
                - Changed in sql_partition to use '^' instead of '-'. This is safe as
                  the operator is either 0 or 0x8000000000000000ULL.
                - Added check for select_nr < INT_MAX in JOIN::build_explain() to
                  avoid bug when get_select() could return NULL.
                - Reordered elements in POSITION for better alignment.
                - Changed sql_test.cc::print_plan() to use pointers instead of objects.
                - Fixed bug in find_set() where could could execute '1 << -1'.
                - Added variable have_sanitizer, used by mtr.  (This variable was before
                  only in 10.5 and up).  It can now have one of two values:
                  ASAN or UBSAN.
                - Moved ~Archive_share() from ha_archive.cc to ha_archive.h and marked
                  it virtual. This was an effort to get UBSAN to work with loaded storage
                  engines. I kept the change as the new place is better.
                - Added in CONNECT engine COLBLK::SetName(), to get around a wrong cast
                  in tabutil.cpp.
                - Added HAVE_REPLICATION around usage of rgi_slave, to get embedded
                  server to compile with UBSAN. (Patch from Marko).
                - Added #ifdef for powerpc64 to avoid a bug in old gcc versions related
                  to integer arithmetic.
                
                Changes that should not be needed but had to be done to suppress warnings
                from UBSAN:
                
                - Added static_cast<<uint16_t>> around shift to get rid of a LOT of
                  compiler warnings when using UBSAN.
                - Had to change some '/' of 2 base integers to shift to get rid of
                  some compile time warnings.
                
                Reviewed by:
                - Json changes: Alexey Botchkov
                - Charset changes in ctype-uca.c: Alexander Barkov
                - InnoDB changes & Embedded server: Marko Mäkelä
                - sql_acl.cc changes: Vicențiu Ciorbaru
                - build_explain() changes: Sergey Petrunia
            

            serg Sergei Golubchik added a comment - caused by commit 031f11717d9 Author: Michael Widenius <monty@mariadb.org> Date: Sun Apr 18 15:29:13 2021 +0300   Fix all warnings given by UBSAN The easiest way to compile and test the server with UBSAN is to run: ./BUILD/compile-pentium64-ubsan and then run mysql-test-run. After this commit, one should be able to run this without any UBSAN warnings. There is still a few compiler warnings that should be fixed at some point, but these do not expose any real bugs. The 'special' cases where we disable, suppress or circumvent UBSAN are: - ref10 source (as here we intentionally do some shifts that UBSAN complains about. - x86 version of optimized int#korr() methods. UBSAN do not like unaligned memory access of integers. Fixed by using byte_order_generic.h when compiling with UBSAN - We use smaller thread stack with ASAN and UBSAN, which forced me to disable a few tests that prints the thread stack size. - Verifying class types does not work for shared libraries. I added suppression in mysql-test-run.pl for this case. - Added '#ifdef WITH_UBSAN' when using integer arithmetic where it is safe to have overflows (two cases, in item_func.cc). Things fixed: - Don't left shift signed values (byte_order_generic.h, mysqltest.c, item_sum.cc and many more) - Don't assign not non existing values to enum variables. - Ensure that bool and enum values are properly initialized in constructors. This was needed as UBSAN checks that these types has correct values when one copies an object. (gcalc_tools.h, ha_partition.cc, item_sum.cc, partition_element.h ...) - Ensure we do not called handler functions on unallocated objects or deleted objects. (events.cc, sql_acl.cc). - Fixed bugs in Item_sp::Item_sp() where we did not call constructor on Query_arena object. - Fixed several cast of objects to an incompatible class! (Item.cc, Item_buff.cc, item_timefunc.cc, opt_subselect.cc, sql_acl.cc, sql_select.cc ...) - Ensure we do not do integer arithmetic that causes over or underflows. This includes also ++ and -- of integers. (Item_func.cc, Item_strfunc.cc, item_timefunc.cc, sql_base.cc ...) - Added JSON_VALUE_UNITIALIZED to json_value_types and ensure that value_type is initialized to this instead of to -1, which is not a valid enum value for json_value_types. - Ensure we do not call memcpy() when second argument could be null. - Fixed that Item_func_str::make_empty_result() creates an empty string instead of a null string (safer as it ensures we do not do arithmetic on null strings). Other things: - Changed struct st_position to an OBJECT and added an initialization function to it to ensure that we do not copy or use uninitialized members. The change to a class was also motived that we used "struct st_position" and POSITION randomly trough the code which was confusing. - Notably big rewrite in sql_acl.cc to avoid using deleted objects. - Changed in sql_partition to use '^' instead of '-'. This is safe as the operator is either 0 or 0x8000000000000000ULL. - Added check for select_nr < INT_MAX in JOIN::build_explain() to avoid bug when get_select() could return NULL. - Reordered elements in POSITION for better alignment. - Changed sql_test.cc::print_plan() to use pointers instead of objects. - Fixed bug in find_set() where could could execute '1 << -1'. - Added variable have_sanitizer, used by mtr. (This variable was before only in 10.5 and up). It can now have one of two values: ASAN or UBSAN. - Moved ~Archive_share() from ha_archive.cc to ha_archive.h and marked it virtual. This was an effort to get UBSAN to work with loaded storage engines. I kept the change as the new place is better. - Added in CONNECT engine COLBLK::SetName(), to get around a wrong cast in tabutil.cpp. - Added HAVE_REPLICATION around usage of rgi_slave, to get embedded server to compile with UBSAN. (Patch from Marko). - Added #ifdef for powerpc64 to avoid a bug in old gcc versions related to integer arithmetic. Changes that should not be needed but had to be done to suppress warnings from UBSAN: - Added static_cast<<uint16_t>> around shift to get rid of a LOT of compiler warnings when using UBSAN. - Had to change some '/' of 2 base integers to shift to get rid of some compile time warnings. Reviewed by: - Json changes: Alexey Botchkov - Charset changes in ctype-uca.c: Alexander Barkov - InnoDB changes & Embedded server: Marko Mäkelä - sql_acl.cc changes: VicenÈ›iu Ciorbaru - build_explain() changes: Sergey Petrunia

            People

              serg Sergei Golubchik
              greenman Ian Gilfillan
              Votes:
              1 Vote for this issue
              Watchers:
              3 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.