Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Up-cast from SQL drops order information? #2999

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Up-cast from SQL drops order information? #2999

monetdb-team opened this issue Nov 30, 2020 · 0 comments


Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2012-02-07 16:24:20 +0100
From: @swingbit
To: SQL devs <>
Version: 11.11.7 (Jul2012-SP1)
CC: @njnes, @drstmane, @yzchang

Last updated: 2013-01-22 09:29:17 +0100

Comment 16868

Date: 2012-02-07 16:24:20 +0100
From: @swingbit

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.7 (KHTML, like Gecko) Chrome/16.0.912.77 Safari/535.7
Build Identifier:

Take the following SQL:

create table a(i int);
insert into a values (1),(2),(3);

select cast(i as bigint) from a;

and it's explain:

| mal |
| function user.s4_1():void; |
| X_1 := sql.mvc(); |
| X_8:bat[:oid,:int] := sql.bind(X_1,"spinque","a","i",0); |
| X_4:bat[:oid,:oid] := sql.bind_dbat(X_1,"spinque","a",1); |
| X_7 := bat.reverse(X_4); |
| X_10 := algebra.kdifference(X_8,X_7); |
| X_11 := batcalc.lng(X_10); |
| X_12 := sql.resultSet(1,1,X_11); |
| sql.rsColumn(X_12,"spinque.","i","bigint",64,0,X_11); |
| X_17 := io.stdout(); |
| sql.exportResult(X_17,X_12); |
| end s4_1; |

When debugging this query from SQL, I found out that X_10 is sorted on tail, as expected. However, X_11 is not sorted on tail.

Of course I would expect an up-cast not to modify the order (while a down-cast would).

What I find strange is also that trying to repeat the same in mal doesn't seem to change the order:

mal>a :=,:int);
mal>la := batcalc.lng(a);
mal>ra := bat.reverse(a);
mal>rla := bat.reverse(la);
mal>i_ra := bat.isSorted(ra);
mal>i_rla := bat.isSorted(rla);

t h name
int void type

[ 1, 0@0 ]
[ 2, 1@0 ]
[ 3, 2@0 ]
mal>io.print("Ordered on tail",i_ra);
[ "Ordered on tail", true]

t h name
lng void type

[ 1, 0@0 ]
[ 2, 1@0 ]
[ 3, 2@0 ]
mal>io.print("Ordered on tail",i_rla);
[ "Ordered on tail", true]

Reproducible: Always

Steps to Reproduce:

  1. Debug the SQL query above
  2. Inspect X_10 (original int values) and X_11 (lng values)

Actual Results:

X_11 has tsorded=0

Expected Results:

X_11 has tsorded=65

$ mserver5 --version
MonetDB 5 server v11.7.6 (64-bit, 64-bit oids)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved
Visit for further information
Found 35.5GiB available memory, 8 available cpu cores
libpcre: 7.8 2008-09-05 (compiled with 7.8)
openssl: OpenSSL 1.0.0d 8 Feb 2011 (compiled with OpenSSL 1.0.0d-fips 8 Feb 2011)
libxml2: 2.7.7 (compiled with 2.7.7)
Compiled by: (x86_64-unknown-linux-gnu)
Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -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 -Wmissing-include-dirs -Wp,-D_FORTIFY_SOURCE=2
Linking : /usr/bin/ld -m elf_x86_64

Comment 16869

Date: 2012-02-07 16:43:38 +0100
From: @drstmane

Thanks for notifying and reporting.

We'll look into this, although it is technically not a BUG: it is technically correct to have a sorted BAT that is not marked as being sorted.

However, given that MonetDB can and does exploit (and often rely on) known properties like sortedness for performance, we should indeed be more eager on propagating such properties wherever possible...

Did you run your MAL example in the very mserver5 that also ran the SQL example?
(I.e., when running the MAL example, you did not by any chance enable property checking?)

Comment 16870

Date: 2012-02-07 16:48:56 +0100
From: @swingbit

I agree, not technically a bug. But indeed, given how much faster many operators can be on sorted inputs, this information should not be lost whenever possible.

I ran sql and mal examples on the very same mserver5 (even on the same db), simply changing from -sql to -mal in my mclient command. Without stopping mserver5 in between.

Comment 17218

Date: 2012-05-03 22:04:31 +0200
From: @sjoerdmullender

Changeset b51e0d4dae3f made by Sjoerd Mullender in the MonetDB repo, refers to this bug.

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

Changeset description:

Use gdk_calc code for type coercion in MAL.
This should fix bug #2999.

Comment 17689

Date: 2012-08-24 14:56:03 +0200
From: @sjoerdmullender

Jul2012-SP1 has been released.

Comment 18124

Date: 2012-11-27 15:41:33 +0100
From: @yzchang

No need, and also not possible to add a test for this bug

Comment 18382

Date: 2013-01-22 09:29:17 +0100
From: @sjoerdmullender

Oct2012-SP3 has been released.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant