Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
firebird-automations opened this issue Feb 12, 2015 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.4 [ 10585 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA617 [ QA617 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment