Restarting the server resolved the issue. I did not look at the plans..
There still remains the isse that tables can be added multiple times to the same merge table causing duplicate rows to be returned
In some cases we were unable to remove a table from a merge table without first dropping the merge table
Additionally we also have issues with bulk copy into tables of merge tables
GDKerror:!ERROR: Incompatible operands. - this occurs now and then.. the only fix is to drop the merge table , drop the affecting table that fails to bulk copy to , recreate it and recreate the merge table.
When I tried to drop the table after removing from the merge table , the server would core dump., I was only able to drop the bad table after dropping the merge table, the alter table Merge Table drop table Sub Table did not work
Warren S. Master
Chief Technology Officer, TRG Management LP
280 Park Avenue - 30th floor/West - New York, NY 10017
Phone 212.984.3504
Email warren.master@rohatyngroup.com
Web www.rohatyngroup.com
Linkedin www.linkedin.com/in/warrenmaster
-----Original Message-----
From: users-list [mailto:users-list-bounces+warren.master=rohatyngroup.com@monetdb.org] On Behalf Of users-list-request@monetdb.org
Sent: Friday, October 13, 2017 6:00 AM
To: users-list@monetdb.org
Subject: users-list Digest, Vol 62, Issue 5
Send users-list mailing list submissions to
users-list@monetdb.org
To subscribe or unsubscribe via the World Wide Web, visit
https://www.monetdb.org/mailman/listinfo/users-list
or, via email, send a message with subject or body 'help' to
users-list-request@monetdb.org
You can reach the person managing the list at
users-list-owner@monetdb.org
When replying, please edit your Subject line so it is more specific than "Re: Contents of users-list digest..."
Today's Topics:
1. Re: Merge Table Issues (Niels Nes)
----------------------------------------------------------------------
Message: 1
Date: Thu, 12 Oct 2017 20:39:51 +0200
From: Niels Nes
all_vw is a merge table with three tables all with identical structure
Warren Do the plan's look correct, ie include the proper member tables? Niels
intraday
history
currentyear
---------------------------------------------------------------------- -----------------------
My merge tables do not return consistent results
I have tried to refresh statistics
ANALYZE sys. intraday (asofdate) MINMAX
ANALYZE sys. history (asofdate) MINMAX
ANALYZE sys. currentyear (asofdate) MINMAX
still same issue
the issue exists for a few dates that I checked, there are many days that do work on the merge table , all dates work on the view and the individual tables.
I thought it might be an issue with statistics then the conversion function made it work on the merge table , so now at a loss.
the following query on the merge table return no rows
---------------------------------------------------------------------- -----------------------
select fundid,sum(curdlttot) as fxt
from vw where asofdate='2017-10-02'
group by fundid
The following does return data from the merge table albeit, much slower ? after converting date value
---------------------------------------------------------------------- -----------------------
select fundid,sum(curdlttot) as fxt
from vw where asofdate='2017-10-02D'
group by fundid
select fundid,sum(curdlttot) as fxt
from vw where asofdate= date_to_str(asofdate, '%Y-%m-%d') = '2017-10-02'
group by fundid
the followings query on the subtable of the merge table returns data with no issues or performance
---------------------------------------------------------------------- -----------------------
select fundid,sum(curdlttot) as fxt
from currentyear where asofdate='2017-10-02'
group by fundid
creating a view on the union of all three tables returns data as expected
create view test_all as
select * from intraday
union all
select * from history
union all
select * from currentyear
select fundid,sum(curdlttot) as fxt
from test_all where asofdate='2017-10-02'
group by fundid
Warren S. Master
Chief Technology Officer, CISO
TRG Management LP
280 Park Avenue - 30th floor/West - New York, NY 10017
Phone 212.984.3504
Email warren.master@rohatyngroup.com
Web www.rohatyngroup.com
Linkedin www.linkedin.com/in/warrenmaster
This e-mail communication and any attachments may contain confidential and privileged information and are for use by the intended addressee(s) only. If you are not the intended addressee, you have received this communication in error. Any dissemination or reproduction of this email or its contents is strictly prohibited and may be unlawful. If you suspect you have received this communication in error, please notify us immediately by replying to this message and delete it from your computer.Thank you.
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/716e-mail: Niels.Nes@cwi.nl ------------------------------ Subject: Digest Footer _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list ------------------------------ End of users-list Digest, Vol 62, Issue 5 ***************************************** This e-mail communication and any attachments may contain confidential and privileged information and are for use by the intended addressee(s) only. If you are not the intended addressee, you have received this communication in error. Any dissemination or reproduction of this email or its contents is strictly prohibited and may be unlawful. If you suspect you have received this communication in error, please notify us immediately by replying to this message and delete it from your computer.Thank you.