4.5 Beta Array indexing and alias

SQL++
1

when i use alias with array indexing.
query plan is not properly executing.

without alias. plan is fine.

explain SELECT * 
FROM PlayHistory USE INDEX(`PLAYHISTORY_GPID_IDX`) 
WHERE ANY t IN PlayHistory.players SATISFIES t.userName = "rnVHZOQLutmH8nD0duoxeuOG" END 

[
  {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "UnionScan",
        "scans": [
          {
            "#operator": "IndexScan",
            "index": "PLAYHISTORY_GPID_IDX",
            "keyspace": "PlayHistory",
            "namespace": "default",
            "spans": [
              {
                "Range": {
                  "High": [
                    "\"rnVHZOQLutmH8nD0duoxeuOG\""
                  ],
                  "Inclusion": 3,
                  "Low": [
                    "\"rnVHZOQLutmH8nD0duoxeuOG\""
                  ]
                }
              }
            ],
            "using": "gsi"
          }
        ]
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Fetch",
              "keyspace": "PlayHistory",
              "namespace": "default"
            },
            {
              "#operator": "Filter",
              "condition": "any `t` in (`PlayHistory`.`players`) satisfies ((`t`.`userName`) = \"rnVHZOQLutmH8nD0duoxeuOG\") end"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "self",
                  "star": true
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  }
]

with alias it uses primary index.

explain SELECT * 
FROM PlayHistory as h USE INDEX(`PLAYHISTORY_GPID_IDX`) 
WHERE ANY t IN h.players SATISFIES t.userName = "rnVHZOQLutmH8nD0duoxeuOG" END 

[
  {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "PrimaryScan",
        "index": "#primary",
        "keyspace": "PlayHistory",
        "namespace": "default",
        "using": "gsi"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Fetch",
              "as": "h",
              "keyspace": "PlayHistory",
              "namespace": "default"
            },
            {
              "#operator": "Filter",
              "condition": "any `t` in (`h`.`players`) satisfies ((`t`.`userName`) = \"rnVHZOQLutmH8nD0duoxeuOG\") end"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "self",
                  "star": true
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  }
]

index

CREATE INDEX `PLAYHISTORY_GPID_IDX` ON `PlayHistory`((distinct (array (`t`.`userName`) for `t` in (`PlayHistory`.`players`) end)),(`PlayHistory`.`timestamp`),(`PlayHistory`.`playType`)) WITH({"index_type" : "forestdb"})

i use same variable name in array index.
how can i give alias to bucket name in create index statement.

query result as below.

2

This should be fixed in the upcoming Beta.

3

beta version doesn’t solve this problem.

4

Please try to drop the index and re create with following statements.
In your create index statement you used bucket name in the array index portion. Which is not required.

drop index `PlayHistory`.`PLAYHISTORY_GPID_IDX`;
CREATE INDEX `PLAYHISTORY_GPID_IDX` ON `PlayHistory`((distinct (array (`t`.`userName`) for `t` in `players` end)),`timestamp`,`playType`) WITH({“index_type” : “forestdb”});

5

thanks a lot. i appreciate that.