Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
12.3.3
-
None
-
Linux x86_64, MariaDB.org Ubuntu 24.04 binary distribution
-
Not for Release Notes
Description
MariaDB 12.3.3 returns a row containing integer 1 when forced index lookups on TINYINT, SMALLINT, and MEDIUMINT columns compare it with the fractional DOUBLE value 0.6e0. Equivalent scans correctly return no rows.
Save the following as case.sql and run it with mariadb < case.sql using an account that can create and drop a database:
CREATE DATABASE IF NOT EXISTS integer_probe; |
USE integer_probe; |
DROP TABLE IF EXISTS numbers; |
CREATE TABLE numbers ( |
id INT PRIMARY KEY, |
v_tiny TINYINT,
|
v_small SMALLINT, |
v_medium MEDIUMINT,
|
KEY k_tiny(v_tiny), |
KEY k_small(v_small), |
KEY k_medium(v_medium) |
);
|
INSERT INTO numbers VALUES (1,1,1,1); |
|
|
SELECT 'tiny_force' AS case_name, COUNT(*) AS matched |
FROM numbers FORCE INDEX(k_tiny) WHERE v_tiny=0.6; |
SELECT 'tiny_scan' AS case_name, COUNT(*) AS matched |
FROM numbers IGNORE INDEX(k_tiny) WHERE v_tiny=0.6; |
SELECT 'small_force' AS case_name, COUNT(*) AS matched |
FROM numbers FORCE INDEX(k_small) WHERE v_small=0.6; |
SELECT 'small_scan' AS case_name, COUNT(*) AS matched |
FROM numbers IGNORE INDEX(k_small) WHERE v_small=0.6; |
SELECT 'medium_force' AS case_name, COUNT(*) AS matched |
FROM numbers FORCE INDEX(k_medium) WHERE v_medium=0.6; |
SELECT 'medium_scan' AS case_name, COUNT(*) AS matched |
FROM numbers IGNORE INDEX(k_medium) WHERE v_medium=0.6; |
|
|
SELECT 'tiny_double_force' AS case_name, COUNT(*) AS matched |
FROM numbers FORCE INDEX(k_tiny) WHERE v_tiny=0.6e0; |
SELECT 'tiny_double_scan' AS case_name, COUNT(*) AS matched |
FROM numbers IGNORE INDEX(k_tiny) WHERE v_tiny=0.6e0; |
SELECT 'small_double_force' AS case_name, COUNT(*) AS matched |
FROM numbers FORCE INDEX(k_small) WHERE v_small=0.6e0; |
SELECT 'small_double_scan' AS case_name, COUNT(*) AS matched |
FROM numbers IGNORE INDEX(k_small) WHERE v_small=0.6e0; |
SELECT 'medium_double_force' AS case_name, COUNT(*) AS matched |
FROM numbers FORCE INDEX(k_medium) WHERE v_medium=0.6e0; |
SELECT 'medium_double_scan' AS case_name, COUNT(*) AS matched |
FROM numbers IGNORE INDEX(k_medium) WHERE v_medium=0.6e0; |
|
|
EXPLAIN SELECT id FROM numbers FORCE INDEX(k_tiny) |
WHERE v_tiny=0.6e0; |
|
|
DROP TABLE numbers; |
DROP DATABASE integer_probe; |
Observed results
tiny_force 0
|
tiny_scan 0
|
small_force 0
|
small_scan 0
|
medium_force 0
|
medium_scan 0
|
tiny_double_force 1
|
tiny_double_scan 0
|
small_double_force 1
|
small_double_scan 0
|
medium_double_force 1
|
medium_double_scan 0
|
The decimal 0.6 controls agree and return zero for every type. With DOUBLE 0.6e0, each forced-index query returns one row while its scan counterpart returns zero. The recorded EXPLAIN for the forced TINYINT query reports ref access through k_tiny with a constant key.
Expected behavior
The stored integer value 1 is not equal to 0.6. All twelve queries should therefore return zero rows, regardless of whether MariaDB uses an index lookup or a scan. Forming an optimizer key from the DOUBLE value must not turn this lossy conversion into an exact match.
This occurs when indexed TINYINT, SMALLINT, or MEDIUMINT columns are compared with a fractional DOUBLE value through an optimizer key lookup.
Attachments
Issue Links
- duplicates
-
-
- In Review
-