MySQL Forums
Forum List  »  Quality Assurance

Not BUG? Depreciated forgiveness for use of wrong quote character?
Posted by: Lawrence Murakami
Date: January 16, 2008 01:04AM

Has anybody noted new problems with misuse of the single quote as an identifier quote character instead of the backtick? I have noted the problem specifically in an alias_name within an order by clause.

An example of the specific change in forgiveness I have noted follows:

Query statement:
select BOOK_NAME as Topic, GUEST_NAME as Name,
count(DATE_TIME) as Count, max(DATE_TIME) as 'Last entry',
'-', min(DATE_TIME) as 'First entry' from guestbook
where DATE_TIME < '2037-05-20 00:00:00'
group by Topic, Name order by 'Last entry'

New unforgiving results (end only) **Note order by is ineffective**
| YS Computer | Zz History | 10 | 2007-02-09 14:37:05 | - | 2003-01-03 16:09:19 |
+---------------+------------------+-----+---------------------+---+---------------------+
1363 rows in set (0.04 sec)

Old forgiving results (end only)
| PC Stuff | LAM2006D1 | 45 | 2008-01-15 02:49:01 | - | 2006-07-06 22:36:02 |
| Company | Auto Service Co. | 3 | 2008-01-15 10:47:55 | - | 2002-10-26 10:26:10 |
+--------------+-------------------+-----+---------------------+---+---------------------+
1364 rows in set (0.02 sec)

In first of the above results the order by clause was ineffective. The resulting order is the same as the group by clause.

Two different environments:
mysql Ver 14.12 Distrib 5.0.45, for mandriva-linux-gnu (i586) using readline 5.2
mysql Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (x86_64) using readline 5.0

This specific query fails in the first (newest) environment.

I know that the query is using the wrong quote character according to current documentation. It is an error or bad practice that I myself am guilty of. I have an explanation of why I have used this bad practice in a specific example (not included here). Following this narrative is an example of the code only partially corrected that till works in the new less forgiving environment.

I want to know if MySQL might want to continue to allow this error of mine to go unreported and produce the desired results. (In a future change that addresses this non-bug).

I will discontinue this programmer error and begin fixing critical places where I have used this error in production.

I have done a bunch of homework before posting this but created too long of an initial problem report and won't post this entire ramble.

I found this while working on replication including testing of a home grown set of web applications and data that includes MySQL data. This is currently affecting a replicated instance of the web application on a PCLinuxOS workstation. I initially noted it in the web application but zeroed in on the error using the mysql command line client on the two host machines. I believe I have it down to a high probability that it is a MySQL change. The actual instances of the error are in the database, in static html source and in perl cgi scripts. I am replicating to dissimilar environments so pinpointing is difficult.

Will this non bug be addressed? Can anybody else replicate this?

I so humbly report this bad coding practice that has crept into my toolkit (I cut and paste a lot!) that I know is wrong and promise to discontinue and yet hope will be forgiven again.

Lawrence A. Murakami
q@lam1.us

P.S. I am working on a world of additional documentation on this issue to be presented upon request.

P.P.S. Please let me know if you adamantly believe I deserve everything I get for my past errors and more which will partially be addressed by your post, email or whatever that doesn't discuss whether this issue can be replicated, is correctly reported, or should or will be addressed.

P.P.P.S. A partially corrected query that works in the new less forgiving environment.

select BOOK_NAME as Topic, GUEST_NAME as Name,
count(DATE_TIME) as Count, max(DATE_TIME) as `Last entry`,
'-', min(DATE_TIME) as 'First entry'
from guestbook
where DATE_TIME < '2037-05-20 00:00:00'
group by Topic, Name order by `Last entry`

| PC Stuff | LAM2006D1 | 43 | 2008-01-14 22:17:11 | - | 2006-07-06 22:36:02 |
+--------------+-------------------+-----+---------------------+---+---------------------+
1363 rows in set (0.03 sec)

Lawrence A. Murakami
q@lam1.us

Options: ReplyQuote


Subject
Views
Written By
Posted
Not BUG? Depreciated forgiveness for use of wrong quote character?
3678
January 16, 2008 01:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.