Bug #42425 Issues with unsigned bigint range select with Falcon
Submitted: 28 Jan 2009 14:55 Modified: 24 Mar 2009 14:38
Reporter: Nidhi Shrotriya Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: F_ENCODING

[28 Jan 2009 14:55] Nidhi Shrotriya 
Description:
See How to repeat. 

How to repeat:
Repeated with mysql-6.0-falcon-team.

Create table as below with Innodb & Falcon.

CREATE TABLE t2(c1 BIGINT UNSIGNED NOT NULL, c2 BIGINT NULL, c3 TINYINT, c4 SMALLINT , c5 MEDIUMINT, c6 INT, c7 INTEGER, PRIMARY KEY(c1,c6));

Innodb:

SELECT * FROM t2 WHERE c1 > 0 ORDER BY c1,c6;
c1      c2      c3      c4      c5      c6      c7
101     0       37      38      39      40      41
101     NULL    102     103     104     105     106
101     102     103     104     105     106     107
102     109     110     111     112     113     114
103     109     110     111     112     113     114
108     109     110     111     112     101     114
108     109     110     111     112     102     114
108     109     110     111     112     113     114
115     116     117     118     119     120     121
122     123     124     125     126     127     128
255     -2147483648     6       7       8       9       10
255     127     12      13      14      15      16
256     128     27      28      29      30      31
65535   -8388608        11      12      13      14      15
16777215        -32768  16      17      18      19      20
4294967295      -128    21      22      23      24      25
18446744073709551615    9223372036854775807     26      27      28      29      30
18446744073709551615    9223372036854775807     36      37      38      39      40

Falcon:

SELECT * FROM t2 WHERE c1 > 0 ORDER BY c1,c6;
c1      c2      c3      c4      c5      c6      c7
101     0       37      38      39      40      41
101     NULL    102     103     104     105     106
101     102     103     104     105     106     107
102     109     110     111     112     113     114
103     109     110     111     112     113     114
108     109     110     111     112     101     114
108     109     110     111     112     102     114
108     109     110     111     112     113     114
115     116     117     118     119     120     121
122     123     124     125     126     127     128
255     -2147483648     6       7       8       9       10
255     127     12      13      14      15      16
256     128     27      28      29      30      31
65535   -8388608        11      12      13      14      15
16777215        -32768  16      17      18      19      20
4294967295      -128    21      22      23      24      25

"missing rows"
18446744073709551615    9223372036854775807     26      27      28      29      30
18446744073709551615    9223372036854775807     36      37      38      39      40
---------------------------------------------
Innodb:
SELECT * FROM t2 WHERE c1 >= 0 AND c1 < 18446744073709551615 AND c6 = 14 ORDER BY c1,c6;
c1      c2      c3      c4      c5      c6      c7
65535   -8388608        11      12      13      14      15

Falcon:
SELECT * FROM t2 WHERE c1 >= 0 AND c1 < 18446744073709551615 AND c6 = 14 ORDER BY c1,c6;
c1      c2      c3      c4      c5      c6      c7

"no rows found ?"
------------------------------------------------
Innodb:

SELECT * FROM t2 WHERE c1 < 18446744073709551615 ORDER BY c1,c6;
c1      c2      c3      c4      c5      c6      c7
0       NULL    5       6       NULL    0       NULL
0       -9223372036854775808    1       2       3       4       5
0       -128    7       8       9       10      11
0       0       17      18      19      20      21
0       -129    22      23      24      25      26
0       -9223372036854775808    31      32      33      34      35
0       0       32      32      34      35      36
101     0       37      38      39      40      41
101     NULL    102     103     104     105     106
101     102     103     104     105     106     107
102     109     110     111     112     113     114
103     109     110     111     112     113     114
108     109     110     111     112     101     114
108     109     110     111     112     102     114
108     109     110     111     112     113     114
115     116     117     118     119     120     121
122     123     124     125     126     127     128
255     -2147483648     6       7       8       9       10
255     127     12      13      14      15      16
256     128     27      28      29      30      31
65535   -8388608        11      12      13      14      15
16777215        -32768  16      17      18      19      20
4294967295      -128    21      22      23      24      25

Falcon:

SELECT * FROM t2 WHERE c1 < 18446744073709551615 ORDER BY c1,c6;
c1      c2      c3      c4      c5      c6      c7

"no rows found ?"
[30 Jan 2009 11:54] MySQL Verification Team 
Thank you for the bug report. Could you please provide the dump file of the table. Thanks in advance.
[1 Mar 2009 0:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[2 Mar 2009 8:27] Nidhi Shrotriya 
Please find the dump of table and simplest testcase.
mysql> CREATE TABLE bigint_falcon(c1 BIGINT UNSIGNED NOT NULL, c2 BIGINT NULL, c3 TINYINT, c4 SMALLINT , c5 MEDIUMINT, c6 INT, c7 INTEGER, PRIMARY KEY(c1,c6))engine=falcon;
Query OK, 0 rows affected (0.18 sec)
mysql> INSERT INTO bigint_falcon VALUES(0,-9223372036854775808,1,2,3,4,5),(255,-2147483648,6,7,8,9,10),(65535,-8388608,11,12,13,14,15),(16777215,-32768,16,17,18,19,20),(4294967295,-128,21,22,23,24,25),(18446744073709551615,9223372036854775807,26,27,28,29,30);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM bigint_falcon 
    -> ;
+----------------------+----------------------+------+------+------+----+------+
| c1                   | c2                   | c3   | c4   | c5   | c6 | c7   |
+----------------------+----------------------+------+------+------+----+------+
|                    0 | -9223372036854775808 |    1 |    2 |    3 |  4 |    5 | 
|                  255 |          -2147483648 |    6 |    7 |    8 |  9 |   10 | 
|                65535 |             -8388608 |   11 |   12 |   13 | 14 |   15 | 
|             16777215 |               -32768 |   16 |   17 |   18 | 19 |   20 | 
|           4294967295 |                 -128 |   21 |   22 |   23 | 24 |   25 | 
| 18446744073709551615 |  9223372036854775807 |   26 |   27 |   28 | 29 |   30 | 
+----------------------+----------------------+------+------+------+----+------+
6 rows in set (0.01 sec)
mysql> SELECT * FROM bigint_falcon WHERE c1 > 0 ORDER BY c1,c6;
+------------+-------------+------+------+------+----+------+
| c1         | c2          | c3   | c4   | c5   | c6 | c7   |
+------------+-------------+------+------+------+----+------+
|        255 | -2147483648 |    6 |    7 |    8 |  9 |   10 | 
|      65535 |    -8388608 |   11 |   12 |   13 | 14 |   15 | 
|   16777215 |      -32768 |   16 |   17 |   18 | 19 |   20 | 
| 4294967295 |        -128 |   21 |   22 |   23 | 24 |   25 | 
+------------+-------------+------+------+------+----+------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM bigint_falcon WHERE c1 < 18446744073709551615 ORDER BY c1,c6;
Empty set (0.01 sec)
[24 Mar 2009 8:57] Sveta Smirnova 
Thank you for the feedback.

Verified as described. MyISAM returns correct results:

CREATE TABLE bigint_myisam(c1 BIGINT UNSIGNED NOT NULL, c2 BIGINT NULL, c3 TINYINT,
c4 SMALLINT , c5 MEDIUMINT, c6 INT, c7 INTEGER, PRIMARY KEY(c1,c6))engine=myisam;
INSERT INTO bigint_myisam
VALUES(0,-9223372036854775808,1,2,3,4,5),(255,-2147483648,6,7,8,9,10),(65535,-8388608,11,12,13,14,15),(16777215,-32768,16,17,18,19,20),(4294967295,-128,21,22,23,24,25),(18446744073709551615,9223372036854775807,26,27,28,29,30);
SELECT * FROM bigint_myisam;
c1      c2      c3      c4      c5      c6      c7
0       -9223372036854775808    1       2       3       4       5
255     -2147483648     6       7       8       9       10
65535   -8388608        11      12      13      14      15
16777215        -32768  16      17      18      19      20
4294967295      -128    21      22      23      24      25
18446744073709551615    9223372036854775807     26      27      28      29      30
SELECT * FROM bigint_myisam WHERE c1 > 0 ORDER BY c1,c6;
c1      c2      c3      c4      c5      c6      c7
255     -2147483648     6       7       8       9       10
65535   -8388608        11      12      13      14      15
16777215        -32768  16      17      18      19      20
4294967295      -128    21      22      23      24      25
18446744073709551615    9223372036854775807     26      27      28      29      30
SELECT * FROM bigint_myisam WHERE c1 < 18446744073709551615 ORDER BY c1,c6;
c1      c2      c3      c4      c5      c6      c7
0       -9223372036854775808    1       2       3       4       5
255     -2147483648     6       7       8       9       10
65535   -8388608        11      12      13      14      15
16777215        -32768  16      17      18      19      20
4294967295      -128    21      22      23      24      25
[24 Mar 2009 14:38] Ann Harrison 
Duplicate of 40950 - Falcon indexes broken for unsigned bigint
[26 Mar 2009 12:57] Lars-Erik Bjørk 
I have confirmed that this bug is no longer reproducible after the patch for bug#40950