On Tue, Jul 31, 2012 at 1:11 AM, Stefan Manegold <Stefan.Manegold@cwi.nl> wrote:

Henry,

is there any chance you could upgrade from Aug2011-SP2 to a more recent release of MonetDB, preferably the latest Jul2012 ?

(Or at least test the problematic cases with this latest release?)

Stefan

----- Original Message -----
> We are seeing a very strange behavior from MonetDB from simple
> queries on a
> single table.
>
> In monthly_tou_avg_price table, we have monthly average aggregate
> data that
> were inserted from a very large table (> 2 billion) with hourly data.
> We
> have created the aggregate table to speed up certain queries.
> Strangely, we
> are getting incorrect results from very simple queries on the
> aggregate
> table.
>
> Q1 below returns 9,890 records with yyyy_mm column having empty value
> for
> the 2012-07 records. All of the 2012-07 records are missing the
> yyyy_mm
> value. We ran the SQL statement that inserted those records and it
> does not
> show any empty yyyy_mm values. If we check for empty string or null
> value
> in yyyy_mm column (Q2 and Q3) against the aggregate table, we do not
> get
> any hits. If we query for yyyy_mm= '2012-07' as in Q4, we get exactly
> 9890
> records showing yyyy_mm populated with value 2012-07. How could this
> be
> possible, Q1 showing empty yyyy_mm values for the same 9,890 records
> and Q4
> showing 2012-07 yyyy_mm values?
>
> Q1: select * from monthly_tou_avg_price where header_id = 6
> Q2: select * from monthly_tou_avg_price where header_id = 6 and
> yyyy_mm = ''
> Q3: select * from monthly_tou_avg_price where header_id = 6 and
> yyyy_mm is
> null
> Q4: select * from monthly_tou_avg_price where header_id = 6 and
> yyyy_mm =
> '2012-07'
>
> Q5 below returns empty value for yyyy_mm even though yyyy_mm column
> does
> not have any empty or null values. Q6 does not return any empty or
> null
> yyyy_mm values and contradicts Q5 result by returning 2012-07.
> Furthermore,
> Q7 contradicts Q5 with 9,890 records with 2012-07 value in yyyy_mm
> column.
>
> Q5: select distinct yyyy_mm from monthly_tou_avg_price where
> header_id = 6
> Q6: select distinct yyyy_mm from monthly_tou_avg_price
> Q7: select * from monthly_tou_avg_price where header_id = 6 and years
> =
> 2012 and months = 7
>
>
>
> yyyy_mm L15
> 9890
> 2009-04 7574
> 2009-05 7574
> 2009-06 7750
> 2009-07 7786
> 2009-08 7788
> 2009-09 7828
> 2009-10 7814
> 2009-11 7844
> 2009-12 8322
> 2010-01 8317
> 2010-02 8363
> 2010-03 8361
> 2010-04 9214
> 2010-05 9124
> 2010-06 9156
> 2010-07 9165
> 2010-08 9195
> 2010-09 9197
> 2010-10 9198
> 2010-11 9234
> 2010-12 9232
> 2011-01 9246
> 2011-02 9238
> 2011-03 9696
> 2011-04 9672
> 2011-05 9670
> 2011-06 9668
> 2011-07 9656
> 2011-08 9698
> 2011-09 9682
> 2011-10 9724
> 2011-11 9706
> 2011-12 9768
> 2012-01 9766
> 2012-02 9764
> 2012-03 9832
> 2012-04 9824
> 2012-05 9826
> 2012-06 9902
>
>
>
>
>
>
> We also ran an UPDATE statement to set yyyy_mm to 2012-07 for rows
> with
> header_id = 6 and years = 2012 and months = 7. And, 9,890 records
> were
> successfully updated. However, Q1 through Q8 returned same results as
> above
> with no improvement.
>
> We have not seen this kind of issues in other tables. This problem
> seems to
> be limited to a table that is populated with an INSERT INTO
> statement. We
> checked the result returned by the select portion of Q8 and yyyy_mm
> is
> populated with 2012-07. But, the aggregate table seems to end up in a
> weird
> state where it returns incorrect results.
>
> Q8: insert into "monthly_tou_avg_price"
> select a.header_id, extract(year from a.opr_date) as y,
> extract(month
> from a.opr_date) as m,
> b.yyyymm as yyyy_mm, b.svalue as TOU, a.resource_name,
> round(avg(a.lmp_cong_prc),5),
> round(avg(a.lmp_ene_prc),5),
> round(avg(a.lmp_loss_prc),5), round(avg(a.lmp_prc),5),
> round(avg(a.ns_clr_prc),5),
> round(avg(a.rd_clr_prc),5),
> round(avg(a.ru_clr_prc),5),
> round(avg(a.sp_clr_prc),5), round(avg(a.shadow_prc),5),
> round(avg(a.fuel_prc),5), count(lmp_cong_prc) as
> actual_lmpcong_tou_count
> from oasislive.price a
> join oasislive.time b
> on a.yyyymmddhh = b.yyyymmddhh
> where a.opr_date between '2012-07-01' and '2012-07-31'
> group by a.header_id, y, m, yyyy_mm, TOU, a.resource_name
> order by y, m, a.header_id, TOU, a.resource_name;
>
> We can see this INSERT INTO related issue with other queries. Q9
> shows that
> we do not have any values for 2011-03 ON period which is incorrect.
> In
> fact, if we specify '2011-03' as in Q10, it shows that we do in fact
> have
> values for the 2011-03 ON period!
>
> Q9: select yyyy_mm, tou, avg(lmp_prc) from monthly_tou_avg_price
> group by
> yyyy_mm, tou order by yyyy_mm, tou
>
> yyyy_mm tou L6
> .
> .
>
> 2011-03 OFF 16.721532797520894
> 2011-03 ON (null)
> 2011-04 OFF 15.427080074671187
> .
> .
>
> Q10: select yyyy_mm, tou, avg(lmp_prc) from monthly_tou_avg_price
> where
> yyyy_mm = '2011-03' group by yyyy_mm, tou order by yyyy_mm, tou
>
> yyyy_mm tou L11
> 2011-03 OFF 16.72153279752101
> 2011-03 ON 30.276486575893575
>
>
> I apologize for the long-winded email. I just wasn't sure how to
> describe
> what I was seeing.... Please let me know if there is any additional
> information that I can provide you with. By the way, we are currently
> using
> August 2011 -SP2 release, but saw this issue in older releases as
> well.
>
> Best regards,
>
> Henry
>
>
>
>
> We are currently running MonetDB August 2011 SP2 release on CentOS
>

> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond.
> Discussions
> will include endpoint security, mobile security and the latest in
> malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> MonetDB-users mailing list
> MonetDB-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-users
>

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users