Hi,
is the following a bug or expected behaviour when using NOT IN to filter
decimal columns when the list contains multiple numbers?
*1. Define a table with a decimal column and a bigint column, and put in
some data...*
drop table if exists atable;
create table atable (
adecimal decimal(21,6),
aint bigint);
insert into atable values(22,22), (33,33), (44,44);
*2. Use IN and NOT IN to filter results on the decimal. The result when the
list only has a single entry is as expected...*
SELECT * FROM ATABLE where adecimal in (22);
ADECIMAL
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
AINT
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
22.00000022(1 row, 1 ms)
SELECT * FROM ATABLE where adecimal not in (22);
ADECIMAL
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
AINT
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
33.0000003344.00000044(2 rows, 1 ms)
*3. But it seems wrong when there is more than one number in the list....*
SELECT * FROM ATABLE where adecimal in (22,33);
ADECIMAL
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
AINT
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>(no
rows, 1 ms)
SELECT * FROM ATABLE where adecimal not in (22,33);
ADECIMAL
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
AINT
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
22.0000002233.0000003344.00000044(3 rows, 9 ms)
*4. Cross checking with integers gives the expected result...*
SELECT * FROM ATABLE where aint in (22);
ADECIMAL
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
AINT
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
22.00000022(1 row, 2 ms)
SELECT * FROM ATABLE where aint not in (22);
ADECIMAL
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
AINT
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
33.0000003344.00000044(2 rows, 1 ms)
SELECT * FROM ATABLE where aint in (22,33);
ADECIMAL
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
AINT
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
22.0000002233.00000033(2 rows, 1 ms)
SELECT * FROM ATABLE where aint not in (22,33);
ADECIMAL
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
AINT
<http://192.168.1.85:8082/query.do?jsessionid=479a5e0ac92a5da9e8a5e6ad074ac0cf#>
44.00000044(1 row, 1 ms)
I'm on 1.3.175.
Thanks,
Ian.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
