[sqlite] 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.

[Jay Kreibich]

Post by Maurice van der Stee
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';
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

Post by Maurice van der Stee
While it should only have returned the first row.
--
===============================
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Olivier Mascia]

Post by Maurice van der Stee
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';
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

[Hick Gunter]

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:sqlite-users-***@mailinglists.sqlite.org] Im Auftrag von Maurice van der Stee
Gesendet: Donnerstag, 29. November 2018 14:46
An: sqlite-***@mailinglists.sqlite.org
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 (***@planet.nl)
_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
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) [phone] - 0

May be privileged. May be confidential. Please delete if not the addressee.