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

Limit/Offset query, returns two different results consistently if limit is greater than 1000

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Incomplete
    • Affects Version/s: None
    • Fix Version/s: N/A
    • Component/s: OTHER
    • Labels:
    • Environment:
      Linux

      Description

      Hello,

      Error can be seen in the image below. Effectively running the exact same sql query, will return two different responses here.

      mysql> select * from BTC_USDT_binance where id >= 56057162 and id < 56057791 and bidAsk = 1 order by id asc limit 2000;
      Empty set (0.00 sec)
      

      mysql> select * from BTC_USDT_binance where id >= 56057162 and id <     56057791 and bidAsk = 1 order by id asc limit 2000;
      +----------+------------------------+---------+----------+--------+-------+
      | id       | date                   | price   | volume   | bidAsk | reset |
      +----------+------------------------+---------+----------+--------+-------+
      | 56057163 | 2018-11-27 00:49:46.63 | 3848.26 | 0.201554 |       |           |
      | 56057164 | 2018-11-27 00:49:46.63 | 3835.31 |        0 |       |       |
      | 56057168 | 2018-11-27 00:49:47.64 | 3848.27 |      2.1 |       |       |
      | 56057175 | 2018-11-27 00:49:48.64 |  3848.3 | 0.086733 |       |       |
      | 56057176 | 2018-11-27 00:49:48.64 | 3848.27 |        0 |       |       |
      | 56057177 | 2018-11-27 00:49:48.64 | 3848.31 | 0.085672 |       |       |
      

      Second Image to showcase it:

      mysql> select COUNT(*) from BTC_USDT_binance where id >= '56057162' and id < '56057791' and bidAsk = 0;
      +----------+
      | COUNT(*) |
      +----------+
      |      316 |
      +----------+
      1 row in set (0.00 sec)
       
      mysql> select COUNT(*) from BTC_USDT_binance where id >= '56057162' and id < '56057791' and bidAsk = 0;
      +----------+
      | COUNT(*) |
      +----------+
      |        1 |
      +----------+
      1 row in set (0.00 sec)
       
      mysql> select COUNT(*) from BTC_USDT_binance where id >= '56057162' and id < '56057791' and bidAsk = 0;
      +----------+
      | COUNT(*) |
      +----------+
      |      316 |
      +----------+
      1 row in set (0.00 sec)
       
      mysql> select COUNT(*) from BTC_USDT_binance where id >= '56057162' and id < '56057791' and bidAsk = 0;
      +----------+
      | COUNT(*) |
      +----------+
      |        1 |
      +----------+
      1 row in set (0.01 sec)
       
      mysql> select COUNT(*) from BTC_USDT_binance where id >= '56057162' and id < '56057791' and bidAsk = 0;
      +----------+
      | COUNT(*) |
      +----------+
      |      316 |
      +----------+
      1 row in set (0.00 sec)
      

      Notes/Observations on this:
      I have turned off other connections, am not storing to the table at all. It happens consistently every other - meaning that if I run it once, it will return one result (typically a large query/result of a couple thousand responses), and if I query it again, it will return the other (a single record).

          1. Note this is not related to space constraints on the primary key or anything related to this. The database schema is as follows here.

      mysql> describe BTC_USDT_binance;
      +--------+---------------------+------+-----+---------+----------------+
      | Field  | Type                | Null | Key | Default | Extra          |
      +--------+---------------------+------+-----+---------+----------------+
      | id     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
      | date   | timestamp(2)        | YES  |     | NULL    |                |
      | price  | float               | YES  |     | NULL    |                |
      | volume | float               | YES  |     | NULL    |                |
      | bidAsk | bit(1)              | YES  |     | NULL    |                |
      | reset  | bit(1)              | YES  |     | NULL    |                |
      +--------+---------------------+------+-----+---------+----------------+
      

          1. Hacks Around

      If you limit the number of records drawn (i.e. only pull 100 records at once), the problem goes away. This happens until around 1,000 records. This can be easily seen/reproduced by running (varying the value of N):

      select * from BTC_USDT_binance where id >= 56057162 and id < 56057791 and bidAsk = 1 order by id asc limit N;
      

      If you instead only use a single-sided greater than, and use a limit, the problem goes away as well. This is seen in:

      select * from BTC_USDT_binance where id >= 56057162 and bidAsk = 1 order by id asc limit 500;
      

      Other Details and Notes

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            mauro Mauro Delazeri
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration