How do I get SQL decimal scale in MAL?
HI,
I"m working on writing an aggregate in in C that is a MAL function. I've run into a problem with decimal types.
I've noticed that when I inspect the BAT inputs for various types, they are coerced to simpler types. (flt,dbl -> dbl). (int, decimal -> int). There is also no other information in the BAT structure to see the type.
I notice that a SQL select gets the type info passed in as parameters for the 'exportValue' output.
For example:
- sql.exportValue(1,".","second_single_value","decimal",9,3,8,_4,"");
*
How can I get this type information ('decimal', etc.) passed into my function?
Dru
Hi Dru,
the decimal scale information is only maintained in the SQL catalog and given that decimals are stored as integers in MonetDB, neither the MonetDB kernel, nor MAL usually need to know about decimals or their scale.
Hence, the question is, whether/why your aggregate functions would need to know about decimals or their scale?
Best, Stefan
Dru Nelson drudru@gmail.com wrote:
HI,
I"m working on writing an aggregate in in C that is a MAL function. I've run into a problem with decimal types.
I've noticed that when I inspect the BAT inputs for various types, they are coerced to simpler types. (flt,dbl -> dbl). (int, decimal -> int). There is also no other information in the BAT structure to see the type.
I notice that a SQL select gets the type info passed in as parameters for the 'exportValue' output.
For example:
- sql.exportValue(1,".","second_single_value","decimal",9,3,8,_4,"");
How can I get this type information ('decimal', etc.) passed into my function?
Dru
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
I second that! Are you sure that you cannot come up with an alternative algorithm for your aggregation that does not need this information (and possibly be faster) ?
On Sat, Nov 9, 2013 at 11:26 AM, Stefan Manegold Stefan.Manegold@cwi.nl wrote:
Hi Dru,
the decimal scale information is only maintained in the SQL catalog and given that decimals are stored as integers in MonetDB, neither the MonetDB kernel, nor MAL usually need to know about decimals or their scale.
Hence, the question is, whether/why your aggregate functions would need to know about decimals or their scale?
Best, Stefan
Dru Nelson drudru@gmail.com wrote:
HI,
I"m working on writing an aggregate in in C that is a MAL function. I've run into a problem with decimal types.
I've noticed that when I inspect the BAT inputs for various types, they are coerced to simpler types. (flt,dbl -> dbl). (int, decimal -> int). There is also no other information in the BAT structure to see the type.
I notice that a SQL select gets the type info passed in as parameters for the 'exportValue' output.
For example:
sql.exportValue(1,".","second_single_value","decimal",9,3,8,_4,"");
How can I get this type information ('decimal', etc.) passed into my function?
Dru
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- | Stefan.Manegold@CWI.nl | Database Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
Hi,
While thinking about this in the morning yesterday, I came up with a solution. First let me explain a bit more about the challenge.
I was implementing percentile_cont for a contract project. I added the syntax to the parser. On the backend I was using dense_rank as my initial template. Because dense_rank takes ANY_1, it would receive either a flt or int BAT. I thought i had everything working great until i worked with a decimal column. Because the decimal type is just an int type scaled, and the BAT doesn't record this, I was stuck. The window function generates a result based on the values (vs dense_rank just needing to know ordinality)
My solution was to modify the AST after the parse to inject a CAST to a dbl on the OVER portion. It was not my ideal solution, since it will force an intermediate to be created for any type that isn't a double. None the less, the MAL optimizers work correctly and the function handles all types.
Best,
Dru
On Sat, Nov 9, 2013 at 8:22 AM, Lefteris lsidir@gmail.com wrote:
I second that! Are you sure that you cannot come up with an alternative algorithm for your aggregation that does not need this information (and possibly be faster) ?
On Sat, Nov 9, 2013 at 11:26 AM, Stefan Manegold Stefan.Manegold@cwi.nl wrote:
Hi Dru,
the decimal scale information is only maintained in the SQL catalog and given that decimals are stored as integers in MonetDB, neither the
MonetDB
kernel, nor MAL usually need to know about decimals or their scale.
Hence, the question is, whether/why your aggregate functions would need
to
know about decimals or their scale?
Best, Stefan
Dru Nelson drudru@gmail.com wrote:
HI,
I"m working on writing an aggregate in in C that is a MAL function. I've run into a problem with decimal types.
I've noticed that when I inspect the BAT inputs for various types, they are coerced to simpler types. (flt,dbl -> dbl). (int, decimal -> int). There is also no other information in the BAT structure to see the type.
I notice that a SQL select gets the type info passed in as parameters
for
the 'exportValue' output.
For example:
sql.exportValue(1,".","second_single_value","decimal",9,3,8,_4,"");
How can I get this type information ('decimal', etc.) passed into my function?
Dru
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- | Stefan.Manegold@CWI.nl | Database Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
Hi Dru,
please be aware that decimal are essential integers --- just the decimal point is shifted; however, it's shifted identically for all values in a BAT; i.e., if your decimal(9,4) column holds, say, values between 1.1 and 6789.1234, the underlying BAT holds integer values between 11000 and 67891234; thus, both ordinality and relative ratios are maintained.
Best, Stefan
----- Original Message -----
Hi,
While thinking about this in the morning yesterday, I came up with a solution. First let me explain a bit more about the challenge.
I was implementing percentile_cont for a contract project. I added the syntax to the parser. On the backend I was using dense_rank as my initial template. Because dense_rank takes ANY_1, it would receive either a flt or int BAT. I thought i had everything working great until i worked with a decimal column. Because the decimal type is just an int type scaled, and the BAT doesn't record this, I was stuck. The window function generates a result based on the values (vs dense_rank just needing to know ordinality)
My solution was to modify the AST after the parse to inject a CAST to a dbl on the OVER portion. It was not my ideal solution, since it will force an intermediate to be created for any type that isn't a double. None the less, the MAL optimizers work correctly and the function handles all types.
Best,
Dru
On Sat, Nov 9, 2013 at 8:22 AM, Lefteris < lsidir@gmail.com > wrote:
I second that! Are you sure that you cannot come up with an alternative algorithm for your aggregation that does not need this information (and possibly be faster) ?
On Sat, Nov 9, 2013 at 11:26 AM, Stefan Manegold < Stefan.Manegold@cwi.nl > wrote:
Hi Dru,
the decimal scale information is only maintained in the SQL catalog and given that decimals are stored as integers in MonetDB, neither the MonetDB kernel, nor MAL usually need to know about decimals or their scale.
Hence, the question is, whether/why your aggregate functions would need to know about decimals or their scale?
Best, Stefan
Dru Nelson < drudru@gmail.com > wrote:
HI,
I"m working on writing an aggregate in in C that is a MAL function. I've run into a problem with decimal types.
I've noticed that when I inspect the BAT inputs for various types, they are coerced to simpler types. (flt,dbl -> dbl). (int, decimal -> int). There is also no other information in the BAT structure to see the type.
I notice that a SQL select gets the type info passed in as parameters for the 'exportValue' output.
For example:
sql.exportValue(1,".","second_single_value","decimal",9,3,8,_4,"");
How can I get this type information ('decimal', etc.) passed into my function?
Dru
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- | Stefan.Manegold@CWI.nl | Database Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
Hi Stefan,
Thanks.
I don't use the int type anymore. MAL recognizes the CAST and coverts the OVER clauses to dbl.
Also, at the MAL implementation level, in C, this scale info was not present. I think that is fine. I recognize that SQL was added to the engine and that BAT structures are essentially a different system.
Best,
Dru
On Sun, Nov 10, 2013 at 11:12 PM, Stefan Manegold Stefan.Manegold@cwi.nlwrote:
Hi Dru,
please be aware that decimal are essential integers --- just the decimal point is shifted; however, it's shifted identically for all values in a BAT; i.e., if your decimal(9,4) column holds, say, values between 1.1 and 6789.1234, the underlying BAT holds integer values between 11000 and 67891234; thus, both ordinality and relative ratios are maintained.
Best, Stefan
----- Original Message -----
Hi,
While thinking about this in the morning yesterday, I came up with a solution. First let me explain a bit more about the challenge.
I was implementing percentile_cont for a contract project. I added the
syntax
to the parser. On the backend I was using dense_rank as my initial
template.
Because dense_rank takes ANY_1, it would receive either a flt or int
BAT. I
thought i had everything working great until i worked with a decimal
column.
Because the decimal type is just an int type scaled, and the BAT doesn't record this, I was stuck. The window function generates a result based on the values (vs dense_rank just needing to know ordinality)
My solution was to modify the AST after the parse to inject a CAST to a
dbl
on the OVER portion. It was not my ideal solution, since it will force an intermediate to be created for any type that isn't a double. None the
less,
the MAL optimizers work correctly and the function handles all types.
Best,
Dru
On Sat, Nov 9, 2013 at 8:22 AM, Lefteris < lsidir@gmail.com > wrote:
I second that! Are you sure that you cannot come up with an alternative algorithm for your aggregation that does not need this information (and possibly be faster) ?
On Sat, Nov 9, 2013 at 11:26 AM, Stefan Manegold <
Stefan.Manegold@cwi.nl >
wrote:
Hi Dru,
the decimal scale information is only maintained in the SQL catalog and given that decimals are stored as integers in MonetDB, neither the
MonetDB
kernel, nor MAL usually need to know about decimals or their scale.
Hence, the question is, whether/why your aggregate functions would
need to
know about decimals or their scale?
Best, Stefan
Dru Nelson < drudru@gmail.com > wrote:
HI,
I"m working on writing an aggregate in in C that is a MAL function.
I've
run into a problem with decimal types.
I've noticed that when I inspect the BAT inputs for various types,
they
are coerced to simpler types. (flt,dbl -> dbl). (int, decimal -> int). There is also
no
other information in the BAT structure to see the type.
I notice that a SQL select gets the type info passed in as parameters
for
the 'exportValue' output.
For example:
sql.exportValue(1,".","second_single_value","decimal",9,3,8,_4,"");
How can I get this type information ('decimal', etc.) passed into my function?
Dru
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- | Stefan.Manegold@CWI.nl | Database Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
Hi Dru,
it's good to know that your solution works for you ;-)
My point was just for the records to explain the intentions and specifics of our decimal implementation. In fact, in this line, we never had the need to know the scale information in MAL; at the end, the position of the decimal point is only relevant to the human user; the MonetDB kernel and the CPU do not (need to) know about any related semantics.
Best, Stefan
----- Original Message -----
Hi Stefan,
Thanks.
I don't use the int type anymore. MAL recognizes the CAST and coverts the OVER clauses to dbl.
Also, at the MAL implementation level, in C, this scale info was not present. I think that is fine. I recognize that SQL was added to the engine and that BAT structures are essentially a different system.
Best,
Dru
On Sun, Nov 10, 2013 at 11:12 PM, Stefan Manegold < Stefan.Manegold@cwi.nl > wrote:
Hi Dru,
please be aware that decimal are essential integers --- just the decimal point is shifted; however, it's shifted identically for all values in a BAT; i.e., if your decimal(9,4) column holds, say, values between 1.1 and 6789.1234, the underlying BAT holds integer values between 11000 and 67891234; thus, both ordinality and relative ratios are maintained.
Best, Stefan
----- Original Message -----
Hi,
While thinking about this in the morning yesterday, I came up with a solution. First let me explain a bit more about the challenge.
I was implementing percentile_cont for a contract project. I added the syntax to the parser. On the backend I was using dense_rank as my initial template. Because dense_rank takes ANY_1, it would receive either a flt or int BAT. I thought i had everything working great until i worked with a decimal column. Because the decimal type is just an int type scaled, and the BAT doesn't record this, I was stuck. The window function generates a result based on the values (vs dense_rank just needing to know ordinality)
My solution was to modify the AST after the parse to inject a CAST to a dbl on the OVER portion. It was not my ideal solution, since it will force an intermediate to be created for any type that isn't a double. None the less, the MAL optimizers work correctly and the function handles all types.
Best,
Dru
On Sat, Nov 9, 2013 at 8:22 AM, Lefteris < lsidir@gmail.com > wrote:
I second that! Are you sure that you cannot come up with an alternative algorithm for your aggregation that does not need this information (and possibly be faster) ?
On Sat, Nov 9, 2013 at 11:26 AM, Stefan Manegold < Stefan.Manegold@cwi.nl > wrote:
Hi Dru,
the decimal scale information is only maintained in the SQL catalog and given that decimals are stored as integers in MonetDB, neither the MonetDB kernel, nor MAL usually need to know about decimals or their scale.
Hence, the question is, whether/why your aggregate functions would need to know about decimals or their scale?
Best, Stefan
Dru Nelson < drudru@gmail.com > wrote:
HI,
I"m working on writing an aggregate in in C that is a MAL function. I've run into a problem with decimal types.
I've noticed that when I inspect the BAT inputs for various types, they are coerced to simpler types. (flt,dbl -> dbl). (int, decimal -> int). There is also no other information in the BAT structure to see the type.
I notice that a SQL select gets the type info passed in as parameters for the 'exportValue' output.
For example:
sql.exportValue(1,".","second_single_value","decimal",9,3,8,_4,"");
How can I get this type information ('decimal', etc.) passed into my function?
Dru
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- | Stefan.Manegold@CWI.nl | Database Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
Hi Stefan, yep, makes sense. I think monetdb is a clean architecture designed for performance. Thanks for your responses. Dru
On Mon, Nov 11, 2013 at 12:06 AM, Stefan Manegold Stefan.Manegold@cwi.nlwrote:
Hi Dru,
it's good to know that your solution works for you ;-)
My point was just for the records to explain the intentions and specifics of our decimal implementation. In fact, in this line, we never had the need to know the scale information in MAL; at the end, the position of the decimal point is only relevant to the human user; the MonetDB kernel and the CPU do not (need to) know about any related semantics.
Best, Stefan
----- Original Message -----
Hi Stefan,
Thanks.
I don't use the int type anymore. MAL recognizes the CAST and coverts the OVER clauses to dbl.
Also, at the MAL implementation level, in C, this scale info was not
present.
I think that is fine. I recognize that SQL was added to the engine and
that
BAT structures are essentially a different system.
Best,
Dru
On Sun, Nov 10, 2013 at 11:12 PM, Stefan Manegold <
Stefan.Manegold@cwi.nl >
wrote:
Hi Dru,
please be aware that decimal are essential integers --- just the decimal point is shifted; however, it's shifted identically for all values in a BAT; i.e., if your decimal(9,4) column holds, say, values between 1.1 and 6789.1234, the underlying BAT holds integer values between 11000 and 67891234; thus, both ordinality and relative ratios are
maintained.
Best, Stefan
----- Original Message -----
Hi,
While thinking about this in the morning yesterday, I came up with a solution. First let me explain a bit more about the challenge.
I was implementing percentile_cont for a contract project. I added the syntax to the parser. On the backend I was using dense_rank as my initial template. Because dense_rank takes ANY_1, it would receive either a flt or int
BAT. I
thought i had everything working great until i worked with a decimal column. Because the decimal type is just an int type scaled, and the BAT
doesn't
record this, I was stuck. The window function generates a result based
on
the values (vs dense_rank just needing to know ordinality)
My solution was to modify the AST after the parse to inject a CAST to
a dbl
on the OVER portion. It was not my ideal solution, since it will force
an
intermediate to be created for any type that isn't a double. None the
less,
the MAL optimizers work correctly and the function handles all types.
Best,
Dru
On Sat, Nov 9, 2013 at 8:22 AM, Lefteris < lsidir@gmail.com > wrote:
I second that! Are you sure that you cannot come up with an alternative algorithm for your aggregation that does not need this information (and possibly be faster) ?
On Sat, Nov 9, 2013 at 11:26 AM, Stefan Manegold <
Stefan.Manegold@cwi.nl >
wrote:
Hi Dru,
the decimal scale information is only maintained in the SQL catalog
and
given that decimals are stored as integers in MonetDB, neither the MonetDB kernel, nor MAL usually need to know about decimals or their scale.
Hence, the question is, whether/why your aggregate functions would
need
to know about decimals or their scale?
Best, Stefan
Dru Nelson < drudru@gmail.com > wrote:
HI,
I"m working on writing an aggregate in in C that is a MAL function.
I've
run into a problem with decimal types.
I've noticed that when I inspect the BAT inputs for various types,
they
are coerced to simpler types. (flt,dbl -> dbl). (int, decimal -> int). There is
also no
other information in the BAT structure to see the type.
I notice that a SQL select gets the type info passed in as
parameters
for the 'exportValue' output.
For example:
sql.exportValue(1,".","second_single_value","decimal",9,3,8,_4,"");
How can I get this type information ('decimal', etc.) passed into my function?
Dru
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- | Stefan.Manegold@CWI.nl | Database Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
Hi Dru,
your solution works but it adds one more expensive operation and that will make it slower, especially when data is big. As I mentioned earlier, another solution would be to create an implementation that is oblivious of the "display differences" between an int and a decimal.
On Mon, Nov 11, 2013 at 8:01 AM, Dru Nelson drudru@gmail.com wrote:
Hi,
While thinking about this in the morning yesterday, I came up with a solution. First let me explain a bit more about the challenge.
I was implementing percentile_cont for a contract project. I added the syntax to the parser. On the backend I was using dense_rank as my initial template. Because dense_rank takes ANY_1, it would receive either a flt or int BAT. I thought i had everything working great until i worked with a decimal column. Because the decimal type is just an int type scaled, and the BAT doesn't record this, I was stuck. The window function generates a result based on the values (vs dense_rank just needing to know ordinality)
My solution was to modify the AST after the parse to inject a CAST to a dbl on the OVER portion. It was not my ideal solution, since it will force an intermediate to be created for any type that isn't a double. None the less, the MAL optimizers work correctly and the function handles all types.
Best,
Dru
On Sat, Nov 9, 2013 at 8:22 AM, Lefteris lsidir@gmail.com wrote:
I second that! Are you sure that you cannot come up with an alternative algorithm for your aggregation that does not need this information (and possibly be faster) ?
On Sat, Nov 9, 2013 at 11:26 AM, Stefan Manegold Stefan.Manegold@cwi.nl wrote:
Hi Dru,
the decimal scale information is only maintained in the SQL catalog and given that decimals are stored as integers in MonetDB, neither the MonetDB kernel, nor MAL usually need to know about decimals or their scale.
Hence, the question is, whether/why your aggregate functions would need to know about decimals or their scale?
Best, Stefan
Dru Nelson drudru@gmail.com wrote:
HI,
I"m working on writing an aggregate in in C that is a MAL function. I've run into a problem with decimal types.
I've noticed that when I inspect the BAT inputs for various types, they are coerced to simpler types. (flt,dbl -> dbl). (int, decimal -> int). There is also no other information in the BAT structure to see the type.
I notice that a SQL select gets the type info passed in as parameters for the 'exportValue' output.
For example:
sql.exportValue(1,".","second_single_value","decimal",9,3,8,_4,"");
How can I get this type information ('decimal', etc.) passed into my function?
Dru
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- | Stefan.Manegold@CWI.nl | Database Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
Hi Lefteris, I acknowledged that in my post yesterday (see intermediates). I think, over time, I will understand the internals better and implement a more performant solution. Right now, I'm just happy that it works :-). Best, Dru
On Mon, Nov 11, 2013 at 1:04 AM, Lefteris lsidir@gmail.com wrote:
Hi Dru,
your solution works but it adds one more expensive operation and that will make it slower, especially when data is big. As I mentioned earlier, another solution would be to create an implementation that is oblivious of the "display differences" between an int and a decimal.
On Mon, Nov 11, 2013 at 8:01 AM, Dru Nelson drudru@gmail.com wrote:
Hi,
While thinking about this in the morning yesterday, I came up with a solution. First let me explain a bit more about the challenge.
I was implementing percentile_cont for a contract project. I added the syntax to the parser. On the backend I was using dense_rank as my initial template. Because dense_rank takes ANY_1, it would receive either a flt
or
int BAT. I thought i had everything working great until i worked with a decimal column. Because the decimal type is just an int type scaled, and
the
BAT doesn't record this, I was stuck. The window function generates a
result
based on the values (vs dense_rank just needing to know ordinality)
My solution was to modify the AST after the parse to inject a CAST to a
dbl
on the OVER portion. It was not my ideal solution, since it will force an intermediate to be created for any type that isn't a double. None the
less,
the MAL optimizers work correctly and the function handles all types.
Best,
Dru
On Sat, Nov 9, 2013 at 8:22 AM, Lefteris lsidir@gmail.com wrote:
I second that! Are you sure that you cannot come up with an alternative algorithm for your aggregation that does not need this information (and possibly be faster) ?
On Sat, Nov 9, 2013 at 11:26 AM, Stefan Manegold <
Stefan.Manegold@cwi.nl>
wrote:
Hi Dru,
the decimal scale information is only maintained in the SQL catalog
and
given that decimals are stored as integers in MonetDB, neither the MonetDB kernel, nor MAL usually need to know about decimals or their scale.
Hence, the question is, whether/why your aggregate functions would
need
to know about decimals or their scale?
Best, Stefan
Dru Nelson drudru@gmail.com wrote:
HI,
I"m working on writing an aggregate in in C that is a MAL function. I've run into a problem with decimal types.
I've noticed that when I inspect the BAT inputs for various types,
they
are coerced to simpler types. (flt,dbl -> dbl). (int, decimal -> int). There is also no other information in the BAT structure to see the type.
I notice that a SQL select gets the type info passed in as parameters for the 'exportValue' output.
For example:
sql.exportValue(1,".","second_single_value","decimal",9,3,8,_4,"");
How can I get this type information ('decimal', etc.) passed into my function?
Dru
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- | Stefan.Manegold@CWI.nl | Database Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
participants (3)
-
Dru Nelson -
Lefteris -
Stefan Manegold