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

BETWEEN clause returns incorrect results on quoted 64-bit ints

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.5.9, 10.1, 10.0, 5.5
    • Fix Version/s: 10.2, 10.3, 10.4, 10.5
    • Component/s: Data types
    • Labels:
    • Environment:
      FreeBSD/amd64

      Description

      A "between" clause returns wrong results, compared to the equivalent "<=" and "=>" clause, on bigint fields if the integer is single-quoted. There may be other cases but this is the easiest-to-reproduce variant.

      Here is a minimalist test case:

      CREATE TABLE betweentest (
        id int(11) unsigned NOT NULL AUTO_INCREMENT,
        range1 bigint(20) DEFAULT NULL,
        range2 bigint(20) DEFAULT NULL,
        PRIMARY KEY (id)) ENGINE=InnoDB;
      INSERT INTO betweentest VALUES
        (1,2739499741191602369,2739499741191602369),
        (2,2739499741191602192,2739499741191602192),
        (3,2739138623713574912,2739138623730352127);
      

      And some sample queries. Query #4 is the failure case being reported here; it returns an extra row that does NOT match the query. The extra row does match the first 56 bits of the bigint value, but not the full 64 bits. The other 5 queries are correct.

      [localhost:test] mysql> select * from betweentest where 2739138623713706238 between range1 and range2 \G
          id: 3
      range1: 2739138623713574912
      range2: 2739138623730352127
      1 row in set (0.001 sec)
       
      [localhost:test] mysql> select * from betweentest where '2739138623713706238' between range1 and range2 \G
          id: 3
      range1: 2739138623713574912
      range2: 2739138623730352127
      1 row in set (0.001 sec)
       
      [localhost:test] mysql> select * from betweentest where 2739499741191602192 between range1 and range2 \G
          id: 2
      range1: 2739499741191602192
      range2: 2739499741191602192
      1 row in set (0.001 sec)
       
      [localhost:test] mysql> select * from betweentest where '2739499741191602192' between range1 and range2 \G
          id: 1
      range1: 2739499741191602369
      range2: 2739499741191602369
          id: 2
      range1: 2739499741191602192
      range2: 2739499741191602192
      2 rows in set (0.001 sec)
       
      [localhost:test] mysql> select * from betweentest where 2739499741191602192>=range1 and 2739499741191602192<=range2 \G
          id: 2
      range1: 2739499741191602192
      range2: 2739499741191602192
      1 row in set (0.001 sec)
       
      [localhost:test] mysql> select * from betweentest where '2739499741191602192'>=range1 and '2739499741191602192'<=range2 \G
          id: 2
      range1: 2739499741191602192
      range2: 2739499741191602192
      

      The reason we were using single-quotes is because of Perl's DBD::mysql's handling of bind/placeholder variables, as is common practice for avoiding SQL injection vulnerabilities; it seems to be adding them. The examples above are just from the CLI and don't involve Perl at all, though.

      We're working around the issue in our application by switching to the syntax from examples 5 and 6.

      Maybe there's a type conversion issue affecting bigints somewhere? I'm not sure why it would affect only "between" though.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              bit0mike Mike Andrews
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: