Hi,
I am having this interesting SQL problem with mysql Ver 12.22 Distrib
4.0.16, for apple-darwin7.0 (powerpc). For some reason count(*) does
give the right result, when I replace it with sum(1) it works.
mysql> select term, year, count(*) from course where year = 2001 group
by term, year;
+--------+------+--------------------+
| term | year | count(*) |
+--------+------+--------------------+
| Fall | 2001 | 360287970189639680 |
| Spring | 2001 | 360287970189639680 |
+--------+------+--------------------+
2 rows in set (0.00 sec)
mysql> select term, year, sum(1) from course where year = 2001 group
by term, year;
+--------+------+--------+
| term | year | sum(1) |
+--------+------+--------+
| Fall | 2001 | 5 |
| Spring | 2001 | 5 |
+--------+------+--------+
2 rows in set (0.00 sec)
Here is the schema
CREATE TABLE course (
id int(11) NOT NULL auto_increment,
course varchar(64) NOT NULL default '',
title varchar(128) NOT NULL default '',
term varchar(16) NOT NULL default '',
year int(1) NOT NULL default '0',
instructor int(11) NOT NULL default '0',
description varchar(255) default NULL,
PRIMARY KEY (id),
UNIQUE KEY course (course,term,year,instructor),
KEY instructor (instructor),
KEY term (term),
KEY year (year),
CONSTRAINT `0_46` FOREIGN KEY (`instructor`) REFERENCES `instructor`
(`id`),
CONSTRAINT `0_47` FOREIGN KEY (`term`) REFERENCES `term` (`term`)
) TYPE=InnoDB;
I have a work around, but I would like to know what is going on. Any
advice will be appreciated