Bug when using NOT IN on decimal columns?

[IanP]

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	AINT
22.000000	22

(1 row, 1 ms)

SELECT * FROM ATABLE where adecimal not in (22);

ADECIMAL	AINT
33.000000	33
44.000000	44

(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	AINT

(no rows, 1 ms)

SELECT * FROM ATABLE where adecimal not in (22,33);

ADECIMAL	AINT
22.000000	22
33.000000	33
44.000000	44

(3 rows, 9 ms)

4. Cross checking with integers gives the expected result...

SELECT * FROM ATABLE where aint in (22);

ADECIMAL	AINT
22.000000	22

(1 row, 2 ms)

SELECT * FROM ATABLE where aint not in (22);

ADECIMAL	AINT
33.000000	33
44.000000	44

(2 rows, 1 ms)


SELECT * FROM ATABLE where aint in (22,33);

ADECIMAL	AINT
22.000000	22
33.000000	33

(2 rows, 1 ms)

SELECT * FROM ATABLE where aint not in (22,33);

ADECIMAL	AINT
44.000000	44

(1 row, 1 ms)

I'm on 1.3.175. 

Thanks,

Ian.

[IanP]

Self-answered: it was a bug in 1.3.175, it works in 1.3.176. 

Thanks,

Ian.