On Thu, Oct 05, 2017 at 07:29:08PM +0000, Warren Master wrote:

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/716 e-mail: Niels.Nes@cwi.nl