Please ignore my earlier email. I accidently sent the email before it was completed!<br><br><div class="gmail_quote"><div>We are seeing a very strange behavior from MonetDB from simple queries on a single table.</div><div>
<br></div><div>In monthly_tou_avg_price table, we have monthly average aggregate data that were inserted from a very large table (&gt; 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.</div>

<div><br></div><div>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= &#39;2012-07&#39; 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?</div>

<div><br></div>
<div><div>Q1: select * from monthly_tou_avg_price where header_id = 6    </div><div>Q2: select * from monthly_tou_avg_price where header_id = 6 and yyyy_mm = &#39;&#39;</div><div>Q3: select * from monthly_tou_avg_price where header_id = 6 and yyyy_mm is null </div>

<div>Q4: select * from monthly_tou_avg_price where header_id = 6 and yyyy_mm = &#39;2012-07&#39;</div></div><div><br></div><div>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.</div>

<div><div> </div><div><div>Q5: select distinct yyyy_mm from monthly_tou_avg_price where header_id = 6  </div><div>Q6: select distinct yyyy_mm from monthly_tou_avg_price </div><div>Q7: select * from monthly_tou_avg_price where header_id = 6 and years = 2012 and months = 7</div>

</div></div><div><br></div><div>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.</div>

<div><br></div><div>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. </div>

<div><br></div><div>Q8: insert into &quot;monthly_tou_avg_price&quot; </div><div>      select a.header_id, extract(year from a.opr_date) as y, extract(month from a.opr_date) as m, </div><div>               b.yyyymm as yyyy_mm, b.svalue as TOU, a.resource_name,</div>

<div>               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), </div><div>               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),</div>

<div>               round(avg(a.fuel_prc),5), count(lmp_cong_prc) as actual_lmpcong_tou_count</div><div>       from oasislive.price a</div><div>        join oasislive.time b</div><div>         on a.yyyymmddhh = b.yyyymmddhh</div>

<div>    where a.opr_date between &#39;2012-07-01&#39; and &#39;2012-07-31&#39;</div><div> group by a.header_id, y, m, yyyy_mm, TOU, a.resource_name</div><div> order by y, m, a.header_id, TOU, a.resource_name;</div><div>
<br>
</div><div>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 &#39;2011-03&#39; as in Q10, it shows that we do in fact have values for the 2011-03 ON period!</div>

<div><br></div><div><div>Q9: select yyyy_mm, tou, avg(lmp_prc) from monthly_tou_avg_price group by yyyy_mm, tou order by yyyy_mm, tou</div></div><div><br></div><div><div>yyyy_mm<span style="white-space:pre-wrap">	</span>tou<span style="white-space:pre-wrap">	</span>L6</div>

<div>.</div><div>.</div><div><br></div><div>2011-03<span style="white-space:pre-wrap">	</span>OFF<span style="white-space:pre-wrap">	</span>16.721532797520894</div><div>2011-03<span style="white-space:pre-wrap">	</span>ON<span style="white-space:pre-wrap">	</span>(null)</div>

<div>2011-04<span style="white-space:pre-wrap">	</span>OFF<span style="white-space:pre-wrap">	</span>15.427080074671187</div></div><div>.</div><div>.</div><div><br></div><div>Q10: select yyyy_mm, tou, avg(lmp_prc) from monthly_tou_avg_price where yyyy_mm = &#39;2011-03&#39; group by yyyy_mm, tou order by yyyy_mm, tou
</div><div><br></div><div><div>yyyy_mm<span style="white-space:pre-wrap">	</span>tou<span style="white-space:pre-wrap">	</span>L11</div><div>2011-03<span style="white-space:pre-wrap">	</span>OFF<span style="white-space:pre-wrap">	</span>16.72153279752101</div>

<div>2011-03<span style="white-space:pre-wrap">	</span>ON<span style="white-space:pre-wrap">	</span>30.276486575893575</div></div><div><br></div><div><br></div><div>I apologize for the long-winded email. I just wasn&#39;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.</div>

<div><br></div><div>Best regards,</div><div><br></div><div>Henry</div><div><br></div><div><br></div><div><br></div><div><br></div>We are currently running MonetDB August 2011 SP2 release on CentOS
</div><br>