Bug #121062 Prepared statement with USING variables returns empty result against DATE + TIME columns

• Submitted: 6 Aug 14:41
• Modified: 6 Aug 14:50
• Reporter: Xiao Q.
• Status: Open
• Category: MySQL Server: Prepared statements
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Any
• CPU Architecture: Any

[6 Aug 14:41] Xiao Q.

Description:
When querying a table with both a DATE and a TIME column through a prepared
statement with USING user variables, the query returns an empty result set
even though the row should match. The identical query using direct string
literals works correctly.

The bug manifests when:
  - A prepared statement has two ? parameters,
  - The corresponding columns have different temporal types (DATE vs TIME),
  - The bound user variables are plain datetime strings.

Single-parameter prepared statements against DATE or TIME alone work correctly.
Replacing one bound value with CONCAT(CURDATE(), ' 00:00:00') also avoids the
problem, suggesting the issue lies in how the server resolves internal types
of string-type USING parameters against heterogeneous temporal columns.

How to repeat:
-- Step 1
CREATE TABLE t (
    id   BIGINT AUTO_INCREMENT NOT NULL,
    a    DATE    DEFAULT '1800-01-01' NOT NULL,
    b    TIME    DEFAULT '00:00:00'   NOT NULL,
    CONSTRAINT t_pk PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Step 2
INSERT INTO t VALUES (1, '2000-01-01', '00:00:00');

-- Step 3 Direct string literal comparison — WORKS correctly
SELECT id FROM t
WHERE a = '2000-01-01 00:00:00' AND b = '1800-01-01 00:00:00';
-- Result: Found 1

-- Step 4 Prepared statement
PREPARE x1 FROM "SELECT id FROM t WHERE a = ? AND b = ?";
SET @a = '2000-01-01 00:00:00';
SET @b = '1800-01-01 00:00:00';
EXECUTE x1 USING @a, @b;
-- Result: Empty set (expected 1)

-- Helpful
PREPARE x2 FROM "SELECT id FROM t WHERE a = ? AND b = ?";
SET @a = '2000-01-01 00:00:00';
SET @b = CONCAT(CURDATE(), ' 00:00:00');
EXECUTE x2 USING @a, @b;
-- Result: Found 1

SET @a = '2000-01-01 00:00:00';
SET @b = '00:00:00';
EXECUTE x2 USING @a, @b;
-- Result: Found 1

-- Single parameter — WORKS
PREPARE x4 FROM "SELECT id FROM t WHERE b = ?";
SET @b = '1800-01-01 00:00:00';
EXECUTE x4 USING @b;
-- Result: 1
PREPARE x5 FROM "SELECT id FROM t WHERE a = ?";
SET @a = '2000-01-01 00:00:00';
EXECUTE x5 USING @a;
-- Result: 1