Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-270

Generate complex conditions against MongoDB

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      We need to rework the MongoDB adapter to support complex conditions. Currently we use the `$match` operator whose syntax is limiting because it expects a column name on the LHS and a value on the RHS.

      We should instead generate a 'condition' column to be preceding `$project` operator. Then we can generate expressions the conventional way, e.g. `$gt: [e1, e2]` as opposed to `e1: {$gt: e2}`.

      In the following example, `cond` holds the evaluated condition, a subsequent `$match` filters on it, then a `$project` projects away the column.

      ```json
      db.zips.aggregate(
      {$project: {STATE: '$state', POP: '$pop'}},
      {$group: {_id: '$STATE', C: {$sum: 1}, _2: {$sum: '$POP'}}},
      {$project: {STATE: '$_id', C: '$C', _2: '$_2', cond: {$gt: [{$cond: [{$eq: ["$C", 0]}, null, "$_2"]}, 12000000]}}},
      {$match: {cond: true}},
      {$project: {STATE: 1, C: 1}})
      ```

      ---------------- Imported from GitHub ----------------
      Url: https://github.com/julianhyde/optiq/issues/270
      Created by: julianhyde
      Labels:
      Created at: Thu May 01 23:37:10 CEST 2014
      State: open

        Activity

        Hide
        github-import GitHub Import added a comment -

        [Date: Thu May 08 01:51:52 CEST 2014, Author: julianhyde]

        Also, test that queries like the following work:

        ```sql
        select "dataTest"."name" as "c0"
        from "sampleTest"."dataTest" as "dataTest"
        group by "dataTest"."name"
        having NOT((sum("dataTest"."valueProduct") is null))
        order by CASE WHEN "dataTest"."name" IS NULL THEN 1 ELSE 0 END,
        "dataTest"."name" ASC
        ```

        Show
        github-import GitHub Import added a comment - [Date: Thu May 08 01:51:52 CEST 2014, Author: julianhyde ] Also, test that queries like the following work: ```sql select "dataTest"."name" as "c0" from "sampleTest"."dataTest" as "dataTest" group by "dataTest"."name" having NOT((sum("dataTest"."valueProduct") is null)) order by CASE WHEN "dataTest"."name" IS NULL THEN 1 ELSE 0 END, "dataTest"."name" ASC ```

          People

          • Assignee:
            Unassigned
            Reporter:
            github-import GitHub Import
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development