| Bug #24897 | Different results returned because of type-casting failures | ||
|---|---|---|---|
| Submitted: | 8 Dec 2006 3:44 | Modified: | 10 Dec 2006 3:46 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.22 | OS: | |
| Assigned to: | Sveta Smirnova | CPU Architecture: | Any |
[8 Dec 2006 3:44]
[ name withheld ]
Data, table structure
Attachment: invoicecopy.zip (application/zip, text), 167.80 KiB.
[8 Dec 2006 3:46]
[ name withheld ]
Ignore the url; use the attached zip. 5.0.22 is the most recent available version I can install. The exact queries are at the bottom of the .sql; Quoting the arguments for the BETWEEN as strings makes this sql function correctly - the problem seems to exist when casting between int and string / temporary tables.
[8 Dec 2006 8:11]
Sveta Smirnova
Thank you for the report. I can't repeat it using last BK sources.
[10 Dec 2006 3:46]
[ name withheld ]
agreed, WFM with 5.0.27 / windows
Description: I have two queries: A: SELECT DISTINCT i.inv_id FROM tbl_invoice_copy i WHERE i.inv_exportdate BETWEEN 1162301400 AND 1163078999; #4058 results B: SELECT DISTINCT i.inv_id FROM tbl_invoice_copy i WHERE i.inv_exportdate BETWEEN 1162301400 AND 1163078999 AND i.inv_id NOT IN ('1','2','3',[etc]). #0 results - The amount of DISTINCT inv_ids in the NOT IN is 3200; so should return at least 858 results; This appears to be only occuring when a temporary table is needed; because I cannot produce a reduced test case for it. How to repeat: See zip file of sql: http://www.box.net/public/29mc6gx0mi