hi, i've reproduced this bug with very straightforward table (but a complicated query).  the bug itself seems pretty nasty: it occurs on a table with >= 500,000 records but not with the exact same query on a table with <= 400,000 records.  this problem was introduced in Dec2016.  thanks all

https://www.monetdb.org/bugzilla/show_bug.cgi?id=6211





On Tue, Feb 7, 2017 at 6:42 PM, Anthony Damico <ajdamico@gmail.com> wrote:
hi monetdb folks, is it possible to figure out what's going wrong from the attached explain and plan?  thanks

On Wed, Feb 1, 2017 at 9:37 AM, Anthony Damico <ajdamico@gmail.com> wrote:
hi all, here's the EXPLAIN that triggers the opt.remap error calc.isnil not defined..  other errors i've come across when playing with variations of this query are calc.> not defined and mkey.hash not defined
this error is pretty hard for me to reduce..  when i've tried to replicate it with smaller queries or empty tables, it goes away..  and it did work on Jul2016-SP2.  thanks!!



On Wed, Feb 1, 2017 at 8:08 AM, Hannes Mühleisen <hannes.muehleisen@cwi.nl> wrote:
I doubt this is related to MonetDBLite. Rather, given the large query, I suspect a failure of the remap optimiser here.

Hannes

> On 31 Jan 2017, at 20:42, Brian Hood <brianh6854@googlemail.com> wrote:
>
> I think a upgrade file is missing i think you should maybe ask Hannes.
>
> https://github.com/hannesmuehleisen/MonetDBLite
>
> Its probably just some Math functions.
>
> You might be able to find them in the Mercurial repository and create them yourself.
>
> Regards,
>
> Brian Hood
>
> On Jan 31, 2017 6:34 PM, "Anthony Damico" <ajdamico@gmail.com> wrote:
> hi, after reconfiguring things to try to work around this, i hit a third error:   MALException:opt.remap:Function 'mkey.hash' not defined.  mkey hash looks like another function that was be defined for all types.  thanks
>
> On Tue, Jan 31, 2017 at 9:33 AM, Anthony Damico <ajdamico@gmail.com> wrote:
> hi, i continued narrowing down my example for this issue.  i attempted to reproduce it on an empty dataset and also on a different dataset, but neither triggered the error.
>
> the query is just two left joins stacked 33 times with UNION ALLs.  if i remove even one of the 33 stacks (many are duplicated), the error disappears.
>
> this error did not occur on Jun2016_SP2, it appears to have been introduced with Dec2016
>
> here is the condensed (easier to read) code that displays the core commands:  https://drive.google.com/file/d/0B3LuuP081KDYMXU1SWtjbGp6Q1k/view
>
> here's the PLAN:  https://drive.google.com/file/d/0B3LuuP081KDYMnJ5bFh2Zi1yTUk/view
>
> here's the full query:  https://drive.google.com/file/d/0B3LuuP081KDYWV9kQkxzbllUTE0/view
>
> i am confused how calc.isnil or calc.> could not be defined?  they are both defined for "any" type here:  https://github.com/MonetDB/MonetDB/blob/b40c1c1a33feece365fa6655802b6577df04af62/sql/common/sql_types.c#L1408-L1409
>
> please help?  thanks all
>
>
>
>
> On Mon, Jan 30, 2017 at 6:39 PM, Anthony Damico <ajdamico@gmail.com> wrote:
> hi, i tried narrowing down the query causing MALException:opt.remap:Function 'calc.isnil' not defined.
>
> i hit a (probably related) separate error that breaks on the greater than operator rather than isnil() ----  MALException:opt.remap:Function 'calc.>' not defined
>
> here's the PLAN for the calc.> not defined error:  https://drive.google.com/file/d/0B3LuuP081KDYOVZaX1dzcnIyUkk/view?usp=sharing
>
> here's a slightly parsed down PLAN for the calc.isnil error: https://drive.google.com/file/d/0B3LuuP081KDYdkExYk1xazlCVjg/view?usp=sharing
>
> do either of these PLAN statements help identify what's going on?  otherwise i'll try to continue reducing this.. thanks
>
>
>
> On Mon, Jan 30, 2017 at 6:48 AM, Anthony Damico <ajdamico@gmail.com> wrote:
> after rebuilding the database from scratch and using Dec2016, i'm still hitting this calc.isnul error that did not happen in the previous version:  MALException:opt.remap:Function 'calc.isnil' not defined
>
> does anyone have advice about what might be triggering the error?  i've pasted the PLAN here..  https://drive.google.com/file/d/0B3LuuP081KDYdC1QRzRWbHRPUkE/view?usp=drive_web
>
> it does work if i run some subset of the queries being combined with the UNION ALL, just not with the entire UNION ALL, so pretty tough to narrow down (though i will try more if there's no obvious culprit here).
>
> thanks!
>
>
> On Sat, Jan 28, 2017 at 11:14 AM, Anthony Damico <ajdamico@gmail.com> wrote:
> hi, thanks, i am using monetdblite on windows so i think everything would be pre-packaged?  i'm also confused that it looks like there haven't been any changes to isnil recently[1].  i looked at the sql PLAN and it's old enough that there are still VARCHARs in the dataset, so before i kill any more time narrowing down the bug, maybe i should rebuild from scratch on the latest software version..
>
> https://github.com/hannesmuehleisen/MonetDBLite/search?o=desc&q=isnil&s=committer-date&type=Commits&utf8=%E2%9C%93
>
> On Sat, Jan 28, 2017 at 10:31 AM, Brian Hood <brianh6854@googlemail.com> wrote:
> When i have seen a message like this in the past it means that calc.isnull requires an external library.
>
> However unsure in this case.
>
> So maybe check your the monetdb binaries with ldd to find any dependencies that are not found.
>
> Regards,
>
> Brian Hood
>
> On Sat, Jan 28, 2017 at 3:13 PM, Anthony Damico <ajdamico@gmail.com> wrote:
> hi, i upgraded monetdb and am hitting this error with code that previously worked.  my (giant) query has lots of CAST and IS NULL commands in it..  trying to narrow it down now, but does anyone have ideas about what might've introduced this?  it looks like something is calling IS NULL where it shouldn't be allowed to?  thanks
>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>
>
>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>
>
>
>
>
>
>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list