Strange query result

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

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 ([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 result

Richard Hipp-3
On 11/29/18, Maurice van der Stee <[hidden email]> wrote:
> This returns 0 rows:
>
> 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?

--
D. Richard Hipp
[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: [EXTERNAL] Strange query result

Hick Gunter
In reply to this post by Maurice van der Stee
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:[hidden email]] Im Auftrag von Maurice van der Stee
Gesendet: Donnerstag, 29. November 2018 13:53
An: [hidden email]
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 ([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