Bug #42265 Falcon range access returns wrong result(missing rows) for unsigned bigint index
Submitted: 22 Jan 2009 11:05 Modified: 28 Jan 2009 9:10
Reporter: Nidhi Shrotriya Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S3 (Non-critical)
Version:6.0.8-alpha OS:Any
Assigned to: Hakan Küçükyılmaz CPU Architecture:Any
Tags: F_ENCODING

[22 Jan 2009 11:05] Nidhi Shrotriya 
Description:
When a bigint unsigned index is used, range access on index returns wrong results. 
Note that this seems related to Bug#40607 (Falcon unsigned indexes broken), except that

The strange thing to note is
|          0 | -9223372036854775808 |    1 |    2 |    3 |  4 |    5 |

appearing in the result but with same value of c1 -> 0
|                    0 | -9223372036854775808 |   31 |   32 |   33 | 34 |   35 |
the above row is missing.

on SELECT * FROM falcon_bigint1 WHERE c1 <> 255 ORDER BY c1,c6; 
as below

mysql> CREATE TABLE falcon_bigint1(c1 BIGINT UNSIGNED NOT NULL, c2 BIGINT NULL, c3 TINYINT, c4 SMALLINT , c5 MEDIUMINT, c6 INT, c7 INTEGER, PRIMARY KEY(c1,c6)) engine=falcon;

mysql> select * from falcon_bigint1;
+----------------------+----------------------+------+------+------+----+------+
| 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 |
|                    0 | -9223372036854775808 |   31 |   32 |   33 | 34 |   35 |
| 18446744073709551615 |  9223372036854775807 |   36 |   37 |   38 | 39 |   40 |
+----------------------+----------------------+------+------+------+----+------+
8 rows in set (0.01 sec)

mysql> SELECT * FROM falcon_bigint1 WHERE c1 <> 255 ORDER BY c1,c6;
+------------+----------------------+------+------+------+----+------+
| c1         | c2                   | c3   | c4   | c5   | c6 | c7   |
+------------+----------------------+------+------+------+----+------+
|          0 | -9223372036854775808 |    1 |    2 |    3 |  4 |    5 |
|      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)

Missing rows in the result ->
----------------------------------------
| 18446744073709551615 |  9223372036854775807 |   26 |   27 |   28 | 29 |   30 |
|                    0 | -9223372036854775808 |   31 |   32 |   33 | 34 |   35 |
| 18446744073709551615 |  9223372036854775807 |   36 |   37 |   38 | 39 |   40 |

How to repeat:
mysql> CREATE TABLE falcon_bigint1(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.17 sec)

mysql> INSERT INTO falcon_bigint1 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.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>  INSERT INTO falcon_bigint1 VALUES(-128,-9223372036854775809,31,32,33,34,35),(18446744073709551616,9223372036854775808,36,37,38,39,40);
Query OK, 2 rows affected, 4 warnings (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 4

mysql> select * from falcon_bigint1;
+----------------------+----------------------+------+------+------+----+------+
| 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 |
|                    0 | -9223372036854775808 |   31 |   32 |   33 | 34 |   35 |
| 18446744073709551615 |  9223372036854775807 |   36 |   37 |   38 | 39 |   40 |
+----------------------+----------------------+------+------+------+----+------+
8 rows in set (0.01 sec)

mysql> SELECT * FROM falcon_bigint1 WHERE c1 <> 255 ORDER BY c1,c6;
+------------+----------------------+------+------+------+----+------+
| c1         | c2                   | c3   | c4   | c5   | c6 | c7   |
+------------+----------------------+------+------+------+----+------+
|          0 | -9223372036854775808 |    1 |    2 |    3 |  4 |    5 |
|      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)

Missing rows in the result ->
----------------------------------------
| 18446744073709551615 |  9223372036854775807 |   26 |   27 |   28 | 29 |   30 |
|                    0 | -9223372036854775808 |   31 |   32 |   33 | 34 |   35 |
| 18446744073709551615 |  9223372036854775807 |   36 |   37 |   38 | 39 |   40 |

Also the strange thing to note is
|          0 | -9223372036854775808 |    1 |    2 |    3 |  4 |    5 |

appearing in the result but with same value of c1 -> 0
|                    0 | -9223372036854775808 |   31 |   32 |   33 | 34 |   35 |
the above row doesn't appear.
[22 Jan 2009 12:39] Hakan Küçükyılmaz 
I tried your example with our latest Falcon code from mysql-6.0-falcon-team tree. I can't repeat it:

[13:36] root@test>select * from falcon_bigint1;
+----------------------+----------------------+------+------+------+----+------+
| 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 |
|                    0 | -9223372036854775808 |   31 |   32 |   33 | 34 |   35 |
| 18446744073709551615 |  9223372036854775807 |   36 |   37 |   38 | 39 |   40 |
+----------------------+----------------------+------+------+------+----+------+
8 rows in set (0.00 sec)

[13:37] root@test>SELECT * FROM falcon_bigint1 WHERE c1 <> 255 ORDER BY c1,c6;
+----------------------+----------------------+------+------+------+----+------+
| c1                   | c2                   | c3   | c4   | c5   | c6 | c7   |
+----------------------+----------------------+------+------+------+----+------+
|                    0 | -9223372036854775808 |    1 |    2 |    3 |  4 |    5 |
|                    0 | -9223372036854775808 |   31 |   32 |   33 | 34 |   35 |
|                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 |
+----------------------+----------------------+------+------+------+----+------+
7 rows in set (0.00 sec)
[28 Jan 2009 9:01] Nidhi Shrotriya 
I also tried it with mysql-6.0-falcon-team and it seems to be fixed in the latest code.
[28 Jan 2009 9:10] Hakan Küçükyılmaz 
Thanks for your bug report. I am glad that it works in our latest code base. I am closing this bug report as "Can't repeat".