Bug #1315 having clauses don't accept quotes around aliased column name
Submitted: 16 Sep 2003 19:39 Modified: 17 Sep 2003 5:02
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:Ver 11.15 Distrib 3.23.37 OS:sun-solaris2.7 (sparc)
Assigned to: CPU Architecture:Any

[16 Sep 2003 19:39] Baron Schwartz
Description:
Sometimes a having clause does not accept aliased column names with quotes around them.  For example, this statement will return no rows, but generates no errors:

=== Begin Query ===

select 
    a.c_uid as 'Account', 
    c_balance as 'Balance', 
    sum(
        case when c_to_account=a.c_uid then c_amount else 0 end
        )  
    +
    sum(
        case when c_from_account=a.c_uid then -c_amount else 0 end
        ) 
    - c_balance as 'Discrepancy'
from t_account as a, t_transaction as t
where
    c_to_account=a.c_uid
    or c_from_account=a.c_uid 
group by a.c_uid
having 'Discrepancy' <> 0
order by 'Discrepancy', 'Account';

=== End Query ===

The same query, with the having line changed to
having Discrepancy <> 0
will succeed.

How to repeat:
Run the query above.  That query assumes the following table definitions (I would be very happy to provide sample data, even a whole sample database... just email me, bps7j at yahoo dot c o m):

create table t_account (
  c_uid             int unsigned    not null auto_increment,
  c_owner           int unsigned    not null default 1, -- root
  c_creator         int unsigned    not null default 1, -- root
  c_group           int unsigned    not null default 3, -- treasurer
  c_unixperms       char(9)         not null default '111111100', -- nonstandard!
  c_created_date    datetime        not null,
  c_last_modified   timestamp       not null,
  c_status          int unsigned    not null default 1,
  c_description     char(100),                -- Short descr. of the account
  c_balance         decimal(6,2)    not null,
  index (c_status),
  index (c_balance),
  primary key  (c_uid)
) type=MyISAM;

create table t_transaction (
  c_uid             int unsigned    not null auto_increment,
  c_owner           int unsigned    not null default 1, -- root
  c_creator         int unsigned    not null default 1, -- root
  c_group           int unsigned    not null default 3, -- treasurer
  c_unixperms       char(9)         not null default '111110100',
  c_created_date    datetime        not null,
  c_last_modified   timestamp       not null,
  c_status          int unsigned    not null default 1,
  c_category        int unsigned    not null, -- > t_transaction_category
  c_from_account    int unsigned    not null, -- > t_account
  c_to_account      int unsigned    not null, -- > t_account
  c_amount          decimal(6,2)    not null,
  c_relates_to      char(30),       -- table the object is stored in
  c_related_uid     int unsigned    not null, -- foreign key to?
  c_comment         char(255),
  index (c_relates_to),
  index (c_related_uid),
  index (c_from_account),
  index (c_to_account),
  index (c_category),
  primary key  (c_uid)
) type=MyISAM;

Suggested fix:
No suggestions
[17 Sep 2003 5:02] Alexander Keremidarski
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Single quotes and doblequotes are used to denote string literals.

In your example:
having 'Discrepancy' <> 0

means string literal 'Discrepancy' is compared to 0

MySQL allows you to use backticks to quote names like. In other words

'Discrepancy', "Discrepancy" are just string litterals
while
Discrepancy, `Discrepancy` are column name or alias