pingcap / pingcap/tidb

unexpected query result using having

Open
#57,461 3 comments 0 reactions 1 assignee Claimed by @hawkingrei View on GitHub
fuzz/randomtest impact/wrong-result may-affects-5.4 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 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!

### 1. Minimal reproduce step (Required)

``` SQL

CREATE TABLE `t272d1c4f` (
`col_42` blob NOT NULL,
PRIMARY KEY (`col_42`(5)) /*T![clustered_index] CLUSTERED */,
KEY `idx_17` (`col_42`(4)),
KEY `idx_18` (`col_42`(5))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `t272d1c4f` VALUES(x''),(x'21522126'),(x'234e745454735a34234c'),(x'2533234e39'),(x'2538486f7050587928623333633850735067'),(x'25644477413434467a4f256b366d32'),(x'256f6225664c7446436b6b7635417741315e'),(x'26234b454125'),(x'262d714c7e335938364f29353d432d52'),(x'26514b4858515366495045285f'),(x'28624b6f513238'),(x'29445e6a65'),(x'29532a646d4c2547477a746143265e4f66'),(x'2954'),(x'2a'),(x'2a235845585f2175'),(x'2a2440524e623d5a2a486b5365253d'),(x'2b32'),(x'2d'),(x'3034325745'),(x'307a644d'),(x'324563'),(x'344056282a62445a6e72514f4e'),(x'3638694f6c415e6b455949347e'),(x'372837635766706c'),(x'3976297733516c565339'),(x'3d78237156586121493256726b62594c69'),(x'3d7a67335248785a454429513d5756555338'),(x'45772a7748'),(x'48'),(x'4b695640256849486e4350232b242d5f5073'),(x'4c566472717137484033725040567021'),(x'4c6178286e4a784c36334b7e402d'),(x'4d4c7e6d42662b502626555f'),(x'4e71395e3378373d3476297866422353'),(x'4f553729474d38742a5f7568'),(x'50'),(x'5054336a574626'),(x'515f6d32476e6342547e5450685866316e'),(x'516a5e3026672b2942234c29634b'),(x'5531644d64'),(x'5673554161546f3d5f53707462324c75'),(x'5f6444'),(x'633349544f'),(x'675078715654396274313d4d485f3938742a'),(x'694e256b506b6b3d554769484279'),(x'6b'),(x'6b683124'),(x'6c'),(x'6d512b35422161'),(x'6f2b4679573d766d34504272'),(x'6f6f337959682a47246177505a577736'),(x'73494f4f777340717a79316c294757'),(x'73622a4179'),(x'745251317e7a365a'),(x'747a6f50355337'),(x'77674b'),(x'7774'),(x'777469'),(x'7845'),(x'79');

SELECT SUBSTRING(`t272d1c4f`.`col_42`, 0) AS `r0`,MIN(`t272d1c4f`.`col_42`) AS `r1`,BIT_AND(`t272d1c4f`.`col_42`) AS `r2` FROM `t272d1c4f` WHERE `t272d1c4f`.`col_42` IN ('0s^MAjiT=','MW','C=lK@w+KTU3') OR `t272d1c4f`.`col_42`='Lax(nJxL63K~@-' GROUP BY `t272d1c4f`.`col_42` HAVING `t272d1c4f`.`col_42`<'hFqPzt09DpT' AND `t272d1c4f`.`col_42`>'I4ea@b~8';

SELECT * FROM `t272d1c4f` WHERE `t272d1c4f`.`col_42` IN ('0s^MAjiT=','MW','C=lK@w+KTU3') OR `t272d1c4f`.`col_42`='Lax(nJxL63K~@-' GROUP BY `t272d1c4f`.`col_42` HAVING `t272d1c4f`.`col_42`<'hFqPzt09DpT' AND `t272d1c4f`.`col_42`>'I4ea@b~8';
```

### 2. What did you expect to see? (Required)
Both queries return one row.
``` SQL
mysql> SELECT SUBSTRING(`t272d1c4f`.`col_42`, 0) AS `r0`,MIN(`t272d1c4f`.`col_42`) AS `r1`,BIT_AND(`t272d1c4f`.`col_42`) AS `r2` FROM `t272d1c4f` WHERE `t272d1c4f`.`col_42` IN ('0s^MAjiT=','MW','C=lK@w+KTU3') OR `t272d1c4f`.`col_42`='Lax(nJxL63K~@-' GROUP BY `t272d1c4f`.`col_42` HAVING `t272d1c4f`.`col_42`<'hFqPzt09DpT' AND `t272d1c4f`.`col_42`>'I4ea@b~8';
Empty set (0.00 sec)

mysql> SELECT * FROM `t272d1c4f` WHERE `t272d1c4f`.`col_42` IN ('0s^MAjiT=','MW','C=lK@w+KTU3') OR `t272d1c4f`.`col_42`='Lax(nJxL63K~@-' GROUP BY `t272d1c4f`.`col_42` HAVING `t272d1c4f`.`col_42`<'hFqPzt09DpT' AND `t272d1c4f`.`col_42`>'I4ea@b~8';
+--------------------------------+
| col_42 |
+--------------------------------+
| 0x4C6178286E4A784C36334B7E402D |
+--------------------------------+
1 row in set (0.00 sec)
```
### 3. What did you see instead (Required)
The first query return empty result.
### 4. What is your TiDB version? (Required)
248000c998517716263ac866ccce90a6905ff018

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.