Inconsistent query result caused by SUBSTR zero-based index handling and BIT_LENGTH evaluation in MariaDB

• 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

, whereas other databases consistently return only

, indicating incorrect or inconsistent filtering behavior in MySQL under this expression pattern.

• How to reproduce

-- 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!

*