bug: insert bigint data close to the boundary range, the result set is wrong #1332

davidshiz · 2023-02-27T08:50:14Z

Have you read the Contributing Guidelines on issues?

I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

I confirm there is no existing issue for this

Describe the problem

mysql> CREATE TABLE int8_tbl(q1 int8, q2 int8);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO int8_tbl VALUES('4567890123456789','123');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO int8_tbl VALUES ('9223372036854775807','9223372036854775806');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO int8_tbl VALUES ('-9223372036854775805','-9223372036854775806');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM int8_tbl;
+----------------------+----------------------+
| q1                   | q2                   |
+----------------------+----------------------+
|     4567890123456789 |                  123 |
|  9223372036854775807 |  9223372036854775806 |
| -9223372036854775805 | -9223372036854775806 |
+----------------------+----------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM int8_tbl WHERE q2 < 4567890123456789;
Empty set (0.00 sec)

Expected behavior

mysql> SELECT * FROM int8_tbl WHERE q2 < 4567890123456789;
+----------------------+----------------------+
| q1                   | q2                   |
+----------------------+----------------------+
|     4567890123456789 |                  123 |
| -9223372036854775805 | -9223372036854775806 |
+----------------------+----------------------+
2 rows in set (0.00 sec)

How To Reproduce

CREATE TABLE int8_tbl(q1 int8, q2 int8);
INSERT INTO int8_tbl VALUES('4567890123456789','123');
INSERT INTO int8_tbl VALUES ('9223372036854775807','9223372036854775806');
INSERT INTO int8_tbl VALUES ('-9223372036854775805','-9223372036854775806');
SELECT * FROM int8_tbl WHERE q2 < 4567890123456789;

Environment

root@ub01:~# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB-v1.0.2 for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 0f4250589
        Last commit time: Date:   Tue Feb 21 06:09:37 2023 +0000
        Build time: Date: Wed Feb 22 17:50:51 CST 2023

Are you interested in submitting a PR to solve the problem?

Yes, I will!

RingsC · 2023-02-27T12:49:37Z

@davidshiz, pls, give out the resul of select * from int8_tbl . Thanks.

davidshiz · 2023-02-27T12:59:13Z

@davidshiz, pls, give out the resul of select * from int8_tbl . Thanks.

ok, It has been updated, view the above content

hustjieke · 2023-03-02T02:05:12Z

It seems like the similar problem with #1344

hustjieke · 2023-03-08T06:14:10Z

Simplify the sql:

mysql> create table t(a int8);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t where a < 3;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> insert into t values(-9223372036854775806);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t where a < 3;
+----------------------+
| a                    |
+----------------------+
|                    1 |
| -9223372036854775806 |
+----------------------+
2 rows in set (0.00 sec)

mysql> insert into t values(9223372036854775806);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t where a < 3;
Empty set (0.00 sec)

hustjieke · 2023-03-08T06:32:03Z

error msg should be [-9223372036854775806, 9223372036854775807]:

davidshiz · 2023-03-08T06:36:33Z

same question

mysql> select * from int8_tbl;
+---------------------+---------------------+
| q1                  | q2                  |
+---------------------+---------------------+
| 9223372036854775807 | 9223372036854775806 |
|                NULL |                NULL |
|                 123 |                 456 |
|                 123 |    4567890123456789 |
|    4567890123456789 |                 123 |
|    4567890123456789 |    4567890123456789 |
|    4567890123456789 |   -4567890123456789 |
+---------------------+---------------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM int8_tbl WHERE q2 >= 4567890123456789;
Empty set (0.00 sec)

El-even-11 · 2023-03-09T08:30:05Z

another similar problem

mysql> select * from int8_tbl;
+----------------------+----------------------+
| q1                   | q2                   |
+----------------------+----------------------+
| -9223372036854775805 | -9223372036854775806 |
+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> select * from int8_tbl where q2 = -9223372036854775807;
+----------------------+----------------------+
| q1                   | q2                   |
+----------------------+----------------------+
| -9223372036854775805 | -9223372036854775806 |
+----------------------+----------------------+
1 row in set (0.00 sec)

hustjieke · 2023-03-09T08:34:32Z

another similar question

mysql> select * from int8_tbl;
+----------------------+----------------------+
| q1                   | q2                   |
+----------------------+----------------------+
| -9223372036854775805 | -9223372036854775806 |
+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> select * from int8_tbl where q2 = -9223372036854775807;
+----------------------+----------------------+
| q1                   | q2                   |
+----------------------+----------------------+
| -9223372036854775805 | -9223372036854775806 |
+----------------------+----------------------+
1 row in set (0.00 sec)

Yes, I'm working on this bug now. It seems like sth. wrong happened when do optimizer.

hustjieke · 2023-03-14T01:59:55Z

root cause:
In function TianmuAttr::EvaluatePack_BetweenInt():

  int64_t pv1 = d.val1.vc->GetValueInt64(mit); // left value
  int64_t pv2 = d.val2.vc->GetValueInt64(mit); // right value
  int64_t local_min = dpn.min_i;
  int64_t local_max = dpn.max_i;
  if (pv1 != common::MINUS_INF_64) 
    pv1 = pv1 - local_min;-----> error happened here, if local_min = -9223372036854775806, the result of  pv1 may be out of boundary and get wrong value, we should use uint64 to store pv1
  else  local_min-local_min = 0
    pv1 = 0;

  if (pv2 != common::PLUS_INF_64)  // encode from 0-level to 2-level
    pv2 = pv2 - local_min; -----> The same reason as pv1
  else
    pv2 = local_max - local_min;

and then when do row filter, int64_t v = p->GetValInt(mit.GetCurInpack(dim)); should be changed to uint64_t, as we use format uint64_t to store data into pack:

      if (d.op == common::Operator::O_BETWEEN && !mit.NullsPossibleInPack(dim) && dpn.numOfNulls == 0) {
        // easy and fast case - no "if"s
        do {
          int64_t v = p->GetValInt(mit.GetCurInpack(dim));
          if (pv1 > v || v > pv2) 
            mit.ResetCurrent(); 
          ++mit; 
        } while (mit.IsValid() && !mit.PackrowStarted());

…in or not in stoneatom#1332 [summary] In tianmu, pack_int uses uint64_t to store level 2 encode, ranges from [0, uint64_max], so when filter data, the conditions(in code named "pv1, pv2") should be also changed to level 2 encode. e.g.: stored value = actually value - min uint64 upv1 = pv1 - local_min; uint64 upv2 = local_max - local_min; the fixes mainly in funciont "EvaluatePack_BetweenInt". For not int (value): when value < local_min, it should be a special case. Only used when rough check = RS_ALL(no numeric data matchs), but table has null, rough check will be reset RS_SOME.

…ag --> (pv2 < local_min) stoneatom#1332

…in or not in #1332 [summary] In tianmu, pack_int uses uint64_t to store level 2 encode, ranges from [0, uint64_max], so when filter data, the conditions(in code named "pv1, pv2") should be also changed to level 2 encode. e.g.: stored value = actually value - min uint64 upv1 = pv1 - local_min; uint64 upv2 = local_max - local_min; the fixes mainly in funciont "EvaluatePack_BetweenInt". For not int (value): when value < local_min, it should be a special case. Only used when rough check = RS_ALL(no numeric data matchs), but table has null, rough check will be reset RS_SOME.

…ag --> (pv2 < local_min) #1332

davidshiz added the A-bug Something isn't working label Feb 27, 2023

davidshiz added this to StoneDB for MySQL 5.7 Feb 27, 2023

davidshiz moved this to Todo in StoneDB for MySQL 5.7 Feb 27, 2023

wisehead assigned hustjieke Mar 1, 2023

wisehead added this to the stonedb_5.7_v1.0.3 milestone Mar 1, 2023

hustjieke removed the status in StoneDB for MySQL 5.7 Mar 2, 2023

hustjieke moved this to In Progress in StoneDB for MySQL 5.7 Mar 2, 2023

hustjieke added the prio: high High priority label Mar 8, 2023

hustjieke mentioned this issue Mar 15, 2023

bug: create database name with backtick, unable to write data normally #1389

Open

3 tasks

hustjieke mentioned this issue Mar 15, 2023

fix(core): incorrect result when execute sql with between | > or < in or not in #1332 #1393

Merged

14 tasks

hustjieke added a commit to hustjieke/stonedb-8.0.30-upgrade that referenced this issue Mar 17, 2023

fix(core): mv test from select_from to issue1332 and replace empty_fl…

0c8f3ed

…ag --> (pv2 < local_min) stoneatom#1332

mergify bot closed this as completed in #1393 Mar 17, 2023

mergify bot pushed a commit that referenced this issue Mar 17, 2023

fix(core): mv test from select_from to issue1332 and replace empty_fl…

3ddea31

…ag --> (pv2 < local_min) #1332

github-project-automation bot moved this from In Progress to Done in StoneDB for MySQL 5.7 Mar 17, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

bug: insert bigint data close to the boundary range, the result set is wrong #1332

bug: insert bigint data close to the boundary range, the result set is wrong #1332

davidshiz commented Feb 27, 2023 •

edited

Loading

RingsC commented Feb 27, 2023

davidshiz commented Feb 27, 2023

hustjieke commented Mar 2, 2023

hustjieke commented Mar 8, 2023

hustjieke commented Mar 8, 2023

davidshiz commented Mar 8, 2023

El-even-11 commented Mar 9, 2023 •

edited

Loading

hustjieke commented Mar 9, 2023

hustjieke commented Mar 14, 2023

bug: insert bigint data close to the boundary range, the result set is wrong #1332

bug: insert bigint data close to the boundary range, the result set is wrong #1332

Comments

davidshiz commented Feb 27, 2023 • edited Loading

Have you read the Contributing Guidelines on issues?

Please confirm if bug report does NOT exists already ?

Describe the problem

Expected behavior

How To Reproduce

Environment

Are you interested in submitting a PR to solve the problem?

RingsC commented Feb 27, 2023

davidshiz commented Feb 27, 2023

hustjieke commented Mar 2, 2023

hustjieke commented Mar 8, 2023

hustjieke commented Mar 8, 2023

davidshiz commented Mar 8, 2023

El-even-11 commented Mar 9, 2023 • edited Loading

hustjieke commented Mar 9, 2023

hustjieke commented Mar 14, 2023

davidshiz commented Feb 27, 2023 •

edited

Loading

El-even-11 commented Mar 9, 2023 •

edited

Loading