#1287 expression not in aggregate or GROUP BY columns

current-release
closed-fixed
1
2013-12-30
2013-03-01
No

Hello,
I have defined 2 tables:
CREATE TABLE rules (PK INTEGER PRIMARY KEY, code varchar(255));
CREATE TABLE results (PK INTEGER PRIMARY KEY, rule INTEGER, fulfilled boolean, FOREIGN KEY (rule) references rules(PK));

When I execute following query:
SELECT
rul1.code AS RULE,
res1.fulfilled AS FULFILLED,
100*count(res1.PK)
/
(
SELECT
count( res2.PK )
FROM results res2 JOIN rules rul2 ON res2.rule = rul2.PK
WHERE ( rul2.PK = rul1.PK )

)
AS AMOUNT
FROM results res1 JOIN rules rul1 ON res1.rule = rul1.PK
GROUP BY rul1.code , res1.fulfilled

I receive error:
java.sql.SQLSyntaxErrorException: expression not in aggregate or GROUP BY columns: (100*( COUNT())/(())

This problem appears only on version 2.2.9, on 2.2.8 it works fine. Also, when I run similar query on mysql it also works correctly.
Maybe this problem is somehow related to changes concerning bug with id 3534936.

Regards,
Adam

Discussion

  • Fred Toussi

    Fred Toussi - 2013-03-01
    • assigned_to: nobody --> fredt
    • priority: 5 --> 1
    • milestone: --> current-release
    • labels: --> engine 2.0
     
  • Fred Toussi

    Fred Toussi - 2013-03-01

    There were some implementation changes in 2.2.9 which result in this.

    But it looks like the new error is correct. Consider the legal query below:

    SELECT
    rul1.code AS RULE,
    res1.fulfilled AS FULFILLED,
    100*count(res1.PK)
    AS AMOUNT
    FROM results res1 JOIN rules rul1 ON res1.rule = rul1.PK
    GROUP BY rul1.code , res1.fulfilled

    Each row of the result will cover several different rul1.PK values. Now which one of these rul1.PK values is supposed to be used in the subquery?

    As an example of what is accepted, the following query uses a deterministic value:

    SELECT
    rul1.code AS RULE,
    res1.fulfilled AS FULFILLED,
    100*count(res1.PK)
    /
    (
    SELECT
    count( res2.PK )
    FROM results res2 JOIN rules rul2 ON res2.rule = rul2.PK
    WHERE ( rul2.PK = MAX(rul1.PK) )
    )
    AS AMOUNT
    FROM results res1 JOIN rules rul1 ON res1.rule = rul1.PK
    GROUP BY rul1.code , res1.fulfilled

    MySQL accepts some invalid (non-deterministic) queires with GROUP BY, which we consider errors.

     
  • Fred Toussi

    Fred Toussi - 2013-05-31
    • status: open --> open-fixed
     
  • Fred Toussi

    Fred Toussi - 2013-12-30
    • status: open-fixed --> closed-fixed
     

Log in to post a comment.