Re: All tested on the same column, some functions work, some don't?

On Tue, Apr 23, 2013 at 12:37:32PM -0400, Anthony Damico wrote:

On Tue, Apr 23, 2013 at 11:28 AM, Stefan Manegold wrote:

...

"wrd" is an internal MonetDB type.

Which SQL type did you use in you your create table statement? I.e., what does \d x07 say about the type of column rx_cso_num ?

Sorry about that -- it's a BIGINT

...

Or did you create the table as result of a SQL query (as you suggest?)?

Yes, I did..

...

Then, the clause(s) of the query that determine column rx_cso_num as well as the (SQL) types of the related columns of the source table (if any) would be useful.

..inside the clause:

COUNT( CASE WHEN MY_CODE IN ('01','02') THEN UNIQUE_ROW_IDENTIFIER END ) as rx_cso_num

Am I right that this clause is embedded in a SQL statement like create table ... as select ... <clause> ... from <table> with data; where <table> has columns UNIQUE_ROW_IDENTIFIER and MY_CODE ?

both UNIQUE_ROW_IDENTIFIER and MY_CODE are VARCHAR(255). I hope that's what you're asking for?

...

Also, could you run the failing queries in mclient prefixed with "explain" and "trace" (i.e., run it twice), and send us the output (in case of "trace", only the second result set is of interest).

I ran trace and explain in front of all five queries. Of those ten queries, the four not shown print much more detail (that would be more helpful for troubleshooting).. but I don't understand the output, so I'm not comfortable with sending it. I'm sorry that these six results aren't terribly helpful..

Ok, TRACE does not produce any more useful info since the queries fail. My thinko. EXPLAIN merely produces the physical execution plan (in MonetDB's MAL language) of your queires (which you already shared with us). The plan does not contain any data other than the table names, column names and literals in your query that you already shared with us. Without being able to reproduce (and thus debug) the problem, or at least getting more insight from the physical query plan, it will very hard for us to understand, locate, and hopefully fix the problem. Stefan

sql>trace select median(rx_cso_num) from x07; overflow in conversion of 133 to bte. sql>trace select stddev(rx_cso_num) from x07; SELECT: no such unary operator 'stddev(wrd)' sql>explain select stddev(rx_cso_num) from x07; SELECT: no such unary operator 'stddev(wrd)' sql>trace select stddev_pop(rx_cso_num) from x07; SELECT: no such unary operator 'stddev_pop(wrd)' sql>explain select stddev_pop(rx_cso_num) from x07; SELECT: no such unary operator 'stddev_pop(wrd)' sql>trace select prod(rx_cso_num) from x07; overflow in conversion of 133 to bte.

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-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) |