Strange query results.

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

Strange query results.

Maurice van der Stee
This reproduces the issue for me:

create table config (config_package integer, config_flags integer);
insert into config (config_package, config_flags) values (1, 2);
insert into config (config_package, config_flags) values (2, 4);
insert into config (config_package, config_flags) values (3, 6);
select config_package, config_flags, (config_flags & '4') from config
where (config_flags & '4') != '4';

This produces:

1|2|0
2|4|4
3|6|4

While it should only have returned the first row.
--
===============================
Maurice van der Stee ([hidden email])
_______________________________________________
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: Strange query results.

Jay Kreibich

> On Nov 29, 2018, at 7:45 AM, Maurice van der Stee <[hidden email]> wrote:
>
> This reproduces the issue for me:
>
> create table config (config_package integer, config_flags integer);
> insert into config (config_package, config_flags) values (1, 2);
> insert into config (config_package, config_flags) values (2, 4);
> insert into config (config_package, config_flags) values (3, 6);
> select config_package, config_flags, (config_flags & '4') from config
> where (config_flags & '4') != '4';
>
> This produces:
>
> 1|2|0
> 2|4|4
> 3|6|4

You’re putting single-quotes around the 4, so it is a one character string, not a number.

In the case of “ config_flags & ‘4’ ”, the “&” operator only accepts numbers, so the string is converted to a number.

In the case of “ <number> != ‘4’ “, the equality operator is checking to see if the number 4 is equal to the string ‘4’.  They are not.  != already returns true.

You can fix this by changing all instances of “ ‘4’ “ to just “4”.

  -j



> While it should only have returned the first row.
> --
> ===============================
> Maurice van der Stee ([hidden email])
> _______________________________________________
> 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: Strange query results.

Olivier Mascia
In reply to this post by Maurice van der Stee
> Le 29 nov. 2018 à 14:45, Maurice van der Stee <[hidden email]> a écrit :
>
> This reproduces the issue for me:
>
> create table config (config_package integer, config_flags integer);
> insert into config (config_package, config_flags) values (1, 2);
> insert into config (config_package, config_flags) values (2, 4);
> insert into config (config_package, config_flags) values (3, 6);
> select config_package, config_flags, (config_flags & '4') from config
> where (config_flags & '4') != '4';
> This produces:
>
> 1|2|0
> 2|4|4
> 3|6|4
>
> While it should only have returned the first row.

Just like this?

select config_package, config_flags, (config_flags & 4) from config
where (config_flags & 4) != 4;

1|2|0

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


_______________________________________________
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: [EXTERNAL] Strange query results.

Hick Gunter
In reply to this post by Maurice van der Stee
Just as expected. You are comparing INTEGER to TEXT values. '4' is a text value, not an integer.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Maurice van der Stee
Gesendet: Donnerstag, 29. November 2018 14:46
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Strange query results.

This reproduces the issue for me:

create table config (config_package integer, config_flags integer); insert into config (config_package, config_flags) values (1, 2); insert into config (config_package, config_flags) values (2, 4); insert into config (config_package, config_flags) values (3, 6); select config_package, config_flags, (config_flags & '4') from config where (config_flags & '4') != '4';

This produces:

1|2|0
2|4|4
3|6|4

While it should only have returned the first row.
--
===============================
Maurice van der Stee ([hidden email])
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users