Date: 2016-06-28 03:12:59 +0200
From: @skinkie
To: SQL devs <>
Version: 11.21.19 (Jul2015-SP4)
CC: @njnes

Last updated: 2016-07-22 09:56:09 +0200

Comment 22215

Date: 2016-06-28 03:12:59 +0200
From: @skinkie

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/49.0.2623.108 Chrome/49.0.2623.108 Safari/537.36
Build Identifier:

CREATE TABLE "sys"."av_20160609" (
"trip_hash" BIGINT,
"userstopcode" CHARACTER LARGE OBJECT,
"vehiclenumber" INTEGER,
"operatingday" DATE,
"trip_id" VARCHAR(16),
"pointorder" SMALLINT,
"passagesequencenumber" SMALLINT,
"targetarrivaltime" VARCHAR(8),
"targetdeparturetime" VARCHAR(8),
"arrivaltime" TIMESTAMP,
"departuretime" TIMESTAMP
);

This table is generated from:

create table av_20160609 as
select * from tt_20160609
left join
(select max(receive) as arrivaltime, trip_hash, userstopcode, vehiclenumber from kv6_20160609 where messagetype = 'ARRIVAL' group by trip_hash, userstopcode, vehiclenumber) as arrival
using (trip_hash, userstopcode)
left join
(select max(receive) as departuretime, trip_hash, userstopcode, vehiclenumber from kv6_20160609 where messagetype = 'DEPARTURE' group by trip_hash, userstopcode, vehiclenumber) as departure
using (trip_hash, userstopcode, vehiclenumber) order by trip_hash, pointorder, vehiclenumber with data;

sql>select arrivaltime, userstopcode, departuretime - arrivaltime from av_20160609 where vehiclenumber is not null limit 10;
inputs not the same size

Moving the arrivaltime to the end of the selection list:

sql>select userstopcode, departuretime - arrivaltime, arrivaltime from av_20160609 where vehiclenumber is not null limit 10;
+--------------+---------+----------------------------+
| userstopcode | L1 | arrivaltime |
+==============+=========+============================+
| 6022 | 155.966 | 2016-06-09 21:43:40.382000 |
| 4014 | 17.936 | 2016-06-09 21:47:52.394000 |
| 4012 | 17.934 | 2016-06-09 21:50:40.407000 |
| 4010 | 14.983 | 2016-06-09 21:52:22.341000 |
| 3443 | 14.983 | 2016-06-09 21:54:46.392000 |
| 3417 | 14.983 | 2016-06-09 21:55:56.406000 |
| 3423 | 14.996 | 2016-06-09 21:56:59.399000 |
| 3425 | 21.003 | 2016-06-09 21:57:50.375000 |
| 3609 | 18.050 | 2016-06-09 21:59:44.354000 |
| 3614 | 33.035 | 2016-06-09 22:00:29.302000 |
+--------------+---------+----------------------------+
10 tuples (10.890ms)

I cannot reproduce the case with the example below.

DROP TABLE "av";
CREATE TABLE "av" (
"userstopcode" TEXT,
"vehiclenumber" INTEGER,
"arrivaltime" TIMESTAMP,
"departuretime" TIMESTAMP
);

INSERT INTO av VALUES ('test', NULL, now(), now());
INSERT INTO av VALUES ('test', 1, now(), now());

Reproducible: Always

MonetDB 5 server v11.24.0 (64-bit, 64-bit oids, 128-bit integers)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 62.8GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.38 2015-11-23 (compiled with 8.38)
openssl: OpenSSL 1.0.2h 3 May 2016 (compiled with OpenSSL 1.0.2h 3 May 2016)
libxml2: 2.9.4 (compiled with 2.9.4)
Compiled by: skinkie@chamechaude (x86_64-pc-linux-gnu)
Compilation: gcc -O3 -pipe -march=broadwell -mabm -mavx256-split-unaligned-load -mavx256-split-unaligned-store -mclflushopt -mrtm -mxsavec -mxsaves -pipe -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -m elf_x86_64

Comment 22246

Date: 2016-07-13 13:50:27 +0200
From: @njnes

fixed in jun2016 and default. The (sub)slice instructions were not pushed down properly.