Date: 2012-02-15 22:06:11 +0100
From: @skinkie
To: SQL devs <>
Version: 11.7.5 (Dec2011) [obsolete]
CC: @drstmane

Last updated: 2012-02-29 10:34:25 +0100

Comment 16893

Date: 2012-02-15 22:06:11 +0100
From: @skinkie

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/535.1 (KHTML, like Gecko) Chrome/14.0.835.159 Safari/535.1
Build Identifier:

select bit_and(1, power(2, 1));
TypeException:user.s7_1[7]:'calc.and' undefined in: _12:any := calc.and(_8:dbl, _11:dbl)
39000!program contains errors

I hope this is a low hanging fruit where it would just be one line of mx. I guess it is in batcalc.mx:1145. But given the way the double is stored, it may or may not work. A more graceful error would be a feature request.

I wonder if it is possible to choose the type of the result based on he input. For example, in the example I use to integer types. Why would it be required to choose dbl as result opposed to lng?

power(a,b) {
if a = int and b >= 0
type is lng
else
type is dbl

I understand the implication for a multicolumn power would be to know the min value of column b and the type of column a.

Reproducible: Always

Comment 16895

Date: 2012-02-15 22:26:24 +0100
From: @drstmane

As far as I am concerned, bit-wise AND does not make any sense for non-integer types; even C does not allow it:

$ cat x.c
include <stdlib.h>
include <stdio.h>

int main () {
double a = 1.2;
double b = 3.4;
double c = a & b;
printf("%g %g %g\n",a,b,c);
return 0;
}

$ gcc x.c -o x
x.c: In function ‘main’:
x.c:7:15: error: invalid operands to binary & (have ‘double’ and ‘double’)

I'm not sure, what the SQL standard says, but I would not be surprised, if it also does not allow bit-wise AND on non-integer types. If so, this feature would clearly be invalid.

Comment 16896

Date: 2012-02-15 22:30:56 +0100
From: @drstmane

Likewise the signature for the power() function should be clear from the SQL standard (assuming it is indeed defined in the SQL standard).

Also there, I would not be surprised if power() is (just like pow() in C and MAL) defined on floating point types and returning floating point types. If so, please consider using casts if applicable for your use case.

Comment 16897

Date: 2012-02-15 22:35:42 +0100
From: @skinkie

If so, this feature would clearly be invalid.

Would there be a way to explicitly say to the user that double types are unsupported? From searching through: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt I can't really tell if it should be supported. From the Transact-SQL pages I see this:

http://msdn.microsoft.com/en-us/library/ms174965.aspx

I'll file a feature request for the other datatype.

Comment 16898

Date: 2012-02-15 22:36:27 +0100
From: @skinkie

(In reply to comment 2)

If so, please consider using casts if applicable for your use case.

Yes, exactly. Will do this to workaround this specific case.

Comment 16901

Date: 2012-02-15 22:49:36 +0100
From: @drstmane

If SQL bit_and() is indeed not defined for non-integer types, it should indeed trigger an SQL error message when called with non-integer arguments.

Comment 16907

Date: 2012-02-16 12:56:30 +0100
From: @sjoerdmullender

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

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

Changeset description:

Bit-wise operations don't work on floating point values.
And anyway, there is no implementation for these combinations.
This fixes bug #3013.

Comment 16908

Date: 2012-02-16 14:13:30 +0100
From: @sjoerdmullender

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

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

Changeset description:

Test for bug #3013.

Comment 16909

Date: 2012-02-16 14:15:20 +0100
From: @sjoerdmullender

bit operations on floating point values don't make any sense, so I removed the functions from the SQL catalog. This means that the original query
select bit_and(1, power(2, 1));
suddenly works:

sql>select bit_and(1, power(2, 1));
+----------------------+
| bit_and_single_value |
+======================+
| 0 |
+----------------------+
1 tuple (1.107ms)

Comment 16911

Date: 2012-02-16 14:29:41 +0100
From: @skinkie

Thanks! If there is an antonym of collateral damage it would be very suited here :)