UNION - Take only left side of the query

SQL++
,
1

I have 2 queries: Q1 and Q2

  • Q1 returns 16 items.
  • Q2 returns 170 items.

If I write my query like this: Q1 UNION Q2 I only get 16 items. And if I write it the other way: Q2 UNION Q1 I got 170 items. But I would like to have 186 items. Is my understanding of the UNION keyword is bad?

EDIT

Here is an example:

SELECT MAX(event.dateTime) as dateTime, event.visitId FROM default visit
JOIN default event ON KEY event.visitId FOR visit
WHERE visit.visitType = "VT_HOSPITALIZATION"
AND event.dateTime IS VALUED
AND SPLIT(META(visit).`id`,':')[0] != "_sync"
AND IFMISSING(visit.isVoided,"") IN  [false , ""] AND
event.`type` = "EVENT"
GROUP BY event.visitId HAVING event.visitId IS NOT MISSING

UNION

SELECT MAX(event.dateTime) as dateTime, event.visitId FROM default visit
JOIN default event ON KEY event.visitId FOR visit
WHERE event.`type` = "EVENT"
AND visit.visitType = "VT_CONSULTATION"
AND visit.status = "WAITING"
AND DATE_DIFF_MILLIS(NOW_MILLIS(), visit.startDateTime, "day") > 7                         
AND SPLIT(META(visit).`id`,':')[0] != "_sync" 
AND IFMISSING(visit.isVoided,"") IN  [false , ""]
GROUP BY event.visitId HAVING event.visitId IS NOT MISSING

Thank’s.

PS: I’m using Couchbase Server 4.5.1.

2

try use UNION ALL

3

It’s the same issue.
– I’ve edited my original post with an example of my queries.

4

post the explain plan

5

Here is the explain plan:

[
  {
    "plan": {
      "#operator": "UnionAll",
      "children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "DistinctScan",
              "scan": {
                "#operator": "IndexScan",
                "covers": [
                  "cover ((`visit`.`visitType`))",
                  "cover (ifmissing((`visit`.`isVoided`), \"\"))",
                  "cover ((`visit`.`visitId`))",
                  "cover ((meta(`visit`).`id`))"
                ],
                "index": "idx_visit_type_id",
                "index_id": "77d2fec4de56c10d",
                "keyspace": "default",
                "namespace": "default",
                "spans": [
                  {
                    "Range": {
                      "High": [
                        "\"VT_HOSPITALIZATION\"",
                        "successor(false)"
                      ],
                      "Inclusion": 1,
                      "Low": [
                        "\"VT_HOSPITALIZATION\"",
                        "false"
                      ]
                    }
                  },
                  {
                    "Range": {
                      "High": [
                        "\"VT_HOSPITALIZATION\"",
                        "successor(\"\")"
                      ],
                      "Inclusion": 1,
                      "Low": [
                        "\"VT_HOSPITALIZATION\"",
                        "\"\""
                      ]
                    }
                  }
                ],
                "using": "gsi"
              }
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "IndexJoin",
                    "as": "event",
                    "for": "visit",
                    "keyspace": "default",
                    "namespace": "default",
                    "on_key": "(`event`.`visitId`)",
                    "scan": {
                      "index": "idx_visitId",
                      "index_id": "b6498c75045de40a",
                      "using": "gsi"
                    }
                  },
                  {
                    "#operator": "Filter",
                    "condition": "(((((cover ((`visit`.`visitType`)) = \"VT_HOSPITALIZATION\") and ((`event`.`dateTime`) is valued)) and (not ((split(cover ((meta(`visit`).`id`)), \":\")[0]) = \"_sync\"))) and (cover (ifmissing((`visit`.`isVoided`), \"\")) in [false, \"\"])) and ((`event`.`type`) = \"EVENT\"))"
                  },
                  {
                    "#operator": "InitialGroup",
                    "aggregates": [
                      "max((`event`.`dateTime`))"
                    ],
                    "group_keys": [
                      "(`event`.`visitId`)"
                    ]
                  }
                ]
              }
            },
            {
              "#operator": "IntermediateGroup",
              "aggregates": [
                "max((`event`.`dateTime`))"
              ],
              "group_keys": [
                "(`event`.`visitId`)"
              ]
            },
            {
              "#operator": "FinalGroup",
              "aggregates": [
                "max((`event`.`dateTime`))"
              ],
              "group_keys": [
                "(`event`.`visitId`)"
              ]
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Filter",
                    "condition": "((`event`.`visitId`) is not missing)"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "as": "dateTime",
                        "expr": "max((`event`.`dateTime`))"
                      },
                      {
                        "expr": "(`event`.`visitId`)"
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject"
                  }
                ]
              }
            }
          ]
        },
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "IntersectScan",
              "scans": [
                {
                  "#operator": "DistinctScan",
                  "scan": {
                    "#operator": "IndexScan",
                    "index": "idx_visit_type_id",
                    "index_id": "77d2fec4de56c10d",
                    "keyspace": "default",
                    "namespace": "default",
                    "spans": [
                      {
                        "Range": {
                          "High": [
                            "\"VT_CONSULTATION\"",
                            "successor(false)"
                          ],
                          "Inclusion": 1,
                          "Low": [
                            "\"VT_CONSULTATION\"",
                            "false"
                          ]
                        }
                      },
                      {
                        "Range": {
                          "High": [
                            "\"VT_CONSULTATION\"",
                            "successor(\"\")"
                          ],
                          "Inclusion": 1,
                          "Low": [
                            "\"VT_CONSULTATION\"",
                            "\"\""
                          ]
                        }
                      }
                    ],
                    "using": "gsi"
                  }
                },
                {
                  "#operator": "IndexScan",
                  "index": "idx_visit_status_start_date_time",
                  "index_id": "ba3cc5a10a39ec30",
                  "keyspace": "default",
                  "namespace": "default",
                  "spans": [
                    {
                      "Range": {
                        "High": [
                          "successor(\"WAITING\")"
                        ],
                        "Inclusion": 1,
                        "Low": [
                          "\"WAITING\""
                        ]
                      }
                    }
                  ],
                  "using": "gsi"
                }
              ]
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "IndexJoin",
                    "as": "event",
                    "for": "visit",
                    "keyspace": "default",
                    "namespace": "default",
                    "on_key": "(`event`.`visitId`)",
                    "scan": {
                      "index": "idx_visitId",
                      "index_id": "b6498c75045de40a",
                      "using": "gsi"
                    }
                  },
                  {
                    "#operator": "Filter",
                    "condition": "(((((((`event`.`type`) = \"EVENT\") and (cover ((`visit`.`visitType`)) = \"VT_CONSULTATION\")) and ((`visit`.`status`) = \"WAITING\")) and (7 < date_diff_millis(now_millis(), (`visit`.`startDateTime`), \"day\"))) and (not ((split(cover ((meta(`visit`).`id`)), \":\")[0]) = \"_sync\"))) and (cover (ifmissing((`visit`.`isVoided`), \"\")) in [false, \"\"]))"
                  },
                  {
                    "#operator": "InitialGroup",
                    "aggregates": [
                      "max((`event`.`dateTime`))"
                    ],
                    "group_keys": [
                      "(`event`.`visitId`)"
                    ]
                  }
                ]
              }
            },
            {
              "#operator": "IntermediateGroup",
              "aggregates": [
                "max((`event`.`dateTime`))"
              ],
              "group_keys": [
                "(`event`.`visitId`)"
              ]
            },
            {
              "#operator": "FinalGroup",
              "aggregates": [
                "max((`event`.`dateTime`))"
              ],
              "group_keys": [
                "(`event`.`visitId`)"
              ]
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Filter",
                    "condition": "((`event`.`visitId`) is not missing)"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "as": "dateTime",
                        "expr": "max((`event`.`dateTime`))"
                      },
                      {
                        "expr": "(`event`.`visitId`)"
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject"
                  }
                ]
              }
            }
          ]
        }
      ]
    },
    "text": "SELECT MAX(event.dateTime) as dateTime, event.visitId FROM default visit\nJOIN default event ON KEY event.visitId FOR visit\nWHERE visit.visitType = \"VT_HOSPITALIZATION\"\nAND event.dateTime IS VALUED\nAND SPLIT(META(visit).`id`,':')[0] != \"_sync\"\nAND IFMISSING(visit.isVoided,\"\") IN  [false , \"\"] AND\nevent.`type` = \"EVENT\"\nGROUP BY event.visitId HAVING event.visitId IS NOT MISSING\n\nUNION ALL\n\nSELECT MAX(event.dateTime) as dateTime, event.visitId FROM default visit\nJOIN default event ON KEY event.visitId FOR visit\nWHERE event.`type` = \"EVENT\"\nAND visit.visitType = \"VT_CONSULTATION\"\nAND visit.status = \"WAITING\"\nAND DATE_DIFF_MILLIS(NOW_MILLIS(), visit.startDateTime, \"day\") > 7                         \nAND SPLIT(META(visit).`id`,':')[0] != \"_sync\" \nAND IFMISSING(visit.isVoided,\"\") IN  [false , \"\"]\nGROUP BY event.visitId HAVING event.visitId IS NOT MISSING"
  }
]
6

Post the index definitions. Also try with brackets () around each UNION arm (select…) union (select …)

7

idx_visitId: CREATE INDEX idx_visitId ON default(visitId) WHERE (type = "EVENT")

idx_visit_type_id: CREATE INDEX idx_visit_type_id ON default(visitType,ifmissing(isVoided, ""),visitId) WHERE (not ((split((meta().id), ":")[0]) = "_sync"))

idx_visit_status_start_date_time: CREATE INDEX idx_visit_type_id ON default(visitType,ifmissing(isVoided, ""),visitId) WHERE (not ((split((meta().id), ":")[0]) = "_sync"))

8

Please try to give Unique alias names across the UNION arms and try it ( MB-28878)

Also HAVING on group key (event.visitId IS NOT MISSING) is unnecessary ( if need move it to WHERE clause) because it is JOIN condition if it is MISSING join eliminates it

9

Ok, I have 2 dev instances of couchbase-server and I’m used to play with the one that is on 4.5.1 but I was actually doing my test on the other one and I taught it was on 4.5.1 too but this one was on 4.5.0.

Upgrading to 4.5.1 seemed to have fixed the bug.