Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Duplicate Rows in Query with Binary Condition #57390

Open
dwenking opened this issue Nov 14, 2024 · 0 comments · May be fixed by #57584
Open

Duplicate Rows in Query with Binary Condition #57390

dwenking opened this issue Nov 14, 2024 · 0 comments · May be fixed by #57584
Labels
impact/wrong-result may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 may-affects-6.5 may-affects-7.1 may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/major sig/planner SIG: Planner type/bug The issue is confirmed as a bug.

Comments

@dwenking
Copy link

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

A query on table t1 with conditions involving the VARBINARY column (c1) returns duplicate rows unexpectedly.
Although t1 contains only five distinct records, the query returns 15 rows, repeating each original row three times.
This suggests that the query conditions or handling of binary values in VARBINARY columns may not be functioning as expected.

-- Set up
CREATE TABLE t0 (c0 FLOAT, c1 VARBINARY(11));
INSERT INTO t0 (c0, c1) VALUES (8.416507379856948e+37, 0xBD18FF9DA18);
INSERT INTO t0 (c0, c1) VALUES (-8.039100705145107e+36, 0x751F7D92AF2);
INSERT INTO t0 (c0, c1) VALUES (-3.2141114349670245e+37, 0x22693D9BBE4);
INSERT INTO t0 (c0, c1) VALUES (-1.972829282794721e+35, 0x9C0F3D7E886);
INSERT INTO t0 (c0, c1) VALUES (4.535289015159612e+37, 0x2BA66016016);
CREATE TABLE t1 (c0 double , c1 VARBINARY(11));
INSERT INTO t1 SELECT (AVG(c0)), c1 FROM t0 GROUP BY c1;

-- This SELECT returns unexpected results
my:root@127.0.0.1:4000=> SELECT c0, c1 FROM t1 WHERE (c1 OR c1) IN (SELECT c1 FROM t1 WHERE (c1 <= (0x991D3FA2F9C))) AND ((c0 AND 8.98447659672538e+29));
           c0            |            c1             
-------------------------+---------------------------
   -8.03910101011187e+36 | \aQ\xf7\xd9*\xf2 
   -8.03910101011187e+36 | \aQ\xf7\xd9*\xf2 
   -8.03910101011187e+36 | \aQ\xf7\xd9*\xf2 
 -1.9728293777139094e+35 | 	\xc0\xf3\xd7\xe8\x86 
 -1.9728293777139094e+35 | 	\xc0\xf3\xd7\xe8\x86 
 -1.9728293777139094e+35 | 	\xc0\xf3\xd7\xe8\x86 
  8.4165073412274785e+37 | \vя\xf9\xda\x18 
  8.4165073412274785e+37 | \vя\xf9\xda\x18 
  8.4165073412274785e+37 | \vя\xf9\xda\x18 
 -3.2141113638907526e+37 | \x02&\x93ٻ\xe4 
 -3.2141113638907526e+37 | \x02&\x93ٻ\xe4 
 -3.2141113638907526e+37 | \x02&\x93ٻ\xe4 
   4.535288764243739e+37 | \x02\xbaf\x01`\x16 
   4.535288764243739e+37 | \x02\xbaf\x01`\x16 
   4.535288764243739e+37 | \x02\xbaf\x01`\x16 
(15 rows)

2. What did you expect to see? (Required)

The query should return a maximum of five rows, reflecting the distinct records in t1, without any duplicates.

3. What did you see instead (Required)

See above.

4. What is your TiDB version? (Required)

my:[email protected]:4000=> SELECT tidb_version();
                      tidb_version()                       
-----------------------------------------------------------
 Release Version: v8.3.0                                  +
 Edition: Community                                       +
 Git Commit Hash: 1a0c3ac3292fff7742faa0c00a662ccb66ba40db+
 Git Branch: HEAD                                         +
 UTC Build Time: 2024-08-20 10:23:00                      +
 GoVersion: go1.21.10                                     +
 Race Enabled: false                                      +
 Check Table Before Drop: false                           +
 Store: tikv 
(1 row)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
impact/wrong-result may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 may-affects-6.5 may-affects-7.1 may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/major sig/planner SIG: Planner type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
2 participants
@jebter @dwenking