Not equal evaluation does not work

[monetdb-team]

Date: 2012-08-06 16:09:37 +0200
From: @bartscheers
To: SQL devs <>
Version: 11.11.7 (Jul2012-SP1)
CC: @drstmane

Last updated: 2012-09-21 11:51:25 +0200

Comment 17616

Date: 2012-08-06 16:09:37 +0200
From: @bartscheers

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:13.0) Gecko/20100101 Firefox/13.0.1
Build Identifier:

When two numbers from different tables are compared, the <, > and = do work, but the <> doesn't.

Reproducible: Always

Steps to Reproduce:

1.create table t1 (id int,dp int);
2.create table t2 (id int, s int);
3.insert into t1 values (1,1),(2,2),(3,2),(4,2),(5,3),(6,3),(7,1);
4.insert into t2 values (1,1),(1,2),(1,3);
5.select t1.id,t1.dp,t3.cnt from t1,(select count(*) as cnt from t2) t3 where t1.id > 1 and t1.dp <> t3.cnt;

Actual Results:

+------+------+------+
| id | dp | cnt |
+======+======+======+
| 2 | 2 | 3 |
| 3 | 2 | 3 |
| 4 | 2 | 3 |
| 5 | 3 | 3 |
| 6 | 3 | 3 |
| 7 | 1 | 3 |
+------+------+------+
6 tuples

Expected Results:

+------+------+------+
| id | dp | cnt |
+======+======+======+
| 2 | 2 | 3 |
| 3 | 2 | 3 |
| 4 | 2 | 3 |
| 7 | 1 | 3 |
+------+------+------+
4 tuples

Bug still there, if first where-clause is excluded

Comment 17617

Date: 2012-08-06 18:09:14 +0200
From: @drstmane

While also your query should indeed work,
here is an IMHO simpler alternative:

sql>declare t2_cnt bigint; set t2_cnt = (select count(*) from t2);
operation successful (1.453ms)
operation successful (1.483ms)
sql>select t1.id,t1.dp,t2_cnt from t1 where t1.id > 1 and t1.dp <> t2_cnt;
+------+------+--------------+
| id | dp | single_value |
+======+======+==============+
| 2 | 2 | 3 |
| 3 | 2 | 3 |
| 4 | 2 | 3 |
| 7 | 1 | 3 |
+------+------+--------------+
4 tuples (2.050ms)

In fact, an other alternative I could imagine also yields the wrong result:

sql>select t1.id,t1.dp from t1 where t1.id > 1 and t1.dp <> (select count(*) from t2);
+------+------+
| id | dp |
+======+======+
| 2 | 2 |
| 3 | 2 |
| 4 | 2 |
| 5 | 3 |
| 6 | 3 |
| 7 | 1 |
+------+------+
6 tuples (4.260ms)

Comment 17691

Date: 2012-08-24 14:56:05 +0200
From: @sjoerdmullender

Jul2012-SP1 has been released.

Comment 17710

Date: 2012-08-31 12:23:51 +0200
From: @sjoerdmullender

Changeset 58e2e2092b55 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=58e2e2092b55

Changeset description:

Added test for bug #3139.

Comment 17711

Date: 2012-08-31 13:38:38 +0200
From: @sjoerdmullender

Changeset d2962f95c490 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=d2962f95c490

Changeset description:

Fixed a bug in BATantijoin when either side is a singleton BAT.
This fixes bug #3139.