Here is the two queries re-formatted to be more readable and comparable by
humans ... and they appear as if they should both return the same result ...
However, the declared affinity of the column codcnd is INTEGER in one case and
TEXT in the other. When one is doing an equijoin, one of the affinities is
converted into the other, thus they match and a row is output. However, when
you INTERSECT the datatypes (apparently) must be the same (that is, in the case
of an equality test, the text '1' is the same as the integer 1, but in the case
of the INTERSECT a text '1' is NOT the same as an INTEGER 1, hence there is no
intersection and you get a null result set).
Richard can answer whether this is a "bug" or not, to have the affinities
expressed in one form (join) but not the other (intersect).
You can "fix" by making the affinity of the codcnd column the same everywhere
it occurs in the table definitions ...
pragma foreign_keys = 0;
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 text, -- ****** THIS COLUMN IS TEXT IN THE OTHER TABLES ******
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);
select *
from (
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 *
from (
select explot, entfap, coddto, codcnd, count(coddto)
from dtocom
group by explot, entfap, coddto, codcnd
);
select *
from (
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
)
natural join (
select explot, entfap, coddto, codcnd, count(coddto)
from dtocom
group by explot, entfap, coddto, codcnd
);
And the output of the two queries is ...
QUERY PLAN
`--COMPOUND QUERY
|--LEFT-MOST SUBQUERY
| |--CO-ROUTINE 3
| | |--CO-ROUTINE 2
| | | `--COMPOUND QUERY
| | | |--LEFT-MOST SUBQUERY
| | | | `--SCAN TABLE cndscumplidas
| | | `--UNION ALL
| | | `--SCAN TABLE cndsinhibir
| | |--SCAN SUBQUERY 2
| | `--USE TEMP B-TREE FOR GROUP BY
| `--SCAN SUBQUERY 3
`--INTERSECT USING TEMP B-TREE
|--CO-ROUTINE 5
| `--SCAN TABLE dtocom USING COVERING INDEX sqlite_autoindex_dtocom_1
`--SCAN SUBQUERY 5
111111|EEEEE|123456|1|4
QUERY PLAN
|--MATERIALIZE 3
| |--CO-ROUTINE 2
| | `--COMPOUND QUERY
| | |--LEFT-MOST SUBQUERY
| | | `--SCAN TABLE cndscumplidas
| | `--UNION ALL
| | `--SCAN TABLE cndsinhibir
| |--SCAN SUBQUERY 2
| `--USE TEMP B-TREE FOR GROUP BY
|--MATERIALIZE 4
| `--SCAN TABLE dtocom USING COVERING INDEX sqlite_autoindex_dtocom_1
|--SCAN SUBQUERY 3
`--SEARCH SUBQUERY 4 USING AUTOMATIC COVERING INDEX (count(coddto)=? AND
codcnd=? AND coddto=? AND entfap=? AND explot=?)
111111|EEEEE|123456|1|4
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[email protected]] On Behalf Of Cobo Mena, Isaac
>Sent: Thursday, 6 September, 2018 04:13
>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
