>>>>> "web" == web <web@stripped> writes:
web> This example, trimmed from the actual DB, shows the problem:
web> CREATE TABLE object
web> (
web> objectID INT UNSIGNED NOT NULL PRIMARY KEY
web> );
web> CREATE TABLE feature
web> (
web> objectID INT UNSIGNED NOT NULL,
web> catID INT UNSIGNED NOT NULL,
web> INDEX catIDIndex ( catID ),
web> UNIQUE objectIDcatIDUnique ( objectID,catID )
web> );
web> CREATE TABLE category
web> (
web> catID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
web> name char(32) NOT NULL,
web> UNIQUE nameUnique (name)
web> );
web> INSERT INTO object (objectID) VALUES (1),(2);
web> INSERT INTO category (catID,name) VALUES (1,"cat1"),(2,"cat2");
web> INSERT INTO feature (objectID,catID) VALUES (1,1),(1,2),(2,1);
<cut>
web> SELECT object.objectID,( SUM(category.name='cat1') AND SUM(category.name='cat2'))
> AS q
web> FROM category,feature,object
web> WHERE category.catID=feature.catID
web> AND object.objectID=feature.objectID
web> AND category.name IN ('cat1','cat2')
web> GROUP BY object.objectID HAVING q>0;
Hi!
I posted a patch earlier today that fixes the SUM() problem for MySQL
3.23.3
I will at the start of next week release 3.23.4 (just to fix this
problem).
Regards,
Monty
