Date: 2011-08-01 18:22:30 +0200
From: matthieu.guamis
To: SQL devs <>
Version: 11.3.7 (Apr2011-SP2) [obsolete]
CC: matthieu.guamis

Last updated: 2011-09-16 15:04:30 +0200

Comment 16064

Date: 2011-08-01 18:22:30 +0200
From: matthieu.guamis

User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fr; rv:1.9.2.18) Gecko/20110628 Ubuntu/10.10 (maverick) Firefox/3.6.18
Build Identifier:

Main query result is "funny" when using a column name as alias of a subquery containing a CASE WHEN. Query returns content of the subquery but when alias name is set to anything else, it works as expected.

Reproducible: Always

Steps to Reproduce:

sql>create table t (id char(1));
operation successful
sql>insert into t values ('a');
1 affected row (5.336ms)
sql>insert into t values ('b');
1 affected row (0.292ms)
sql>insert into t values ('c');
1 affected row (0.275ms)
sql>SELECT CASE WHEN id = 'a' THEN 'x' ELSE 'y' END AS id FROM t;
+-------------------------------------------------------+
| id |
+=======================================================+
| x |
| y |
| y |
+-------------------------------------------------------+
3 tuples (0.522ms)

OK!

sql>select * from ( SELECT CASE WHEN id = 'a' THEN 'x' ELSE 'y' END AS othercolnamealias FROM t) as req;
+-------------------------------------------------------+
| othercolnamealias |
+=======================================================+
| x |
| y |
| y |
+-------------------------------------------------------+
3 tuples (0.550ms)

OK!

sql>select * from ( SELECT CASE WHEN id = 'a' THEN 'x' ELSE 'y' END AS id FROM t) as req;
+------+
| id |
+======+
| a |
| b |
| c |
+------+
3 tuples (0.341ms)

OUPS!

Comment 16071

Date: 2011-08-04 15:31:19 +0200
From: @sjoerdmullender

This seems to have been fixed in the Aug2011 branch.

Comment 16072

Date: 2011-08-04 15:38:22 +0200
From: @sjoerdmullender

Changeset b86cc28166e9 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=b86cc28166e9

Changeset description:

Added test for bug #2848.

Comment 16073

Date: 2011-08-04 15:39:44 +0200
From: @sjoerdmullender

Fixed in the Aug2011 release.

Comment 16233

Date: 2011-09-16 15:04:30 +0200
From: @sjoerdmullender

The Aug2011 version has been released.