I think your problem is that you have different types for codcnd in different
tables. It's text in cndscumplidas and cndsinhibir but int in dtocom.
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table a (foo int);
sqlite> insert into a values (1);
sqlite> create table b (foo text);
sqlite> insert into b values ('1');
sqlite> select * from a intersect select * from b;
sqlite> select * from a natural join b;
foo
1
sqlite> select foo, typeof(foo) from (select foo from a natural join b);
foo|typeof(foo)
1|integer
sqlite> select foo, typeof(foo) from (select foo from b natural join a);
foo|typeof(foo)
1|text
-----Original Message-----
From: sqlite-users [mailto:[email protected]] On
Behalf Of Cobo Mena, Isaac
Sent: Thursday, September 06, 2018 6:13 AM
To: [email protected]
Cc: Gonzalez Maestre, Miguel; [email protected]
Subject: [sqlite] Diferents values with equivalent queries. is a bug?
Hello.
Using the following sql:
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE cndscumplidas
(
explot text,
entfap text,
coddto text,
CODCMP text,
trnid integer,
tipo integer, -- el tipo de condicion. 1- prd, 2- fam, 3 - sec, 4 - pay, 5
- mfd, 6 - combo, 7 - dto aggr
codprd text, -- != NULL si tipo es 1
codfam text, -- != NULL si tipo es 2
codsec text,
cardmean int,
cardtype int,
codagg text,
codcnd text,
invcnd text,
constraint cndscumplidas_fk foreign key (trnid) references trns(trnid) on
delete cascade
);
INSERT INTO cndscumplidas
VALUES('111111','EEEEE','123456','1',1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL);
INSERT INTO cndscumplidas
VALUES('111111','EEEEE','123456','2',1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL);
CREATE TABLE cndsinhibir
(
explot text,
entfap text,
coddto text,
CODCMP text,
trnid integer,
codcnd text,
constraint cndsinhibir_fk foreign key (trnid) references trns(trnid) on
delete cascade
);
INSERT INTO cndsinhibir VALUES('111111','EEEEE','123456','3',1,'1');
INSERT INTO cndsinhibir VALUES('111111','EEEEE','123456','4',1,'1');
CREATE TABLE dtocom (
EXPLOT varchar(6),
ENTFAP varchar(5),
CODDTO varchar(6),
CODCND int,
TIPCMP int,
CODCMP int,
TSLAST date,
TSLAST_T varchar(8),
TSCREA date,
TSCREA_T varchar(8),
CONSTRAINT dtocom_pk PRIMARY KEY (EXPLOT, ENTFAP, CODDTO,CODCND, TIPCMP,
CODCMP)
);
INSERT INTO dtocom VALUES('111111','EEEEE','123456',1,1,1,NULL,NULL,NULL,NULL);
INSERT INTO dtocom VALUES('111111','EEEEE','123456',1,1,2,NULL,NULL,NULL,NULL);
INSERT INTO dtocom VALUES('111111','EEEEE','123456',1,1,3,NULL,NULL,NULL,NULL);
INSERT INTO dtocom VALUES('111111','EEEEE','123456',1,1,4,NULL,NULL,NULL,NULL);
CREATE TABLE trns ( trnid int primary key);
INSERT INTO trns VALUES(1);
COMMIT;
I'm running the follwing selects:
First (no data returns):
select explot, entfap, coddto, codcnd, count(coddto)
from
(
select explot, entfap, coddto, codcnd, codcmp from cndscumplidas where trnid =
1
union all
select explot, entfap, coddto, codcnd, codcmp from cndsinhibir where trnid = 1
)
group by explot, entfap, coddto, codcnd
intersect
select explot, entfap, coddto, codcnd, count(coddto) from dtocom group by
explot, entfap, coddto, codcnd;
Second (with registers):
select * from
(
select explot, entfap, coddto, codcnd, count(coddto) from dtocom group by
explot, entfap, coddto, codcnd
) a natural join (
select explot, entfap, coddto, codcnd, count(coddto)
from
(
select explot, entfap, coddto, codcnd, codcmp from cndscumplidas where trnid =
1
union all
select explot, entfap, coddto, codcnd, codcmp from cndsinhibir where trnid = 1
)
group by explot, entfap, coddto, codcnd) b;
explot entfap coddto codcnd count(coddto)
---------- ---------- ---------- ---------- -------------
111111 EEEEE 123456 1 4
The result is not the same. I think that the queries are equivalent. is it?
Thanks in advance and sorry for inconveniences.
Best Regards.
Isaac Cobo.
________________________________
Este correo electrónico y, en su caso, cualquier fichero anexo al mismo,
contiene información de carácter confidencial exclusivamente dirigida a su
destinatario o destinatarios. Si no es vd. el destinatario indicado, queda
notificado que la lectura, utilización, divulgación y/o copia sin autorización
está prohibida en virtud de la legislación vigente. En el caso de haber
recibido este correo electrónico por error, se ruega notificar inmediatamente
esta circunstancia mediante reenvío a la dirección electrónica del remitente.
Evite imprimir este mensaje si no es estrictamente necesario.
This email and any file attached to it (when applicable) contain(s)
confidential information that is exclusively addressed to its recipient(s). If
you are not the indicated recipient, you are informed that reading, using,
disseminating and/or copying it without authorisation is forbidden in
accordance with the legislation in effect. If you have received this email by
mistake, please immediately notify the sender of the situation by resending it
to their email address.
Avoid printing this message if it is not absolutely necessary.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
