DISTINCT vs non-DISTINCT affects the result of IN subquery [CORE4690]

[firebird-issue-importer]

Submitted by: Vicente Tejero Trueba (vicentett)

Is related to QA617

I have this statement

select i.*
from ingredientes i
where i.cod_ingrediente < all
(select cod_pais
from paises
where nombre != 'ESPAÑA')
and i.cod_ingrediente in
(select ci.cod_ingrediente
from COMI_INGR ci
join comidas c on (ci.cod_comida=c.cod_comida)
join paises p on (p.cod_pais=c.cod_pais)
where p.NOMBRE='ESPAÑA'
);

The result is different if i write distinct in the 2 subquery.

select i.*
from ingredientes i
where i.cod_ingrediente < all
(select cod_pais
from paises
where nombre != 'ESPAÑA')
and i.cod_ingrediente in
(select distinct ci.cod_ingrediente
from COMI_INGR ci
join comidas c on (ci.cod_comida=c.cod_comida)
join paises p on (p.cod_pais=c.cod_pais)
where p.NOMBRE='ESPAÑA'
);

The script of database is

/* Definition for the `DCAD30` domain : */

CREATE DOMAIN DCAD30 AS
VARCHAR(30)
Default '';

/* Definition for the `DCODINGREDIENTE` domain : */

CREATE DOMAIN DCODINGREDIENTE AS
INTEGER;

/* Definition for the `DREAL` domain : */

CREATE DOMAIN DREAL AS
DECIMAL(7, 2)
Default 0;

/* Definition for the `DTIPOINGRE` domain : */

CREATE DOMAIN DTIPOINGRE AS
CHAR(1)
Default 'V'
Check (VALUE IN ('V','A','M'));

/* Structure for the `INGREDIENTES` table : */

CREATE TABLE INGREDIENTES (
COD_INGREDIENTE DCODINGREDIENTE NOT NULL,
NOMBRE DCAD30 NOT NULL,
TIPO DTIPOINGRE NOT NULL,
IMP_UNIDAD DREAL NOT NULL);

ALTER TABLE INGREDIENTES ADD CONSTRAINT PK_INGREDIENTES PRIMARY KEY (COD_INGREDIENTE);

/* Definition for the `DCODPAIS` domain : */

CREATE DOMAIN DCODPAIS AS
INTEGER;

/* Definition for the `DENTERO` domain : */

CREATE DOMAIN DENTERO AS
INTEGER
Default 0;

/* Structure for the `PAISES` table : */

CREATE TABLE PAISES (
COD_PAIS DCODPAIS NOT NULL,
NOMBRE DCAD30 NOT NULL,
N_HABITANTES DENTERO NOT NULL);

ALTER TABLE PAISES ADD CONSTRAINT PK_PAISES PRIMARY KEY (COD_PAIS);

/* Definition for the `DCAD50` domain : */

CREATE DOMAIN DCAD50 AS
VARCHAR(50)
Default '';

/* Definition for the `DCODCOMIDA` domain : */

CREATE DOMAIN DCODCOMIDA AS
INTEGER;

/* Definition for the `DFECHA` domain : */

CREATE DOMAIN DFECHA AS
DATE;

/* Structure for the `COMIDAS` table : */

CREATE TABLE COMIDAS (
COD_COMIDA DCODCOMIDA NOT NULL,
COD_PAIS DCODPAIS NOT NULL,
NOMBRE DCAD50 NOT NULL,
FECHA_CREACION DFECHA NOT NULL,
PRECIO DREAL NOT NULL,
FECHA_ULT DFECHA);

ALTER TABLE COMIDAS ADD CONSTRAINT PK_COMIDAS PRIMARY KEY (COD_COMIDA);

ALTER TABLE COMIDAS ADD CONSTRAINT FK_COMIDAS1 FOREIGN KEY (COD_PAIS) REFERENCES PAISES(COD_PAIS) ON UPDATE CASCADE;

/* Definition for the `DCODCOMING` domain : */

CREATE DOMAIN DCODCOMING AS
INTEGER;

/* Structure for the `COMI_INGR` table : */

CREATE TABLE COMI_INGR (
COD_COMI_INGR DCODCOMING NOT NULL,
COD_INGREDIENTE DCODINGREDIENTE NOT NULL,
COD_COMIDA DCODCOMIDA NOT NULL,
N_UNIDADES DREAL NOT NULL);

ALTER TABLE COMI_INGR ADD CONSTRAINT PK_COMI_INGR PRIMARY KEY (COD_COMI_INGR);

ALTER TABLE COMI_INGR ADD CONSTRAINT FK_COMI_INGR1 FOREIGN KEY (COD_INGREDIENTE) REFERENCES INGREDIENTES(COD_INGREDIENTE) ON UPDATE CASCADE;

ALTER TABLE COMI_INGR ADD CONSTRAINT FK_COMI_INGR2 FOREIGN KEY (COD_COMIDA) REFERENCES COMIDAS(COD_COMIDA) ON UPDATE CASCADE;

/* Definition for the `DCODRECETA` domain : */

CREATE DOMAIN DCODRECETA AS
INTEGER;

/* Data for the `INGREDIENTES` table (Records 1 - 5) */

INSERT INTO INGREDIENTES
VALUES (1, 'TOMATE', 'V', 1);

INSERT INTO INGREDIENTES
VALUES (2, 'SAL', 'M', 0.5);

INSERT INTO INGREDIENTES
VALUES (3, 'MAGRO', 'A', 5);

INSERT INTO INGREDIENTES
VALUES (4, 'PIMIENTO VERDE', 'V', 0.8);

INSERT INTO INGREDIENTES
VALUES (5, 'LOMO', 'A', 6.5);

COMMIT WORK;

/* Data for the `PAISES` table (Records 1 - 4) */

INSERT INTO PAISES
VALUES (1, 'ESPAÑA', 48000000);

INSERT INTO PAISES
VALUES (2, 'MARRUECOS', 33000000);

INSERT INTO PAISES
VALUES (3, 'EEUU', 300000000);

INSERT INTO PAISES
VALUES (4, 'FRANCIA', 66000000);

COMMIT WORK;

/* Data for the `COMIDAS` table (Records 1 - 5) */

INSERT INTO COMIDAS
VALUES (4, 1, 'MIGAS', '2014-12-13', 3, NULL);

INSERT INTO COMIDAS
VALUES (5, 3, 'CHULETONES A LA BRASA', '2015-02-01', 10, '2015-02-11');

INSERT INTO COMIDAS
VALUES (1, 1, 'ESTOFADO DE CERDO', '2006-09-24', 7, '2014-11-06');

INSERT INTO COMIDAS
VALUES (2, 1, 'PISTO', '2014-09-12', 4, '2014-12-09');

INSERT INTO COMIDAS
VALUES (3, 2, 'CUSCUS', '2014-09-12', 4.5, '2014-12-02');

COMMIT WORK;

/* Data for the `COMI_INGR` table (Records 1 - 8) */

INSERT INTO COMI_INGR
VALUES (8, 3, 5, 0.01);

INSERT INTO COMI_INGR
VALUES (7, 2, 5, 1);

INSERT INTO COMI_INGR
VALUES (1, 1, 1, 0.25);

INSERT INTO COMI_INGR
VALUES (2, 3, 1, 1);

INSERT INTO COMI_INGR
VALUES (3, 2, 1, 0.05);

INSERT INTO COMI_INGR
VALUES (4, 1, 2, 0.5);

INSERT INTO COMI_INGR
VALUES (5, 4, 2, 0.5);

INSERT INTO COMI_INGR
VALUES (6, 2, 3, 0.01);

COMMIT WORK;

Commits: fbf184a FirebirdSQL/fbt-repository@a757a7a FirebirdSQL/fbt-repository@dbcbda6

[firebird-issue-importer]

Commented by: @dyemanov

The correct result is one row returned (with COD_INGEDIENTE = 1), isn't it? Interesting, v3 produces the same (correct) result for both queries and I don't remember any changes re. IN handling there.

Also, did you test with any prior FB v2.5.x version? I mean, is it a recent regression or an old bug?

[firebird-issue-importer]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-issue-importer]

Modified by: @dyemanov

Version: 2.5.3 [ 10461 ]

Version: 2.5.2 Update 1 [ 10521 ]

Version: 2.5.2 [ 10450 ]

Version: 2.5.1 [ 10333 ]

Version: 2.5.0 [ 10221 ]

summary: Result differents using distinct or no distinct => DISTINCT vs non-DISTINCT affects the result of IN subquery

[firebird-issue-importer]

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.4 [ 10585 ]

[firebird-issue-importer]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

[firebird-issue-importer]

Modified by: @pcisar

Link: This issue is related to QA617 [ QA617 ]