Date: 2012-02-15 17:14:02 +0100
From: @skinkie
To: SQL devs <>
Version: 11.7.5 (Dec2011) [obsolete]
CC: @njnes, @drstmane

Last updated: 2012-03-16 14:56:55 +0100

Comment 16890

Date: 2012-02-15 17:14:02 +0100
From: @skinkie

User-Agent: Mozilla/5.0 (X11; Linux i686) AppleWebKit/535.19 (KHTML, like Gecko) Chrome/18.0.1025.11 Safari/535.19
Build Identifier:

sql>trace select 16 and 16;
+------------------+
| and_single_value |
+==================+
| true |
+------------------+
1 tuple (4.755ms)
+-------+----------------------------------------------------------------------------+
| ticks | stmt |
+=======+============================================================================+
| 4 | X_5 := calc.bit(A0=16); |
| 4 | X_6 := calc.bit(A1=16); |
| 4 | X_7 := calc.and(X_5=true,X_6=true); |
| 14 | sql.exportValue(1,".","and_single_value":str,"boolean",1,0,2,X_7=true,""); |
| 5 | end s68_4; |
| 43 | function user.s68_4(A0=16,A1=16); |
| 549 | X_5:void := user.s68_4(16,16); |
+-------+----------------------------------------------------------------------------+
7 tuples (4.835ms)
sql>trace select cast(16 as smallint) and cast(16 as smallint);
+--------------------+
| and_=single_value |
+====================+
| false |
+--------------------+
1 tuple (5.490ms)
+-------+---------------------------------------------------------------------------+
| ticks | stmt |
+=======+===========================================================================+
| 4 | X_5 := calc.==(A0=16:sht,1:sht); |
| 4 | X_6 := calc.==(A1=16:sht,1:sht); |
| 3 | X_7 := calc.and(X_5=false,X_6=false); |
| 15 | sql.exportValue(1,".","and=_single_value","boolean",1,0,2,X_7=false,""); |
| 5 | end s69_4; |
| 120 | function user.s69_4(A0=16:sht,A1=16:sht); |
| 675 | X_5:void := user.s69_4(16:sht,16:sht); |
+-------+---------------------------------------------------------------------------+
7 tuples (5.573ms)

Reproducible: Always

MonetDB 5 server v11.8.0 (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 http://www.monetdb.org/ for further information
Found 3.9GiB available memory, 1 available cpu core
Libraries:
libpcre: 8.20 2011-10-21 (compiled with 8.20)
openssl: OpenSSL 1.0.0e 6 Sep 2011 (compiled with OpenSSL 1.0.0e 6 Sep 2011)
libxml2: 2.7.8 (compiled with 2.7.8)
Compiled by: skinkie@openov_cxx (x86_64-unknown-linux-gnu)
Compilation: gcc -g -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 -Wp,-D_FORTIFY_SOURCE=2
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -m elf_x86_64

Comment 16891

Date: 2012-02-15 17:22:18 +0100
From: @skinkie

It seems it works because of 'luck' and not because it represents the documentation;

http://www.monetdb.org/Documentation/Manuals/MonetDB/Kernel/Modules/Calculator

Comment 16892

Date: 2012-02-15 17:54:57 +0100
From: @skinkie

It seems that the following is the wanted behavior:

sql>trace select bit_and(cast(16 as smallint), cast(16 as smallint)) = cast(16 as smallint);
+----------------------+
| bit_and_single_value |
+======================+
| true |
+----------------------+
1 tuple (6.708ms)
+-------+--------------------------------------------------------------------------------+
| ticks | stmt |
+=======+================================================================================+
| 3 | X_6 := calc.and(A0=16:sht,A1=16:sht); |
| 4 | X_7 := calc.==(X_6=16:sht,A2=16:sht); |
| 14 | sql.exportValue(1,".","bit_and_single_value":str,"boolean",1,0,2,X_7=true,""); |
| 4 | end s20_1; |
| 46 | function user.s20_1(A0=16:sht,A1=16:sht,A2=16:sht); |
| 561 | X_5:void := user.s20_1(16:sht,16:sht,16:sht); |
+-------+--------------------------------------------------------------------------------+
6 tuples (6.792ms)

Comment 16900

Date: 2012-02-15 22:46:29 +0100
From: @drstmane

SQL "and" is boolean not bit-wise; function bit_and(.,.) evaluates a bit-wise and of two arguments:

sql>trace select 16 and 16 ;
+------------------+
| and_single_value |
+==================+
| true |
+------------------+
1 tuple (3.201ms)
+-------+----------------------------------------------------------------------------+
| ticks | stmt |
+=======+============================================================================+
| 3 | X_5 := calc.bit(A0=16); |
| 3 | X_6 := calc.bit(A1=16); |
| 3 | X_7 := calc.and(X_5=true,X_6=true); |
| 8 | sql.exportValue(1,".","and_single_value":str,"boolean",1,0,2,X_7=true,""); |
| 170 | X_5:void := user.s7_1(16,16); |
+-------+----------------------------------------------------------------------------+
5 tuples (3.275ms)

(the given literals "16" are interpreted as / cast to boolean (bit) value "TRUE")
vs.

sql>trace select bit_and(16,16) ;
+----------------------+
| bit_and_single_value |
+======================+
| 16 |
+----------------------+
1 tuple (7.133ms)
+-------+------------------------------------------------------------------------------+
| ticks | stmt |
+=======+==============================================================================+
| 7 | X_5 := calc.and(A0=16,A1=16); |
| 19 | sql.exportValue(1,".","bit_and_single_value":str,"tinyint",8,0,6,X_5=16,""); |
| 276 | X_5:void := user.s6_1(16,16); |
+-------+------------------------------------------------------------------------------+
3 tuples (7.314ms)

(the given literals "16" are interpreted as tinyint)

It remains open, why the the explicit cast behaves as it does:

sql>trace select 16 and cast(16 as int);
+------------------+
| and_single_value |
+==================+
| false |
+------------------+
1 tuple (8.601ms)
+-------+---------------------------------------------------------------------------------+
| ticks | stmt |
+=======+=================================================================================+
| 8 | X_5 := calc.bit(A0=16); |
| 9 | X_6 := calc.==(A1=16,1:int); |
| 7 | X_7 := calc.and(X_5=true,X_6=false); |
| 19 | sql.exportValue(1:int,".","and_single_value","boolean",1:int,0,2,X_7=false,""); |
| 387 | X_5:void := user.s12_1(16,16); |
+-------+---------------------------------------------------------------------------------+
5 tuples (8.737ms)

vs.

sql>trace select bit_and(16,cast(16 as int)) ;
+----------------------+
| bit_and_single_value |
+======================+
| 16 |
+----------------------+
1 tuple (6.594ms)
+-------+---------------------------------------------------------------------------+
| ticks | stmt |
+=======+===========================================================================+
| 3 | X_5 := calc.and(A0=16,A1=16); |
| 9 | sql.exportValue(1,".","bit_and_single_value":str,"int",32,0,6,X_5=16,""); |
| 105 | X_5:void := user.s13_1(16,16); |
+-------+---------------------------------------------------------------------------+
3 tuples (6.642ms)

Comment 16902

Date: 2012-02-15 22:55:08 +0100
From: @skinkie

I was puzzed by the following text;

[bitwise operators]
For integers there are some additional operations. The % operator implements the congruent modulo operation. The << and >> are the left and right bit shift. The or, and, xor and not for integers are implemented as bitwise boolean operations.

It is the first, and only, documentation that comes up when searching for bitwise operations in MonetDB. It is clear to me now, that this text refers to calc.and and not to SQL-'AND'. There is no page on SQL and bit_and, it might help future users either to write a page on SQL functions like these and/or make a reference from the Calculator page to how to use these functions in SQL.

Comment 16910

Date: 2012-02-16 14:25:59 +0100
From: @sjoerdmullender

Bitwise AND in SQL is written &, bitwise OR is written |.
I believe there is no bitwise XOR. It should be ^ but that is acutally power (with the wrong priority, see bug #2291).
The AND and OR (and XOR) operators are Boolean operators.

If there is a bug here, it has to do with conversion of values to Booleans.

Comment 16994

Date: 2012-02-25 18:33:37 +0100
From: @njnes

Changeset aa359ac1eb36 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=aa359ac1eb36

Changeset description:

fixed boolean coerson problem See bug #3012

Comment 17101

Date: 2012-03-16 14:56:55 +0100
From: @grobian

Released in Dec2011-SP2