DOUBLE UNSIGNED column as shift right operand yields inconsistent results between direct query and scalar subquery (0 vs 1) due to different integer conversion paths.

Description

Problem Description

When a DOUBLE UNSIGNED column serves as the right operand of a shift operator (<<), a direct single-table query and a multi-table scalar subquery reconstruction yield different results.

The discrepancy arises from MariaDB adopting different conversion strategies when converting the DOUBLE UNSIGNED value to an integer, depending on whether the unsigned flag is preserved in different query paths (single-table vs. scalar subquery).

How to Reproduce

Execute the following SQL:

DROP DATABASE IF EXISTS repro_mariadb_db13_904_min;

CREATE DATABASE repro_mariadb_db13_904_min;

USE repro_mariadb_db13_904_min;

-- Single-table structure

CREATE TABLE source_t (

v DOUBLE UNSIGNED NOT NULL

);

INSERT INTO source_t VALUES (9223372036854775807);

-- Multi-table scalar subquery reconstruction structure

CREATE TABLE left_t (

vp_rowid BIGINT NOT NULL

);

CREATE TABLE right_t (

vp_rowid BIGINT NOT NULL,

v DOUBLE UNSIGNED NOT NULL

);

INSERT INTO left_t VALUES (1);

INSERT INTO right_t VALUES (1, 9223372036854775807);

-- Direct single-table calculation: actually returns 0

SELECT ((1503381287 << v) AND -553173254) AS result

FROM source_t;

-- Multi-table scalar subquery reconstruction: actually returns 1

SELECT ((1503381287 << v) AND -553173254) AS result

FROM (

SELECT l.vp_rowid,

(SELECT r.v

FROM right_t r

WHERE r.vp_rowid = l.vp_rowid) AS v

FROM left_t l

) AS vp_rel;

Expected Behavior

Both queries should return the same result.

The outcome of the shift and boolean operations should be identical regardless of whether the value comes from a direct single-table read or a scalar subquery within a derived table.

Actual Behavior

Single-table query returns: 0
Multi-table scalar subquery returns: 1

The results are inconsistent.

Additional Analysis

In supplementary verification, the value v read from both paths is displayed as 9.223372036854776e18, and:

CAST(v AS UNSIGNED)

yields 9223372036854775808 on both sides.

The difference arises in MariaDB's evaluation path when the DOUBLE UNSIGNED scalar subquery result participates in the << and boolean AND expressions, not in data copying, type declaration, or VP comparator issues.

When the shift operator << converts the right operand to an integer, it selects different conversion paths based on the unsigned flag of the operand:

Single-table path: v is treated as signed, saturating to LLONG_MAX (0x7FFFFFFFFFFFFFFF) during conversion. After truncating to 32 bits, it becomes 0xFFFFFFFF, causing an excessively large shift amount and returning 0.

Subquery path: v retains its unsigned attribute, and the conversion result is directly 2^63 (0x8000000000000000). Truncating the lower 32 bits yields 0, effectively equivalent to << 0, producing a non-zero result.