MySQL Bugs: #1195: inconsistent query execution

Bug #1195	inconsistent query execution
Submitted:	4 Sep 2003 7:29	Modified:	16 Oct 2003 7:09
Reporter:	Shree Kumar	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.0	OS:	Sparc Solaris 8
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
This bug is, I feel, somehow related to Bug #534: table names become case sensitive with complex queries.

Description: with complex queries, we have noticed case sensitibility in table names causing problems, but *not* consistently. Sometimes, the query returns error saying the table does not exist. Sometimes, it executes successfully.

Example:
The following query:
---
select distinct deviceinfo.IPAddress, deviceinfo.ObjectId, deviceinfo.HostName, 
                deviceinfo.Site, Devicetype.Name, Devicetype.Vendor, 
                IPLong from deviceinfo 
left OUTER JOIN 
Devicetype on deviceinfo.ObjectId=Devicetype.ObjectId where deviceinfo.objectid in 
  (select distinct deviceinfo.objectid from deviceinfo, templatemap 
          where templatemap.ObjectId=deviceinfo.ObjectId and 
                domaintype='Backup') 
and deviceinfo.site='MySite' 
order by deviceinfo.IPLong
---
when executed, sometimes passes and sometimes fails. When it fails, we
get an error message "Table 'databasename.deviceinfo' does not exist".
Note that the actual name of the table is DeviceInfo. Also note that,
once the query executes successfully, repeated queries always pass.

However, the following query always passes:
----
select distinct deviceinfo.IPAddress, deviceinfo.ObjectId, deviceinfo.HostName, 
                deviceinfo.Site, Devicetype.Name, Devicetype.Vendor, 
                IPLong from deviceinfo 
left OUTER JOIN 
Devicetype on deviceinfo.ObjectId=Devicetype.ObjectId where deviceinfo.objectid in 
  (select distinct DeviceInfo.objectid from DeviceInfo, templatemap 
          where templatemap.ObjectId=DeviceInfo.ObjectId and  
                 domaintype='Backup') 
and deviceinfo.site='MySite' 
order by deviceinfo.IPLong
---
(Note that 'deviceinfo' in the subselect has been changed to 'DeviceInfo')

How to repeat:
Not sure, it is not repeatable reliably.

Hi!

First of all, I would suggest you to upgrade to 4.1.1 when it is available.
We're working on it right now, it has many bug fixes since 4.1.0.

The table names should always be case sensitive, except on Windows,
unless you have lower_case_table_names=ON. Check this with:
show variables like 'lower_case_table_names'; If it is OFF, the
query that sometimes failed for you should always fail, otherwise
it should always work.

Can you make better test case for us, something that we could try
to reproduce? That would need:
- SQL statements for creating the tables
- Probably some data to populate the tables
- Query that fail, even if just sometimes
- Information about how you started mysqld, what options
- mysqladmin variables

Please try to make the test case such that we can repeat all
steps here. Thank you.

Regards,
Jani

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".