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

sql_safe_updates does not work with OR clauses

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

            darkain Vincent Milum Jr created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            elenst Elena Stepanova made changes -
            Fix Version/s 10.3 [ 22126 ]
            Assignee Alice Sherepa [ alice ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.4 [ 22408 ]
            alice Alice Sherepa made changes -
            Assignee Alice Sherepa [ alice ] Sergei Petrunia [ psergey ]
            alice Alice Sherepa made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            serg Sergei Golubchik made changes -
            Status Confirmed [ 10101 ] Open [ 1 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Petrunia [ psergey ] Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ]
            alice Alice Sherepa made changes -
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Priority Minor [ 4 ] Major [ 3 ]
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 91918 ] MariaDB v4 [ 143740 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3.35 [ 27512 ]
            Fix Version/s 10.4.25 [ 27510 ]
            Fix Version/s 10.5.16 [ 27508 ]
            Fix Version/s 10.6.8 [ 27506 ]
            Fix Version/s 10.7.4 [ 27504 ]
            Fix Version/s 10.8.3 [ 27502 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Assignee Sergei Petrunia [ psergey ] Sergei Golubchik [ serg ]
            Resolution Fixed [ 1 ]
            Status In Review [ 10002 ] Closed [ 6 ]

            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.