Date: 2015-02-03 00:47:20 +0100
From: @yzchang
To: SQL devs <>
Version: 11.19.9 (Oct2014-SP2)
CC: @njnes

Last updated: 2015-05-07 12:37:57 +0200

Comment 20621

Date: 2015-02-03 00:47:20 +0100
From: @yzchang

In the following query, two approached, which essentially does the same computation, are used to update the values of table t2. The 1st approach (see the 1st UPDATE statement) uses a subquery, and produces the wrong result. The 2nd approach (see the 2nd UPDATE statement) first stored the results of the subquery in table t3, and produces the correct result.

CREATE TABLE t1 (id VARCHAR(48), col1 VARCHAR(32), col2 VARCHAR(8), excepted BOOLEAN);
INSERT INTO t1 (id, col1, col2, excepted) VALUES ('12', 'col1.A.99.code', '.03', 'false'), ('12', 'col1.A.99.code', '.02', 'false');

CREATE TABLE t2 (id VARCHAR(48), col3 VARCHAR(32), col2 VARCHAR(8), row int);
INSERT INTO t2 (id, col3, col2, row) VALUES ('12',null,null,null);
UPDATE t2 SET (col3, col2, row) =
(SELECT col1, col2, row
FROM (
SELECT id, col1, col2,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY (col1 LIKE '%.%.99.%') ASC,
col1 ASC, col2 ASC) AS row
FROM t1
WHERE excepted = false
AND col1 LIKE '%.A.%'
) AS t3
WHERE t3.row = 1
AND t2.id= t3.id
);
SELECT * FROM t2;

DROP TABLE t2;
CREATE TABLE t2 (id VARCHAR(48), col3 VARCHAR(32), col2 VARCHAR(8), row int);
INSERT INTO t2 (id, col3, col2, row) VALUES ('12',null,null,null);
CREATE TABLE t3 AS (
SELECT id, col1, col2,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY (col1 LIKE '%.%.99.%') ASC,
col1 ASC,col2 ASC) AS row
FROM t1
WHERE excepted = false AND col1 LIKE '%.A.%'
) WITH DATA;
UPDATE t2 SET (col3, col2, row) = (SELECT col1, col2, row FROM t3 WHERE t2.id= t3.id AND t3.row = 1);
SELECT * FROM t2;

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;

Comment 20622

Date: 2015-02-03 00:51:27 +0100
From: MonetDB Mercurial Repository <>

Changeset c90ba8342e40 made by Jennie Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test and expected output for bug #3663

Comment 20638

Date: 2015-02-08 17:15:27 +0100
From: MonetDB Mercurial Repository <>

Changeset e7f28b96f180 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

fix bug #3663, ie keep the order information in the expression

Comment 20639

Date: 2015-02-08 18:04:44 +0100
From: @njnes

fixed issue with relational optimizer losing the ordering information