pingcap / pingcap/tidb

Redundant outer DISTINCT changes FLOAT values produced by UNION

Open
#70,235 3 comments 0 reactions 0 assignees View on GitHub
affects-7.5 affects-8.1 affects-8.5 contribution severity/major sig/execution type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

Adding a redundant outer `SELECT DISTINCT` around a `UNION` changes the returned `FLOAT` value. The inner `UNION` already uses distinct set semantics, so the outer `DISTINCT` should neither change the rows nor the values.

The issue occurs when the first, empty `UNION` branch establishes a `DECIMAL` result type while the second branch supplies a `FLOAT`. Without the outer `DISTINCT`, TiDB returns the value as `26.9`. Wrapping the same `UNION` in `SELECT DISTINCT *` exposes the underlying binary floating-point value as `26.899999618530273`.

Both results are stable across repeated executions. This is not caused by row ordering or an unordered `LIMIT`.

### 1. Minimal reproduce step (Required)

```sql
DROP DATABASE IF EXISTS rift_outer_distinct_float;
CREATE DATABASE rift_outer_distinct_float;
USE rift_outer_distinct_float;

CREATE TABLE t (
v FLOAT
);

INSERT INTO t VALUES (26.9);

-- Original: returns 26.9
SELECT CAST(v AS DECIMAL(10,2)) AS x
FROM t
WHERE FALSE
UNION
SELECT v
FROM t;

-- Mutated: returns 26.899999618530273
SELECT DISTINCT *
FROM (
SELECT CAST(v AS DECIMAL(10,2)) AS x
FROM t
WHERE FALSE
UNION
SELECT v
FROM t
) AS s;

SELECT tidb_version();
```
### 2. What did you expect to see? (Required)
The original and wrapped queries should return exactly the same value because `UNION` already removes duplicates:

```text
26.9
```

Adding a redundant outer `DISTINCT` should not change result type coercion, precision, or client-visible values.

### 3. What did you see instead (Required)
```text
Original: 26.9
Mutated: 26.899999618530273
```

The two queries were each executed repeatedly and returned the same respective value on every execution.

### 4. What is your TiDB version? (Required)

```text
Release Version: v8.5.7
Edition: Community
Git Commit Hash: 202b7f47286a1109b5c957401d34c9358d130ae0
Git Branch: HEAD
UTC Build Time: 2026-07-15 02:06:00
GoVersion: go1.25.10
Race Enabled: false
Check Table Before Drop: false
Store: unistore
```

Contributor guide

Open the contributing guide

Research direction

Start by running the SQL reproducer in the issue and compare the UNION result with the redundant outer SELECT DISTINCT result. Trace TiDB's UNION type coercion and DISTINCT handling; the fix is complete when both queries preserve the expected 26.9 value without exposing the binary FLOAT representation.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.