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

sql_safe_updates does not work with OR clauses

    XMLWordPrintable

Details

    Description

      MariaDB [test]> CREATE TABLE `bork` (`a` INT, `b` INT, PRIMARY KEY (`a`), KEY (`b`));
      Query OK, 0 rows affected (0.112 sec)
       
      MariaDB [test]> DELETE FROM bork WHERE `a`=1 or `b`=2;
      ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
      

      The error message says that a KEY was not used in the delete statement. Both sides of the OR are keys, however the OR apparently is preventing the optimizer from realizing this.

      These types of statements are comment when creating a one-to-many or many-to-many linking tables, and attempting to either UPDATE or DELETE these links.

      Current work-around is to use an arbitrarily large LIMIT on the query, as this bypasses the WHERE safety check.

      It also appears that the optimizer assumes that a LIMIT of 0xFFFFFFFFFFFFFFFF or (2^64)-1 is the same as having no limit at all.

      -- 0xFFFFFFFFFFFFFFFE : (2 ^ 64) - 2
      MariaDB [test]> DELETE FROM bork WHERE `a`=1 or `b`=2 LIMIT 18446744073709551614;
      Query OK, 0 rows affected (0.000 sec)
       
      -- 0xFFFFFFFFFFFFFFFF : (2 ^ 64) - 1
      MariaDB [test]> DELETE FROM bork WHERE `a`=1 or `b`=2 LIMIT 18446744073709551615;
      ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
       
      -- 0x10000000000000000 : (2 ^ 64)
      MariaDB [test]> DELETE FROM bork WHERE `a`=1 or `b`=2 LIMIT 18446744073709551616;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '18446744073709551616' at line 1
      

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              darkain Vincent Milum Jr
              Votes:
              0 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.