Equality selection gives wrong result because of corrupt hash table

[swingbit]

Possibly after a failure due to insufficient disk space, a hash table seems to be in an inconsistent state. A range select (using imprints) returns 9 results, while the point select (using hash) returns 0 results:

sql>\d cacheinfo 
CREATE TABLE "spinque"."cacheinfo" (
	"cacheid"   INTEGER,
	"attribute" CHARACTER LARGE OBJECT,
	"value"     CHARACTER LARGE OBJECT
);

sql>select * from sys.storage() where table='cacheinfo' and column='cacheid';
+---------+-----------+---------+------+----------+----------+--------+-----------+------------+----------+--------+-------+----------+--------+-----------+--------+----------+
| schema  | table     | column  | type | mode     | location | count  | typewidth | columnsize | heapsize | hashes | phash | imprints | sorted | revsorted | unique | orderidx |
+=========+===========+=========+======+==========+==========+========+===========+============+==========+========+=======+==========+========+===========+========+==========+
| spinque | cacheinfo | cacheid | int  | writable | 10/1055  | 101045 |         4 |     404180 |        0 | 463308 | false |     2868 | false  | false     | false  |        0 |
+---------+-----------+---------+------+----------+----------+--------+-----------+------------+----------+--------+-------+----------+--------+-----------+--------+----------+
1 tuple

sql>select cacheid from  cacheinfo where cacheid > 519 and cacheid < 521;
+---------+
| cacheid |
+=========+
|     520 |
|     520 |
|     520 |
|     520 |
|     520 |
|     520 |
|     520 |
|     520 |
|     520 |
+---------+
9 tuples

sql>select cacheid from  cacheinfo where cacheid = 520;
+---------+
| cacheid |
+=========+
+---------+
0 tuples

According to sys.storage(), the hash is not persistent, so I tried to stop/start the database to see if it would drop it. Nothing changes. I also tried to analyze the column, but nothing changes.

This is not easily reproducible, but also not urgent to me. I'm reporting it hoping that it can give a clue as to where a problem with hashing might be. It looks to me that it is not robust against a failure.

Software versions

• MonetDB version number v11.39.18
• OS and version: FC32
• self-installed and compiled

[njnes]

do you still have the db? Do you have a repro for this, or could we close this issue as its been a while since this version.