Graphql does not generate the same sql as an equivalent JSON query

[bryceredd]

Describe the bug We can build a query in the playground, and when we execute the query through the JSON API we can observe the generated sql (and it's correct), but when we click GraphiQL and execute it there the SQL is different and missing the time dimension in the WHERE clause.

To Reproduce

1. We build this query in playground:

{
  "measures": [
    "Orders.netSales"
  ],
  "timeDimensions": [
    {
      "dimension": "Orders.createdat",
      "dateRange": "Today"
    }
  ],
  "order": {},
  "dimensions": []
}

The corresponding SQL output that executes looks like this (this is correct):

  SELECT
      sum("orders"."payments" - "orders"."tax" - "orders"."tip") "orders__net_sales"
    FROM
      public.orders AS "orders"  WHERE ("orders"."createdAt" >= '2022-04-14T00:00:00.000Z'::timestamptz AND "orders"."createdAt" <= '2022-04-14T23:59:59.999Z'::timestamptz) AND ("orders".state <> 'PENDING') LIMIT 10000

In GraphiQL we see this:

query CubeQuery {
  cube(
    where: {
      orders: { createdat: { inDateRange: "Today" } }
    }
  ) {
    orders {
      netSales
    }
  }
}

And the generated SQL looks like this (this is incorrect, we're missing the time dimension from the where clause:

  SELECT
      sum("orders"."payments" - "orders"."tax" - "orders"."tip") "orders__net_sales"
    FROM
      public.orders AS "orders"  WHERE ("orders".state <> 'PENDING') LIMIT 10000

Expected behavior The generated SQL should be the same.

Minimally reproducible Cube Schema

cube("Orders", {
  sql: "SELECT * FROM public.order WHERE state <> 'PENDING'
  rewritesQueries: true,  
  ...

Version: 0.29.47

[ianschmitz]

Can confirm i'm experiencing the same behavior. The where clause for the time dimension is not applied when using the GraphQL API.

[github-actions[bot]]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you. If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines. You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[iiq374]

We also see this behaviour. It seems to behave correctly if the time dimension is included in the outputs - but this isn't always desired to have a grouping on the time dim

[iiq374]

It would look like the easiest way to resolve this (although not 100% semantically correct) would be to allow an 'all' type for the TimeDimension: https://github.com/cube-js/cube.js/blob/84e298ed06d6aa68735eecc61a311484eb9cf65b/packages/cubejs-api-gateway/src/graphql.ts#L87

Otherwise AFAIK the issue is caused by getFieldNodeChildren not returning the timeDimension - which means the filter never gets applied by the iterable: https://github.com/cube-js/cube.js/blob/84e298ed06d6aa68735eecc61a311484eb9cf65b/packages/cubejs-api-gateway/src/graphql.ts#L528