MySQL Bugs: #34074: SELECT queries used with WHERE can lead to erroneous return of empty result sets

Bug #34074	SELECT queries used with WHERE can lead to erroneous return of empty result sets
Submitted:	26 Jan 2008 3:10	Modified:	29 Jan 2008 13:19
Reporter:	Jasper Maxwell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.41	OS:	Windows (XP Professional Service Pack 2)
Assigned to:		CPU Architecture:	Any
Tags:	empty, erroneous, result, SET, sets

Description:
On a table with many fields, using a SELECT query along with WHERE leads to MySQL erroneously returning an empty result set.

I had set up a table called 'vm_userdb' with 31 different fields (most of them being TEXT fields). I tested the table and the login script by inserting one record, the value I had entered for the field 'Username' was 'XenoPhyre'. When I tried using the login script by sending 'XenoPhyre' as a username, MySQL returned no records. I had populated the table with 2 more records with different values for Username. The same problem occured.

The query used to retrieve the user data record was:

SELECT * FROM `vm_userdb` WHERE Username = 'XenoPhyre'

I tried bringing up the record manually from PhpMyAdmin using the same query. The same problem occured. Strangely, using the same query without the WHERE:

SELECT * FROM `vm_userdb`

causes MySQL to return a non-empty result set, which could be considered a workaround, more or less, to the problem. However, this workaround doesn't allow me to achieve my intended purpose for the login script, which is to bring up user data for only one user based on the Username given on a login form. 

I tried dropping the entire database then rebuilding it entirely. The same problem still occurs when trying to execute a SELECT query along with WHERE.

What is even more bizarre is that when I built a brand new table with only ONE field (Username), then inserting a test record, the problem no longer occurs. MySQL returns the correct result as expected.

How to repeat:
Create a database called PsiDrakePassportSVC.

Create a table called vm_userdb using the following query:

CREATE TABLE vm_userdb(
Username varchar(30),
PasswordHash varchar(30),
EMailAddress text,
UniqueID varchar(30),
YOB varchar(8),
AccountCreationDate varchar(17),
UserLevel bigint(9),
AvatarContent mediumblob,
OriginalMachine text,
AuthorizedMachines text,
Games text,
Medals text,
TotalThumbsUp bigint(9),
TotalThumbsDown bigint(9),
FirstName text,
LastName text,
Gender int(1),
AKA text,
PersonalDescription text,
Location text,
ContactInformation text,
Notebook text,
DownloadSalvage text,
SecretQuestion varchar(150),
SecretAnswer text,
PaymentOptionData text,
StoreCredit bigint(9),
AdminRemarks text,
FreezeReason text,
BanReason text,
FriendsList text)

Then insert a record as a test. Then try to bring up the record with the MySQL query:

SELECT * FROM vm_userdb WHERE Username = 'xxxxxx'

(Where xxxxxx is the value for Username in the test record).

Hopefully you will receive the same outcome as I did: MySQL will return an empty result set.

Try populating the same table with a few more records, each with different values for Username. Then try the query again. The same problem occurs.

Then try using the query:
SELECT * FROM vm_userdb

MySQL will return a non-zero result set, basically every single record in the table.

Now, make a separate table called test_userdb, with the query:
CREATE TABLE vm_userdb(Username varchar(30))

The insert a test record into that table. Try the query again:
SELECT * FROM vm_userdb WHERE Username = 'xxxxxx'

MySQL will return the correct result as expected.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.45 at least, and inform about the results.

Updating to server version 5.1.44 does not resolve the problem; updating to server version 6.0.3 does not resolve the problem either.

Sorry, but I can not repeat the behaviour described. Look:

C:\Program Files\MySQL\MySQL Server 5.0>bin\mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE vm_userdb(
    -> Username varchar(30),
    -> PasswordHash varchar(30),
    -> EMailAddress text,
    -> UniqueID varchar(30),
    -> YOB varchar(8),
    -> AccountCreationDate varchar(17),
    -> UserLevel bigint(9),
    -> AvatarContent mediumblob,
    -> OriginalMachine text,
    -> AuthorizedMachines text,
    -> Games text,
    -> Medals text,
    -> TotalThumbsUp bigint(9),
    -> TotalThumbsDown bigint(9),
    -> FirstName text,
    -> LastName text,
    -> Gender int(1),
    -> AKA text,
    -> PersonalDescription text,
    -> Location text,
    -> ContactInformation text,
    -> Notebook text,
    -> DownloadSalvage text,
    -> SecretQuestion varchar(150),
    -> SecretAnswer text,
    -> PaymentOptionData text,
    -> StoreCredit bigint(9),
    -> AdminRemarks text,
    -> FreezeReason text,
    -> BanReason text,
    -> FriendsList text);
Query OK, 0 rows affected (0.38 sec)

mysql> insert into vm_userdb (Username, EMailAddress, UniqueID) values ('xxxxxx'
, 'xx@a.com', '123');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM vm_userdb WHERE Username = 'xxxxxx'\G
*************************** 1. row ***************************
           Username: xxxxxx
       PasswordHash: NULL
       EMailAddress: xx@a.com
           UniqueID: 123
                YOB: NULL
AccountCreationDate: NULL
          UserLevel: NULL
      AvatarContent: NULL
    OriginalMachine: NULL
 AuthorizedMachines: NULL
              Games: NULL
             Medals: NULL
      TotalThumbsUp: NULL
    TotalThumbsDown: NULL
          FirstName: NULL
           LastName: NULL
             Gender: NULL
                AKA: NULL
PersonalDescription: NULL
           Location: NULL
 ContactInformation: NULL
           Notebook: NULL
    DownloadSalvage: NULL
     SecretQuestion: NULL
       SecretAnswer: NULL
  PaymentOptionData: NULL
        StoreCredit: NULL
       AdminRemarks: NULL
       FreezeReason: NULL
          BanReason: NULL
        FriendsList: NULL
1 row in set (0.00 sec)

If you have a complete, repeatable test case for any recent version (5.0.45, 5.1.22, 6.0.3), please, copy and paste it as a comment, as I did above.

Enter password: *************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 59
Server version: 6.0.3-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE PsiDrakePassportSVC
Database changed
mysql> CREATE TABLE vm_userdb(
    -> Username varchar(30),
    -> PasswordHash varchar(30),
    -> EMailAddress text,
    -> UniqueID varchar(30),
    -> YOB varchar(8),
    -> AccountCreationDate varchar(17),
    -> UserLevel bigint(9),
    -> AvatarContent mediumblob,
    -> OriginalMachine text,
    -> AuthorizedMachines text,
    -> Games text,
    -> Medals text,
    -> TotalThumbsUp bigint(9),
    -> TotalThumbsDown bigint(9),
    -> FirstName text,
    -> LastName text,
    -> Gender int(1),
    -> AKA text,
    -> PersonalDescription text,
    -> Notebook text,
    -> DownloadSalvage text,
    -> SecretQuestion varchar(150),
    -> SecretAnswer text,
    -> PaymentOptionData text,
    -> StoreCredit bigint(9),
    -> AdminRemarks text,
    -> FreezeReason text,
    -> BanReason text,
    -> FriendsList text);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO vm_userdb (Username, EMailAddress, UniqueID) values ('xxxxxx'
, 'xx@a.com', '123');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM vm_userdb WHERE Username = 'xxxxxx';
+----------+--------------+--------------+----------+------+--------------------
-+-----------+---------------+-----------------+--------------------+-------+---
-----+---------------+-----------------+-----------+----------+--------+------+-
--------------------+----------+-----------------+----------------+-------------
-+-------------------+-------------+--------------+--------------+-----------+--
-----------+
| Username | PasswordHash | EMailAddress | UniqueID | YOB  | AccountCreationDate
 | UserLevel | AvatarContent | OriginalMachine | AuthorizedMachines | Games | Me
dals | TotalThumbsUp | TotalThumbsDown | FirstName | LastName | Gender | AKA  |
PersonalDescription | Notebook | DownloadSalvage | SecretQuestion | SecretAnswer
 | PaymentOptionData | StoreCredit | AdminRemarks | FreezeReason | BanReason | F
riendsList |
+----------+--------------+--------------+----------+------+--------------------
-+-----------+---------------+-----------------+--------------------+-------+---
-----+---------------+-----------------+-----------+----------+--------+------+-
--------------------+----------+-----------------+----------------+-------------
-+-------------------+-------------+--------------+--------------+-----------+--
-----------+
| xxxxxx   | NULL         | xx@a.com     | 123      | NULL | NULL
 |      NULL | NULL          | NULL            | NULL               | NULL  | NU
LL   |          NULL |            NULL | NULL      | NULL     |   NULL | NULL |
NULL                | NULL     | NULL            | NULL           | NULL
 | NULL              |        NULL | NULL         | NULL         | NULL      | N
ULL        |
+----------+--------------+--------------+----------+------+--------------------
-+-----------+---------------+-----------------+--------------------+-------+---
-----+---------------+-----------------+-----------+----------+--------+------+-
--------------------+----------+-----------------+----------------+-------------
-+-------------------+-------------+--------------+--------------+-----------+--
-----------+
1 row in set (0.00 sec)

(Sorry about the clutter, that's what Command Prompt spewed out.)

Well it seems that I can't reproduce the behavior either using the command line. Perhaps this might mean a problem with my PHP login script and not with MySQL.

MySQL Bug Report (#34074)

Attachment: mysqlbugreport.txt (text/plain), 9.57 KiB.

The textfile above is an update to my above reply. It was too long to be posted as a comment so I had to put it in a text file.

Many thanks for writing a bug report.

Because you can't repeat your issue too, I'll close the bug report now.

If you still have a problem, please feel free to open it again.