Issue Details (XML | Word | Printable)

Key: CORE-4690
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Vicente Tejero Trueba
Votes: 0
Watchers: 3
Operations

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

DISTINCT vs non-DISTINCT affects the result of IN subquery

Created: 12/Feb/15 08:54 AM   Updated: 25/Sep/15 01:18 PM
Component/s: None
Affects Version/s: 2.5.0, 2.5.1, 2.5.2, 2.5.2 Update 1, 2.5.3, 2.5.3 Update 1
Fix Version/s: 2.5.4

Environment: Server WI-2.5.3.26778
Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
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;




 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 12/Feb/15 10:15 AM - edited
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?