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

error in operator "and" in as clausule where [CORE2164] #2595

Closed
firebird-automations opened this issue Nov 3, 2008 · 12 comments
Closed

error in operator "and" in as clausule where [CORE2164] #2595

firebird-automations opened this issue Nov 3, 2008 · 12 comments

Comments

@firebird-automations
Copy link
Collaborator

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;

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Both your statements return one row to me. What am I doing wrong?

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Yes, now I see the results are inconsistent. I will look at this ticket deeper.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE2277 [ CORE2277 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Seems to be fixed in both v2.5 and v3.0, probably as a result of fixing CORE2277.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@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: @pavel-zotov

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

Fix Version: 2.5.6 [ 10721 ]

Fix Version: 3.0.1 [ 10730 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.5.0 [ 10221 ]

Fix Version: 2.5.6 [ 10721 ] =>

Fix Version: 3.0.1 [ 10730 ] =>

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

No branches or pull requests

2 participants