[sqlite] Strange query result

[Maurice van der Stee]

Hello,

I have a sqlite database containing a table config with amongst others
the column conf_flags. I want to select entries from this table which
have one or more specific flags set.

If do

select config_flags from config where config_flags & WANTED_FLAG != 0.

I get the correct result. Now this works if there is only one flag to
test for. With multiple flags this works:

select config_flags from config where (config_flags & (WANTED_FLAG1 |
WANTED_FLAG2)) != 0

but this also returns the rows with only one of the flags set, which is
not what I want.

This returns 0 rows:

select config_flags from config where (config_flags &
WANTED_FLAG) = WANTED_FLAG.

The strange thing is that if I do

select config_flags, config_flags & WANTED_FLAG from config where
(config_flags & WANTED_FLAG) != 0.

This has the correct evaluation in the second result column.

So what am I doing wrong.

===============================
Maurice van der Stee (***@planet.nl)

[Richard Hipp]

Post by Maurice van der Stee
select config_flags from config where (config_flags &
WANTED_FLAG) = WANTED_FLAG.

Can you provide a concrete example - something we can actually run in
the sqlite3 command-line shell?

[Hick Gunter]

Works as expected. Maybe you are not declaring the correct types and end up comparing text to integer (1 is not equal to '1', unless an implicit or explicit cast is involved).

asql> create temp table flags( flag integer );
asql> insert into flags values (0),(1),(2),(3),(4),(5),(6),(7);
rows inserted
-------------
8

asql> select * from flags where flag & 1;
flag
----------
1
3
5
7
asql> select * from flags where flag & 1 == 1;
flag
----------
1
3
5
7
asql> select * from flags where flag & 1 != 0;
flag
----------
1
3
5
7
asql> select * from flags where (flag & 1) != 0;
flag
----------
1
3
5
7
asql> select * from flags where (flag & 1) != 0;
flag
----------
1
3
5
7

asql> select * from flags where flag & (1|2);
flag
----------
1
2
3
5
6
7
asql> select * from flags where flag & (1|2) != 0;
flag
----------
1
2
3
5
6
7
asql> select * from flags where (flag & (1|2)) != 0;
flag
----------
1
2
3
5
6
7

asql> select * from flags where flag & (1|2) == (1|2);
flag
----------
3
7asql> select * from flags where (flag & (1|2)) == (1|2);
flag
----------
3
7

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-***@mailinglists.sqlite.org] Im Auftrag von Maurice van der Stee
Gesendet: Donnerstag, 29. November 2018 13:53
An: sqlite-***@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Strange query result

Hello,

I have a sqlite database containing a table config with amongst others the column conf_flags. I want to select entries from this table which have one or more specific flags set.

If do

select config_flags from config where config_flags & WANTED_FLAG != 0.

I get the correct result. Now this works if there is only one flag to test for. With multiple flags this works:

select config_flags from config where (config_flags & (WANTED_FLAG1 |
WANTED_FLAG2)) != 0

but this also returns the rows with only one of the flags set, which is not what I want.

This returns 0 rows:

select config_flags from config where (config_flags &
WANTED_FLAG) = WANTED_FLAG.

The strange thing is that if I do

select config_flags, config_flags & WANTED_FLAG from config where (config_flags & WANTED_FLAG) != 0.

This has the correct evaluation in the second result column.

So what am I doing wrong.

===============================
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.

[Maurice van der Stee]

Thanks , that explains it. Leanerd something today.

Sorry to reply out of thread, but I am subscribed to the digest, so
haven't yet gotten the original messages.