View Only

 Has anyone seen spurious 1 = 0 condition added to query generated by analyzer?

John Craig's profile image
John Craig posted 01-24-2023 14:29

We are on (still waiting to get a clean process to upgrade to 9.3 on our Linux environments). We have some reports with 7 different filters and the reports return no results (when we know there is data). When we examine the generated SQL (mondrian_sql.log), we find this very odd condition in the WHERE clause of the main query (which certainly explains why the report displays no data):

and (1 = 0)

Has anyone else seen this? Did you figure out how to avoid it?

Thanks for your time,


Stephen Donovan's profile image
Stephen Donovan
My suspicion would be that one of the tuple sets in the filters is generating a zero data set.  That is then being subsituted in as 1 = 0 to the next level query.

Using Mondrian and you also mention Analyzer.  It sounds like you are an EE customer.  Have you brought this issue to the attention of our support team?  They would be able to dig much deeper into your specific scenrio then we can on this forum.
John Craig's profile image
John Craig
Thanks, Stephen. Yes, we're an enterprise customer, but being on an unsupported version of Pentaho means the available support is limited, but the idea that one of the prior tuple queries returned no results is a great place to look. What seems odd is that if the report is recreated from scratch, it does not seem to suffer from the same problems--but at least I have an idea I can use to troubleshoot the odd behavior. I'll suggest we experiment with our customer while I watch the mondrian_sql.log and check for zero row results from the tuple queries.