# Canvas showing error instead of 0 **URL:** \ **Category:** Kibana\ **Tags:** canvas\ **Created:** [January 14, 2021, 1:56am UTC](https://discuss.elastic.co/t/canvas-showing-error-instead-of-0/261068 "2021-01-14T01:56:53Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\ **Post date:** [January 14, 2021, 1:56am UTC](https://discuss.elastic.co/t/canvas-showing-error-instead-of-0/261068/1 "2021-01-14T01:56:54Z")
Hello, I have a query that returns 0 records. The query is also using a filter of "The last 12 hours". I have 0 documents in the index, for the past 2 weeks, so the query is querying NULL(?). ```auto SELECT COUNT(DISTINCT uuid.keyword) as count FROM (SELECT uuid.keyword, resolved.keyword, acknowledged.keyword, timestamp FROM "my-index*" WHERE x.keyword = 'x' ORDER BY timestamp_last_updated DESC) WHERE resolved.keyword = 'false' AND timestamp > NOW() - INTERVAL 5 MINUTES ``` When I look at the data preview, I see 0. When I look at the visualisation on Canvas, I get an error icon. **Canvas:** ![image](https://us1.discourse-cdn.com/elastic/original/3X/0/3/03b7268c9ab7250d3732c0bcc970edb352cfb875.png) **Preview:** ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/f/bfb27135c58f19b39556526c1cd470155ec383d3.png) **Error** due to missing documents: ![image](https://us1.discourse-cdn.com/elastic/original/3X/e/4/e4fe58938e8f90666da50e18d1cac8fbe20005c3.png) Is this a bug? feature? How can I show 0, in my case of 0 documents in the index? Cheers! ---
**Author:** ![Marta\_Bondyra](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marta_bondyra/32/102122_2.png) [@Marta\_Bondyra](https://discuss.elastic.co/u/Marta_Bondyra)\ **Post date:** [January 14, 2021, 10:27am UTC](https://discuss.elastic.co/t/canvas-showing-error-instead-of-0/261068/2 "2021-01-14T10:27:02Z")
The error indicates that the field `timestamp_last_updated` might not exist. Can you double check the name? Does it work when you remove `ORDER BY timestamp_last_updated DESC`? ---
**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\ **Post date:** [January 14, 2021, 11:27pm UTC](https://discuss.elastic.co/t/canvas-showing-error-instead-of-0/261068/3 "2021-01-14T23:27:57Z")
@Marta_Bondyra Thanks for the reply. I know what this means, but that is not the case. This is a false error. I believe that canvas cannot find any document in the index (according to the 12 hours filter), hence this error. This is the root cause and looks like a bug to me. If I cancel the 12 hours filter, for example, I can see results. ![image](https://us1.discourse-cdn.com/elastic/original/3X/7/2/72531867cb629ffbb08d7e125dedc3c986671331.png) Another error which is not realistic. ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/7/d7a9e61a0c559247a5ac817deee8277c3161c655.png) How do I workaround this? Thanks! ---
**Author:** ![Marta\_Bondyra](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marta_bondyra/32/102122_2.png) [@Marta\_Bondyra](https://discuss.elastic.co/u/Marta_Bondyra)\ **Post date:** [January 15, 2021, 7:35am UTC](https://discuss.elastic.co/t/canvas-showing-error-instead-of-0/261068/4 "2021-01-15T07:35:23Z")
I'll try to get someone from the Canvas team to check if it's not a bug then. In the meantime can you let us know what version of the stack you're using? ---
**Author:** ![corey.robertson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/corey.robertson/32/54611_2.png) [@corey.robertson](https://discuss.elastic.co/u/corey.robertson)\ **Post date:** [January 15, 2021, 2:43pm UTC](https://discuss.elastic.co/t/canvas-showing-error-instead-of-0/261068/5 "2021-01-15T14:43:36Z")
Hi @AClerk I think this is a limitation with ElasticsearchSQL and sub-selects [https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html#\_using\_a\_sub\_select](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html#_using_a_sub_select) Can you flatten it into a single query like this, or am I missing some logic of what this query is trying to do? ```auto select count(distinct uuid.keyword) as count from "my-index*" where x.keyword = 'x' and resolved.keyword = 'false' and timestamp > now() - interval 5 minutes ``` ---
**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\ **Post date:** [January 17, 2021, 10:43pm UTC](https://discuss.elastic.co/t/canvas-showing-error-instead-of-0/261068/6 "2021-01-17T22:43:22Z")
Hi @corey.robertson Thanks for the info. 1. I was not aware of such a limitation. Good to know! 2. I am not sure why the nested query. I inherited it 🕶 Getting the following error ![image](https://us1.discourse-cdn.com/elastic/original/3X/c/7/c711d0b3c3f3d459b0155b3f7b5ce991fba88194.png) I guess this is the reason for the nested query?! Cheers!~ ---
**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\ **Post date:** [January 17, 2021, 10:50pm UTC](https://discuss.elastic.co/t/canvas-showing-error-instead-of-0/261068/7 "2021-01-17T22:50:04Z")
@corey.robertson Wrong conclusions. Edited the message above ---
**Author:** ![corey.robertson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/corey.robertson/32/54611_2.png) [@corey.robertson](https://discuss.elastic.co/u/corey.robertson)\ **Post date:** [January 19, 2021, 12:57pm UTC](https://discuss.elastic.co/t/canvas-showing-error-instead-of-0/261068/8 "2021-01-19T12:57:43Z")
@AClerk Can you share your full query? I wasn't sure why it was ordering in the original query since it's just doing a count. ---
**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\ **Post date:** [January 19, 2021, 11:21pm UTC](https://discuss.elastic.co/t/canvas-showing-error-instead-of-0/261068/9 "2021-01-19T23:21:03Z")
@corey.robertson I need to know how many documents have resolved.keyword=false. But only in the last minutes, not from the whole index. I guess that the where clause covers it ```auto WHERE ... timestamp > NOW() - INTERVAL 5 MINUTES ``` So maybe you are right. The internal query is redundant in this case. _FULL query:_ ```auto SELECT COUNT(DISTINCT uuid.keyword) as count FROM (SELECT uuid.keyword, resolved.keyword, acknowledged.keyword, timestamp FROM "my-index*" WHERE x.keyword = 'x' ORDER BY timestamp_last_updated DESC) WHERE resolved.keyword = 'false' AND timestamp > NOW() - INTERVAL 5 MINUTES ``` Cheers! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 16, 2021, 11:21pm UTC](https://discuss.elastic.co/t/canvas-showing-error-instead-of-0/261068/10 "2021-02-16T23:21:16Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.