| Bug #100187 | Unexpectedly more rows than real results | ||
|---|---|---|---|
| Submitted: | 11 Jul 2020 21:32 | Modified: | 13 Jul 2020 13:02 |
| Reporter: | Josef Fröhle | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S1 (Critical) |
| Version: | 8.0.20 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | json, results, rows, unexpected | ||
[11 Jul 2020 21:57]
Josef Fröhle
Uploading mysql-bug-data-100187-2ndtry.zip to //support/incoming/mysql-bug-data-100187-2ndtry.zip
mysql-bug-data-100187-2ndtry.zip 100% 54MB 2.5MB/s 00:21
Calculating SHA256 Checksum [ / ]
SHA-256 Checksum: bd573834cf6046caeadebb3b57b93da83ae45590ab21bf922ebfae799e526da0
Virus Scan for mysql-bug-data-100187-2ndtry.zip is in progress, please wait..
Scanning ......McAfee VirusScan Command Line for Linux64 Version: 6.1.2.230
Copyright (C) 2018 McAfee, Inc.
(408) 988-3832 LICENSED COPY - April 10 2019
AV Engine version: 6000.8403 for Linux64.
Dat set version: 9678 created Jul 10 2020
Scanning for 668680 viruses, trojans and variants.
/ftproot/sftp/support/incoming/mysql-bug-data-100187-2ndtry.zip/hwtools_datacollector.sql ... is OK.
/ftproot/sftp/support/incoming/mysql-bug-data-100187-2ndtry.zip/hwtools_routines.sql ... is OK.
/ftproot/sftp/support/incoming/mysql-bug-data-100187-2ndtry.zip/README.txt ... is OK.
/ftproot/sftp/support/incoming/mysql-bug-data-100187-2ndtry.zip ... is OK.
Summary Report on /ftproot/sftp/support/incoming/mysql-bug-data-100187-2ndtry.zip
File(s)
Total files:................... 1
Total Objects:................. 4
Clean:......................... 1
Not Scanned:................... 0
Possibly Infected:............. 0
Objects Possibly Infected:..... 0
Cleaned:....................... 0
Moved:......................... 0
Deleted:....................... 0
Time: 00:00.01
sftp>
mysql-bug-data-100187-2ndtry.zip uploaded successfully...
[11 Jul 2020 22:04]
Josef Fröhle
Here is a screenshot of Workbench with the error. The same result is returned on the console.
Attachment: mysql_bug1.PNG (image/png, text), 218.04 KiB.
[12 Jul 2020 12:32]
Josef Fröhle
This works like expected: SELECT BIN_TO_UUID(id) as id, created_at, response_date, sha224, available_for FROM datacollector WHERE processed = "N" and JSON_CONTAINS(available_for, "[\"registration\"]") = 1 order by created_at DESC;
[13 Jul 2020 13:02]
MySQL Verification Team
Hi Mr. Frohle, Thank you for your bug report. However, I do not think that this is a bug. If you read our Reference Manual carefully, you will see that those two functions work exactly as they are supposed to. Simply, JSON_CONTAINS() can not be replaced by the expression that you used in the first query. Not a bug.
Description: There is a database that holds different data as JSON. If you search in the database, instead of the real 18 or 19 results, 9767 rows are returned. How to repeat: SELECT BIN_TO_UUID(id) as id, created_at, response_date, sha224, available_for FROM hwtools.datacollector WHERE processed = "N" and ("registration" MEMBER OF(`available_for`)) order by created_at DESC