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
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