Diferents values with equivalent queries. is a bug?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Diferents values with equivalent queries. is a bug?

Cobo Mena, Isaac
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Diferents values with equivalent queries. is a bug?

David Raymond
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:[hidden email]] On Behalf Of Cobo Mena, Isaac
Sent: Thursday, September 06, 2018 6:13 AM
To: [hidden email]
Cc: Gonzalez Maestre, Miguel; [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Diferents values with equivalent queries. is a bug?

Keith Medcalf
In reply to this post by Cobo Mena, Isaac

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-
>[hidden email]] On Behalf Of Cobo Mena, Isaac
>Sent: Thursday, 6 September, 2018 04:13
>To: [hidden email]
>Cc: Gonzalez Maestre, Miguel; [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users