Query total: true doesn't work -- Cube Store support

[Muhamed-M]

Describe the bug I'm trying to implement pagination on table and I'm doing it with limit and offset, but I also need a total number of rows so that pagination can work correctly. I implemented it my way, but it's a little bit slow. I was looking for best practices and I saw that you recently added a way to do it and it's not documented yet. I looked at tests and figured out how to use, but it doesnt't work for me. Can you tell me is problem in me or it's a bug. Here is my query:

query: {
                measures: ['Waitlist.count', 'Waitlist.active', 'Waitlist.thisWeek', 'Waitlist.thisMonth'],
                order: {
                    'Waitlist.count': 'desc'
                },
                dimensions: ['Waitlist.productId', 'Waitlist.sku', 'Waitlist.title', 'Waitlist.artist'],
                filters: [
                    {
                        member: 'Waitlist.status',
                        operator: 'equals',
                        values: ['1']
                    }
                ],
                limit: 25,
                total: true,
                offset: 0
            }

When I pass query to .load() method I'm getting this huge error which tells me nothing.

Version: [0.30.29]

[ivan-vdovin]

Hi @Muhamed-M Could you share full error here please? Also do you use pre-aggs in this case?

[Muhamed-M]

Error:

Uncaught (in promise) Error: Error: Internal: Error during planning: Extension planner for LogicalAlias { input: Projection: #SUM(waitlist___waitlist_products.waitlist__count) AS waitlist__count
  Aggregate: groupBy=[[#waitlist___waitlist_products.waitlist__product_id, #waitlist___waitlist_products.waitlist__sku, #waitlist___waitlist_products.waitlist__title, #waitlist___waitlist_products.waitlist__artist]], aggr=[[SUM(#waitlist___waitlist_products.waitlist__count)]]
    ClusterSend
      Filter: #waitlist___waitlist_products.waitlist__status Eq Utf8("1")
        TableScan: waitlist___waitlist_products projection=Some([0, 2, 3, 4, 5, 8]), filters=[#waitlist__status Eq Utf8("1")], alias: "original_query", schema: DFSchema { fields: [DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__product_id", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__sku", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__title", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__artist", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__count", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__active", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__this_week", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__this_month", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }] } } created an ExecutionPlan with mismatched schema. LogicalPlan schema: DFSchema { fields: [DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__product_id", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__sku", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__title", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__artist", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__count", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__active", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__this_week", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }, DFField { qualifier: Some("original_query"), field: Field { name: "waitlist__this_month", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None } }] }, ExecutionPlan schema: Schema { fields: [Field { name: "waitlist__count", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: None }], metadata: {} }
    at _callee5$ (cubejs-client-core.esm.js?1bbc:2003:1)
    at tryCatch (runtime.js?96cf:63:1)
    at Generator.invoke [as _invoke] (runtime.js?96cf:294:1)
    at Generator.eval [as next] (runtime.js?96cf:119:1)
    at asyncGeneratorStep (asyncToGenerator.js?c973:3:1)
    at _next (asyncToGenerator.js?c973:25:1)

I'm using pre-aggs

[paveltiunov]

@Muhamed-M total query isn't supported in Cube Store yet.

[kodeine]

@paveltiunov so i am trying to use total as well for pagination needs and a table with pre aggregation gives error like above if total is true but if its set to false it works fine. Without pre aggregation it works fine. Any ideas for any alternatives we can use to get total until this issue is resolved?

[mihaiiova]

@kodeine, I currently do a second call with /load where I get the total from. I don't know if it helps speed things up, but I only use the dimensions, filters and timeDimensions from the initial query + limit of 2. I only take the total out of this second call.

[kodeine]

@mihaiiova i understand but running multiple queries just to get total is not my preferred method.

[paveltiunov]

This one should be fixed in the latest version.