Date: 2016-04-28 16:26:09 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.21.19 (Jul2015-SP4)
CC: @njnes

Last updated: 2016-06-23 10:24:17 +0200

Comment 22108

Date: 2016-04-28 16:26:09 +0200
From: Martin van Dinther <<martin.van.dinther>>

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

scalar system function sys.length(string) returns wrong value for strings which have spaces at the end. The spaces are valid and significant characters and therefore should be counted also.

If somebody does not want to have to have trailing spaces in a string to be counted they should use length(rtrim(string)) to get the length without the trailing spaces.

Reproducible: Always

Steps to Reproduce:

1. start mserver5 (Jul2015)
2. start mclient
3. run SQL commands:
   SELECT length('123 ') as "four";
   SELECT length('123 ') as "ten";
   SELECT length('1234567890') as "ten";
   SELECT length(' 67890') as "ten";
   SELECT length(reverse(' 67890')) as "ten";

SELECT sys.length('123 ') as "four";
SELECT sys.length('123 ') as "ten";
SELECT sys.length('1234567890') as "ten";
SELECT sys.length(' 67890') as "ten";
SELECT sys.length(reverse(' 67890')) as "ten";

CREATE TABLE tchar (val CHAR(9) NOT NULL);
INSERT INTO tchar VALUES ('A'), (' BC ');
SELECT val, length(val) FROM tchar;
-- returns wrong length for second row
UPDATE tchar SET val = val || ' ';
SELECT val, length(val) FROM tchar;
-- returns wrong length for both rows
UPDATE tchar SET val = (val || 'x');
SELECT val, length(val) FROM tchar;
SELECT val, length(val) FROM tchar WHERE length(val) <> 9;
UPDATE tchar SET val = rpad(val, length(val), ' ') WHERE length(val) <> 9;
SELECT val, length(val) FROM tchar WHERE length(val) = 9;
-- returns wrong length
DROP TABLE tchar;

Actual Results:

bash-4.3$ mclient
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.21.20 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>SELECT length('123 ') as "four";
+------+
| four |
+======+
| 3 |
+------+
1 tuple (0.959ms)
sql>SELECT length('123 ') as "ten";
+------+
| ten |
+======+
| 3 |
+------+
1 tuple (0.510ms)
sql>SELECT length('1234567890') as "ten";
+------+
| ten |
+======+
| 10 |
+------+
1 tuple (0.674ms)
sql>SELECT length(' 67890') as "ten";
+------+
| ten |
+======+
| 10 |
+------+
1 tuple (0.571ms)
sql>SELECT length(reverse(' 67890')) as "ten";
+------+
| ten |
+======+
| 5 |
+------+
1 tuple (0.642ms)
sql>
sql>SELECT sys.length('123 ') as "four";
+------+
| four |
+======+
| 3 |
+------+
1 tuple (0.767ms)
sql>SELECT sys.length('123 ') as "ten";
+------+
| ten |
+======+
| 3 |
+------+
1 tuple (0.587ms)
sql>SELECT sys.length('1234567890') as "ten";
+------+
| ten |
+======+
| 10 |
+------+
1 tuple (0.631ms)
sql>SELECT sys.length(' 67890') as "ten";
+------+
| ten |
+======+
| 10 |
+------+
1 tuple (0.582ms)
sql>SELECT sys.length(reverse(' 67890')) as "ten";
+------+
| ten |
+======+
| 5 |
+------+
1 tuple (0.650ms)
sql>
sql>CREATE TABLE tchar (val CHAR(9) NOT NULL);
operation successful (19.663ms)
sql>INSERT INTO tchar VALUES ('A'), (' BC ');
2 affected rows (7.656ms)
sql>SELECT val, length(val) FROM tchar;
+-----------+------------+
| val | length_val |
+===========+============+
| A | 1 |
| BC | 3 |
+-----------+------------+
2 tuples (2.177ms)
sql>-- returns wrong length for second row
sql>UPDATE tchar SET val = val || ' ';
2 affected rows (4.864ms)
sql>SELECT val, length(val) FROM tchar;
+-----------+------------+
| val | length_val |
+===========+============+
| A | 1 |
| BC | 3 |
+-----------+------------+
2 tuples (0.497ms)
sql>-- returns wrong length for both rows
sql>UPDATE tchar SET val = (val || 'x');
2 affected rows (6.601ms)
sql>SELECT val, length(val) FROM tchar;
+-----------+------------+
| val | length_val |
+===========+============+
| A x | 6 |
| BC x | 9 |
+-----------+------------+
2 tuples (0.549ms)
sql>SELECT val, length(val) FROM tchar WHERE length(val) <> 9;
+-----------+------------+
| val | length_val |
+===========+============+
| A x | 6 |
+-----------+------------+
1 tuple (0.783ms)
sql>UPDATE tchar SET val = rpad(val, length(val), ' ') WHERE length(val) <> 9;
1 affected row (5.077ms)
sql>SELECT val, length(val) FROM tchar WHERE length(val) = 9;
+-----------+------------+
| val | length_val |
+===========+============+
| BC x | 9 |
+-----------+------------+
1 tuple (2.564ms)
sql>-- returns wrong length
sql>DROP TABLE tchar;
operation successful (4.708ms)
sql>

Expected Results:

sql>SELECT length('123 ') as "four";
+------+
| four |
+======+
| 4 |
+------+
1 tuple (0.959ms)
sql>SELECT length('123 ') as "ten";
+------+
| ten |
+======+
| 10 |
+------+
1 tuple (0.510ms)
sql>SELECT length('1234567890') as "ten";
+------+
| ten |
+======+
| 10 |
+------+
1 tuple (0.674ms)
sql>SELECT length(' 67890') as "ten";
+------+
| ten |
+======+
| 10 |
+------+
1 tuple (0.571ms)
sql>SELECT length(reverse(' 67890')) as "ten";
+------+
| ten |
+======+
| 10 |
+------+
1 tuple (0.642ms)
sql>

sql>CREATE TABLE tchar (val CHAR(9) NOT NULL);
operation successful (19.663ms)
sql>INSERT INTO tchar VALUES ('A'), (' BC ');
2 affected rows (7.656ms)
sql>SELECT val, length(val) FROM tchar;
+-----------+------------+
| val | length_val |
+===========+============+
| A | 1 |
| BC | 4 |
+-----------+------------+
2 tuples (2.177ms)
sql>UPDATE tchar SET val = val || ' ';
2 affected rows (4.864ms)
sql>SELECT val, length(val) FROM tchar;
+-----------+------------+
| val | length_val |
+===========+============+
| A | 5 |
| BC | 8 |
+-----------+------------+
2 tuples (0.497ms)
sql>UPDATE tchar SET val = (val || 'x');
2 affected rows (6.601ms)
sql>SELECT val, length(val) FROM tchar;
+-----------+------------+
| val | length_val |
+===========+============+
| A x | 6 |
| BC x | 9 |
+-----------+------------+
2 tuples (0.549ms)
sql>SELECT val, length(val) FROM tchar WHERE length(val) <> 9;
+-----------+------------+
| val | length_val |
+===========+============+
| A x | 6 |
+-----------+------------+
1 tuple (0.783ms)
sql>UPDATE tchar SET val = rpad(val, length(val), ' ') WHERE length(val) <> 9;
1 affected row (5.077ms)
sql>SELECT val, length(val) FROM tchar WHERE length(val) = 9;
+-----------+------------+
| val | length_val |
+===========+============+
| A x | 9 |
| BC x | 9 |
+-----------+------------+
2 tuples (2.564ms)
sql>-- returns wrong length
sql>DROP TABLE tchar;
operation successful (4.708ms)
sql>

Comment 22130

Date: 2016-05-08 19:47:57 +0200
From: MonetDB Mercurial Repository <>

Changeset 3936a187f5e7 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=3936a187f5e7

Changeset description:

properly return full string length (fixes bug #3999)

Comment 22131

Date: 2016-05-08 19:49:23 +0200
From: @njnes

removed the incorrect sql string length function.

Comment 22135

Date: 2016-05-12 12:03:23 +0200
From: MonetDB Mercurial Repository <>

Changeset 4095ba42f46f made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Add test for Bug #3999