Computer Science Canada

"Features" not bugs?
Author:  Zeroth [ Wed Sep 24, 2008 7:49 pm ]
Post subject:  "Features" not bugs?
So, I've been taking COSC 304, Intro to Databases, and we had a very... interesting experience with MySQL.

Take, for example, this query,
SQL:

SELECT   title, COUNT(eno) AS numEmp,
         MIN(salary) as minSal,
         MAX(salary) as maxSal, AVG(salary) AS avgSal
FROM     emp
GROUP BY title;

(for reference, here is the basic DDL for the tables:
emp (eno, ename, bdate, title, salary, supereno, dno)
proj (pno, pname, budget, dno)
dept (dno, dname, mgreno)
workson (eno, pno, resp, hours))

Now, so, he decided to show us a sample of erroneous selects, and did this:
SQL:

SELECT   ename, COUNT(eno) AS numEmp,
         MIN(salary) as minSal,
         MAX(salary) as maxSal, AVG(salary) AS avgSal
FROM     emp
GROUP BY title;

And MySQL accepted it. It even pulled out names... which would not be unique per group! Funnily enough SQLServer did not accept this input. Even worse, these two queries were both accepted, and returned a column of 5's.
SQL:

SELECT   ename+5, COUNT(eno) AS numEmp,
         MIN(salary) as minSal,
         MAX(salary) as maxSal, AVG(salary) AS avgSal
FROM     emp
GROUP BY title;

SELECT   ename+'5', COUNT(eno) AS numEmp,
         MIN(salary) as minSal,
         MAX(salary) as maxSal, AVG(salary) AS avgSal
FROM     emp
GROUP BY title;


...This was pretty shocking to us, needless to say.

:


You can syndicate this boards posts using the file backend.php or view the topic map using sitemap.php.

Terms of Use | Privacy Policy