Bug #120366 Inconsistent query result caused by SUBSTR zero-based index handling and BIT_LENGTH evaluation in MySQL

• Submitted: 28 Apr 11:17
• Modified: 30 Apr 9:00
• Reporter: Jasper Andrew
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.43 (MySQL Community Server - GPL)
• OS: Any (Docker container)
• CPU Architecture: Any (x86_64)
• Tags: bit_length, Logic bug, substr

[28 Apr 11:17] Jasper Andrew

Description:
It seems that MySQL inconsistently handles SUBSTR when the start position is zero or derived from an integer column, combined with BIT_LENGTH evaluation and implicit type conversion. This leads to divergent results compared to other SQL engines.

In particular, MySQL appears to treat SUBSTR('27', 0, n) as if the start position were 1, while other databases either return an empty string or NULL according to standard string indexing behavior. This difference affects the computed result of BIT_LENGTH(SUBSTR(...)), which in turn changes the evaluation of the WHERE predicate.

As a result, MySQL returns rows {1, 3, 4}, whereas other databases consistently return only {1}, indicating incorrect or inconsistent filtering behavior in MySQL under this expression pattern.

How to repeat:
```SQL
-- SCHEMA

CREATE TABLE comments (
    id          INT,
    post_id     INT,
    user_id     INT,
    content     VARCHAR(1000),
    is_spam     INT,
    created_at  TIMESTAMP NULL
);
INSERT INTO comments VALUES
(1, 1, 2, 'Nice post', 0, '2022-01-20 10:00:00'),
(2, 1, 3, 'Spam here', 1,  '2022-01-21 11:00:00'),
(3, 2, 1, 'Thanks',    0, '2022-01-22 12:00:00'),
(4, 4, 5, NULL,        0, '2022-01-23 13:00:00');

-- Trigger sql
SELECT
    ref_0.id AS c0
FROM comments AS ref_0
WHERE BIT_LENGTH(
    SUBSTR(
        '27',
        ref_0.is_spam,
        ref_0.id
    )
) <= ref_0.user_id;

-- result: {1,3,4} wrong!
```

[30 Apr 9:00] Roy Lyseng

Thank you for the bug report.
However, this is not a bug. MySQL accepts 0 and eve negative values for the start position for SUBSTRING, according to this quote from the user manual:

The forms without a len argument return a substring from string str starting at position pos.
The forms with a len argument return a substring len characters long from string str, starting at
position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative
value for pos. In this case, the beginning of the substring is pos characters from the end of the
string, rather than the beginning. A negative value may be used for pos in any of the forms of this
function. A value of 0 for pos returns an empty string.