Issue Details (XML | Word | Printable)

Key: CORE-5901
Type: Bug Bug
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Duilio Juan Isola
Votes: 0
Watchers: 6
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Aggregate error when there is none

Created: 23/Aug/18 08:39 AM   Updated: 25/Aug/18 08:35 PM
Component/s: Engine
Affects Version/s: 2.5.8
Fix Version/s: None

Environment: Windows 64bit

QA Status: No test


 Description  « Hide
If I execute this SQL statemente it fails with an "aggregate error".
The problem is in the 2nd to 4th WHEN line. (It trys to know if the fisrt 2 letters corresponds or not to the field CODIGO from table PAIS)
If I replace the IN (SELECT ...) with IN ('ES', 'DE', 'RU', ...) then it works fine but it is not what I want.

SELECT COUNT(*),
   CASE
     WHEN (Iban = '') THEN 1
     WHEN ((Iban <> '') AND (SUBSTRING(Iban FROM 1 FOR 2) IN (SELECT Codigo FROM Pais WHERE Codigo <> 'PD'))) THEN 2
     WHEN ((Iban <> '') AND (SUBSTRING(Iban FROM 1 FOR 2) NOT IN (SELECT Codigo FROM Pais))) THEN 3
     WHEN ((Iban <> '') AND (SUBSTRING(Iban FROM 1 FOR 2) IN (SELECT Codigo FROM Pais WHERE Codigo NOT IN ('ES', 'PD')))) THEN 4
   END AS TipCue
FROM Ls01
WHERE ForPago = :ForPago
GROUP BY 2

can't format message 13:896 -- message file C:\WINDOWS\firebird.msg not found.
Dynamic SQL Error.
SQL error code = -104.
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

This is a test structure where it fails.

set term ^;

CREATE TABLE LS01 (
    IBAN VARCHAR(34),
    FORPAGO VARCHAR(3)
)
^
commit work^

CREATE INDEX LS01_IDX1 ON LS01 (IBAN)
^
commit work^

CREATE INDEX LS01_IDX2 ON LS01 (FORPAGO)
^
commit work^

CREATE TABLE PAIS (
    CODIGO VARCHAR(2) NOT NULL,
    DESCRIPCION VARCHAR(100)
)
^
commit work^

ALTER TABLE PAIS ADD CONSTRAINT PK_PAIS PRIMARY KEY (CODIGO)
^
commit work^

INSERT INTO PAIS (CODIGO, DESCRIPCION) VALUES ('ES', 'Spain')^
INSERT INTO PAIS (CODIGO, DESCRIPCION) VALUES ('DE', 'Germany')^
INSERT INTO PAIS (CODIGO, DESCRIPCION) VALUES ('PD', 'Not a country')^
commit work^

INSERT INTO LS01 (IBAN, FORPAGO) VALUES ('', 'C')^
INSERT INTO LS01 (IBAN, FORPAGO) VALUES ('ES123456', 'C')^
INSERT INTO LS01 (IBAN, FORPAGO) VALUES ('DE456789', 'C')^
INSERT INTO LS01 (IBAN, FORPAGO) VALUES ('PD121212', 'C')^
INSERT INTO LS01 (IBAN, FORPAGO) VALUES ('ZZ989898', 'C')^
commit work^

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Omacht András added a comment - 23/Aug/18 09:04 AM
Hi!

I suggest you use (not) exists instead of (not) in.

select x.TipCue, count(1) from (
SELECT
   CASE
     WHEN (l.Iban = '') THEN 1
     WHEN (l.Iban <> '') and (exists (SELECT 1 FROM Pais p WHERE p.Codigo <> 'PD' and p.Codigo = SUBSTRING(l.Iban FROM 1 FOR 2))) THEN 2
     WHEN (l.Iban <> '') and (not exists (SELECT 1 FROM Pais p where p.Codigo = SUBSTRING(l.Iban FROM 1 FOR 2))) THEN 3
     WHEN (l.Iban <> '') and (exists (SELECT 1 FROM Pais p WHERE p.Codigo NOT IN ('ES', 'PD') and p.Codigo = SUBSTRING(l.Iban FROM 1 FOR 2))) THEN 4
   END AS TipCue
FROM Ls01 l
WHERE l.ForPago = :ForPago) x
group by x.TipCue

András

Duilio Juan Isola added a comment - 23/Aug/18 09:56 AM
Than you András!

I tryed that but it didn´t work either.

SELECT COUNT(*),
   CASE
     WHEN (l.Iban = '') THEN 1
     WHEN (l.Iban <> '') and (exists (SELECT 1 FROM Pais p WHERE p.Codigo <> 'PD' and p.Codigo = SUBSTRING(l.Iban FROM 1 FOR 2))) THEN 2
     WHEN (l.Iban <> '') and (not exists (SELECT 1 FROM Pais p where p.Codigo = SUBSTRING(l.Iban FROM 1 FOR 2))) THEN 3
     WHEN (l.Iban <> '') and (exists (SELECT 1 FROM Pais p WHERE p.Codigo NOT IN ('ES', 'PD') and p.Codigo = SUBSTRING(l.Iban FROM 1 FOR 2))) THEN 4
   END AS TipCue
FROM Ls01 l
WHERE l.ForPago = :ForPago
GROUP BY 2

I solved my particular problem with another workarround, but the problem still exists.

You made a SELECT ... FROM (SELECT ...). It works, but it is a workarround, not the solution or explanation to the problem.

Omacht András added a comment - 23/Aug/18 10:24 AM
Hi!
I know that I made a workaround. The group by problem still exists, I just wanted to show you a solution asap.
András

Rafael Dipold added a comment - 23/Aug/18 11:31 AM
The simple case doesn't work in V3.0.4.32961

SELECT
  COUNT(*),
  CASE
    WHEN (IBAN = '') THEN 1
    WHEN (EXISTS(SELECT 1 FROM PAIS)) THEN 2
  END
FROM LS01
GROUP BY 2

Vlad Khorsun added a comment - 25/Aug/18 03:22 PM
The field "lban" is used in SELECT list (actually, in CASE expression) and it should be "contained in either an aggregate function or the GROUP BY clause"

Karol Bieniaszewski added a comment - 25/Aug/18 08:33 PM
But here is "GROUP BY 2" and 2 is the whole case expression

Karol Bieniaszewski added a comment - 25/Aug/18 08:35 PM
test on system tables

this works

--------------------------------------------------------------------------

SELECT
  COUNT(*),
  CASE
    WHEN (R.RDB$RELATION_ID IS NULL) THEN 1
  END
FROM RDB$DATABASE R
GROUP BY 2


this also works

--------------------------------------------------------------------------
SELECT
  COUNT(*),
  CASE
    WHEN (SELECT 1 FROM RDB$DATABASE R2)=1 THEN 2
  END
FROM RDB$DATABASE R
GROUP BY 2


but merged together not
--------------------------------------------------------------------------
SELECT
  COUNT(*),
  CASE
    WHEN (R.RDB$RELATION_ID IS NULL) THEN 1
    WHEN (SELECT 1 FROM RDB$DATABASE R2)=1 THEN 2
  END
FROM RDB$DATABASE R
GROUP BY 2