MySQL Bugs: #37256: SELECT alternately returns correct results and no results

Bug #37256	SELECT alternately returns correct results and no results
Submitted:	6 Jun 2008 22:54	Modified:	11 Jul 2008 22:41
Reporter:	Tim Faust	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	6.0.3	OS:	Windows (Vista Ultimate)
Assigned to:		CPU Architecture:	Any
Tags:	inconsistent, results, SELECT

Description:
A certain select against a given table will alternately return the correct results  and then no results the next execution. Minor changes such as dropping the order by clause, or removing part of the where clause cause it to work correctly, but since the results aren't exactly what I need that doesn't really help much.

The problem also occurred in 6.0.4, but I had to back off to 6.0.3 for other problems,so the examples are against a 6.0.3 setup.

There are other bugs that might be related, but I saw nothing obvious jump out at me as a duplicate. Attached files have table, data and stored function needed to reproduce.

How to repeat:
Setup is 6.0.3 on windows Vista (UAC is On), multilingual(UTF8) with InnoDB. Nothing else special is needed (that I know of)

Query in question is:
SELECT Y.COMPANYID, Y.NAME FROM TBL_COMPANY Y
WHERE (Y.ISPRIVATE = 0 OR Y.MANAGEUSERID = FNC_CONVERT_CHAR_TO_UUID('086273C0-12CF-446E-9AFE-F09164628D4D'))
AND Y.PARENTCOMPANYID IS NULL ORDER BY Y.NAME ASC;

1.) Restore the DB provided, it is just one table with some data and the function used to convert string representations of GUIDS to Binary(16).

2.) Using Query Browser (didn't test with command line) run the query against that database. run it multiple times to see the alternating results.

The following variant works reliably but I don't want to go through this in the real world as I already have the GUIDs in the character representation. I would wonder if my function is defective in some way? It has worked well so far.

SELECT Y.COMPANYID, Y.NAME FROM TBL_COMPANY Y
WHERE (Y.ISPRIVATE = 0 OR Y.MANAGEUSERID = CAST(0xC0736208CF126E449AFEF09164628D4D AS BINARY(16)))
AND Y.PARENTCOMPANYID IS NULL ORDER BY Y.NAME ASC;

Suggested fix:
Unknown

Thank you for the bug report. I can't repeat with server built from source server
it returns all the times 22 rows.

OK, if I discover more info on this or I'll post it

This may be an optimzer problem? By adding IGNORE INDEX(TBL_COMPANY_IF1) (FK for PARENTCOMPANYID) to the SELECT, it effectively forces a table scan and it works correctly all the time.

I am not an authority on the subject, but it seems that for a table with 30 rows in it, using an index (especially when I ask for a non-indexed field like NAME) is not cost effective. I realize my solution may impact performance on large tables, but wrong answers are no good either.

i could repeat this problem on 6.0.4 on vista.  every second resultset returned no result and every other returned 22 rows.  it reminds me of bug #37337
if it's not repeatable on latest source, perhaps it's fixed already?

It may indeed be related. I'll try it again with 6.0.5 or later once it comes out, I'm not set up to build from source at the moment

I have retested this on my machine with 6.0.5 and the problem does not occur, it must have been fixed somewhere after 6.0.3