OS-dependent behaviour for ilike

[monetdb-team]

Date: 2018-02-20 11:55:50 +0100
From: Manuel <>
To: SQL devs <>
Version: 11.27.11 (Jul2017-SP3)

Last updated: 2018-03-29 15:39:26 +0200

Comment 26225

Date: 2018-02-20 11:55:50 +0100
From: Manuel <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.167 Safari/537.36
Build Identifier:

The following query

select max("A") from "unitTestDontDelete" where "A" ilike ('%' || '' || '%')

on the table

CREATE TABLE "sys"."unitTestDontDelete" (
"A" VARCHAR(255),
"B" BIGINT,
"C" DOUBLE,
"D" TIMESTAMP
);
INSERT INTO "sys"."unitTestDontDelete" VALUES (NULL, NULL, NULL, NULL);
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 0, 0.5, '2013-06-10 11:10:10.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat2', 1, 1.5, '2013-06-11 12:11:11.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 2, 2.5, '2013-06-12 13:12:12.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat2', 3, 3.5, '2013-06-13 14:13:13.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 4, 4.5, '2013-06-14 15:14:14.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat2', 5, 5.5, '2013-06-15 16:15:15.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 6, 6.5, '2013-06-16 17:16:16.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat2', 7, 7.5, '2013-06-17 18:17:17.000000');
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 8, 8.5, '2013-06-18 19:18:18.000000');

yields different results depending on the OS:

On Windows it returns null,
On Linux/Mac it returns 'Cat2'

Reproducible: Always

Steps to Reproduce:

1. Import the table unitTestDontDelete on different OSs
2. Execute the query select max("A") from "unitTestDontDelete" where "A" ilike ('%' || '' || '%')

Actual Results:

OS dependent

Comment 26226

Date: 2018-02-20 13:43:02 +0100
From: MonetDB Mercurial Repository <>

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

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=b70be09e345c

Changeset description:

Fixed implementation of strcasestr for when the library doesn't provide it.
The old implementation had several flaws: if needle is "" it would
return NULL instead of haystack; if haystack is "aaab" and needle is
"aab" it would return NULL instead of a pointer to the second 'a'.

This fixes bug #6547.

Comment 26227

Date: 2018-02-20 13:43:06 +0100
From: MonetDB Mercurial Repository <>

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

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=77f42f1749f8

Changeset description:

Test for bug #6547.

Comment 26328

Date: 2018-03-29 15:39:26 +0200
From: @sjoerdmullender

The Mar2018 version has been released.