List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 17 1999 9:30pm
Subject:Sum/group by/having error in 3.23.3
View as plain text  
>>>>> "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
Thread
Sum/group by/having error in 3.23.3web16 Sep
  • update and $dbh->quotejice16 Sep
    • Re: update and $dbh->quoteJay J16 Sep
      • Re: update and $dbh->quoteJesse16 Sep
        • Re: update and $dbh->quotejice16 Sep
          • Re: update and $dbh->quoteChris16 Sep
            • Re: update and $dbh->quotejice16 Sep
              • Re: update and $dbh->quoteChris16 Sep
                • Re: update and $dbh->quotejice16 Sep
    • Re: update and $dbh->quoteJay J16 Sep
      • Re: update and $dbh->quotejice16 Sep
  • Sum/group by/having error in 3.23.3Michael Widenius18 Sep