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
error in operator "and" in as clausule where [CORE2164] #2595
Comments
Commented by: @dyemanov Both your statements return one row to me. What am I doing wrong? |
Commented by: @asfernandes Dmitry, for me too. I had a short look at it, and it seems result of both queries are inconsistent. And if you drop the indexes, it will give a different result. |
Commented by: @dyemanov Yes, now I see the results are inconsistent. I will look at this ticket deeper. |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] Fix Version: 2.5.6 [ 10721 ] Fix Version: 3.0.1 [ 10730 ] |
Modified by: @dyemanovFix Version: 2.5.0 [ 10221 ] Fix Version: 2.5.6 [ 10721 ] => Fix Version: 3.0.1 [ 10730 ] => |
Submitted by: Vicente Tejero Trueba (vicentett)
Relate to CORE2277
I have this statement:
select e.nombre,a.cod_anuncio
from empresas e
inner join anuncios a on (a.cod_empresa=e.cod_empresa)
where a.cod_anuncio not in
(select em.cod_anuncio
from emisiones em
inner join medios m on (m.cod_medio=em.cod_medio)
where m.provincia<>e.provincia
)
This statement don't return any rows
If append other subselect, it returns one row ¿why?
The second statement is
select e.nombre,a.cod_anuncio
from empresas e
inner join anuncios a on (a.cod_empresa=e.cod_empresa)
where a.cod_anuncio not in
(select em.cod_anuncio
from emisiones em
inner join medios m on (m.cod_medio=em.cod_medio)
where m.provincia<>e.provincia
)
and ------ fails
a.cod_anuncio in
(select em1.cod_anuncio
from emisiones em1
inner join medios m1 on (m1.cod_medio=em1.cod_medio)
where m1.provincia=e.provincia
)
The database definition is
--------------------- database script -------------------------------
Create Domain DCODEMPRESA As Integer;
Create Domain DCADENA30 As Varchar(30) Default '';
Create Domain DCOD_ANUNCIO As Integer;
Create Domain DCODMEDIO As Integer;
Create Domain DFECHA As Timestamp;
Create Domain DENTERO As Integer;
Create Domain DIMPORTE As Numeric(10,2);
Create Table EMPRESAS (
COD_EMPRESA DCODEMPRESA NOT NULL,
NOMBRE DCADENA30,
PROVINCIA DCADENA30,
Constraint pk_EMPRESAS Primary Key (COD_EMPRESA)
);
Create Table ANUNCIOS (
COD_ANUNCIO DCOD_ANUNCIO NOT NULL,
DESCRIPCION DCADENA30,
COD_EMPRESA DCODEMPRESA NOT NULL,
Constraint pk_ANUNCIOS Primary Key (COD_ANUNCIO)
);
Create Table MEDIOS (
COD_MEDIO DCODMEDIO NOT NULL,
NOMBRE DCADENA30,
PROVINCIA DCADENA30,
Constraint pk_MEDIOS Primary Key (COD_MEDIO)
);
Create Table EMISIONES (
COD_ANUNCIO DCOD_ANUNCIO NOT NULL,
FECHA DFECHA,
VECES DENTERO,
IMPORTE DIMPORTE,
COD_MEDIO DCODMEDIO NOT NULL,
Constraint pk_EMISIONES Primary Key (COD_ANUNCIO,COD_MEDIO)
);
Alter Table ANUNCIOS add Foreign Key (COD_EMPRESA) references EMPRESAS (COD_EMPRESA) on update cascade on delete no action ;
Alter Table EMISIONES add Foreign Key (COD_ANUNCIO) references ANUNCIOS (COD_ANUNCIO) on update cascade on delete no action ;
Alter Table EMISIONES add Foreign Key (COD_MEDIO) references MEDIOS (COD_MEDIO) on update cascade on delete no action ;
and the data script
--------------------- data script --------------------------------------
delete from emisiones;
delete from anuncios;
delete from empresas;
delete from medios;
commit;
insert into empresas (cod_empresa,nombre,provincia)
values (1,'PULEVA','GRANADA');
insert into empresas (cod_empresa,nombre,provincia)
values (2,'COVAP','CORDOBA');
insert into empresas (cod_empresa,nombre,provincia)
values (3,'LA VEGA','MALAGA');
commit;
insert into medios (cod_medio,nombre,provincia)
values (1,'DIARIO SUR','MALAGA');
insert into medios (cod_medio,nombre,provincia)
values (2,'40 GRANADA','GRANADA');
insert into medios (cod_medio,nombre,provincia)
values (3,'EL PAIS','MADRID');
commit;
insert into anuncios (cod_anuncio,descripcion,cod_empresa)
values (1,'la mejor leche',1);
insert into anuncios (cod_anuncio,descripcion,cod_empresa)
values (2,'la dieta mediterránea ',1);
insert into anuncios (cod_anuncio,descripcion,cod_empresa)
values (3,'El valle de los pedroches',2);
commit;
insert into emisiones (cod_anuncio,fecha,veces,importe,cod_medio)
values (1,'10/01/2007',3,25,1);
insert into emisiones (cod_anuncio,fecha,veces,importe,cod_medio)
values (1,'10/11/2007',3,25,3);
insert into emisiones (cod_anuncio,fecha,veces,importe,cod_medio)
values (1,'10/01/2007',4,30,2);
insert into emisiones (cod_anuncio,fecha,veces,importe,cod_medio)
values (3,'11/01/2007',1,45,3);
commit;
The text was updated successfully, but these errors were encountered: