Unexpected update behaviour when using the for clause variable in the expression part of the set clause

SQL++
,
1

Hello!

While I was testing a migration query on our development database I have found a unexpected behaviour.

Here is a simplified example:

SELECT * FROM `Test`.`_default`.`_default`

[
  {
    "_default": {
      "ID": "key-1",
      "history": [
        {
          "subID": "sub1",
          "timestamp": 1
        }
      ]
    }
  },
  {
    "_default": {
      "ID": "key-2",
      "history": [
        {
          "subID": "sub1",
          "timestamp": 1
        },
        {
          "subID": "sub2",
          "timestamp": 2
        }
      ]
    }
  },
  {
    "_default": {
      "ID": "key-3",
      "history": [
        {
          "subID": "sub1",
          "timestamp": 1
        },
        {
          "subID": "sub2",
          "timestamp": 2
        },
        {
          "subID": "sub3",
          "timestamp": 3
        }
      ]
    }
  }
]

My intention is the following:

  • Create a new object called “nest” in each history entry.
  • Add a “type” field into the “nest” objects with a fixed string value.
  • Move the “subID” field into the “nest” object

My initial idea was the following:

UPDATE `Test`.`_default`.`_default`
SET s.`nest` = {} FOR s IN `history` END, s.`nest`.`subID` = s.`subID` FOR s IN `history` END, s.`nest`.`type` = "some-type" FOR s IN `history` END
UNSET s.`subID` FOR s IN `history` END
RETURNING *

[
  {
    "_default": {
      "ID": "key-1",
      "history": [
        {
          "nest": {
            "subID": "sub1",
            "type": "some-type"
          },
          "timestamp": 1
        }
      ]
    }
  },
  {
    "_default": {
      "ID": "key-2",
      "history": [
        {
          "nest": {
            "subID": "sub2",
            "type": "some-type"
          },
          "timestamp": 1
        },
        {
          "nest": {
            "subID": "sub2",
            "type": "some-type"
          },
          "timestamp": 2
        }
      ]
    }
  },
  {
    "_default": {
      "ID": "key-3",
      "history": [
        {
          "nest": {
            "subID": "sub3",
            "type": "some-type"
          },
          "timestamp": 1
        },
        {
          "nest": {
            "subID": "sub3",
            "type": "some-type"
          },
          "timestamp": 2
        },
        {
          "nest": {
            "subID": "sub3",
            "type": "some-type"
          },
          "timestamp": 3
        }
      ]
    }
  }
]

As you can see “subID” field is somehow only evaluated once and its value is cached for the following updates in the list. As far as I can see, this behaviour is not documented and possibly not intended either. In the documentation only static values are used on the right side of the assigment.

After some testing I have found I way to do what I wanted:

UPDATE `Test`.`_default`.`_default`
SET s.`nest` = OBJECT_PUT({"type": "some-type"}, "subID", s.`subID`) FOR s IN `history` END
UNSET s.`subID` FOR s IN `history` END
RETURNING *

[
  {
    "_default": {
      "ID": "key-1",
      "history": [
        {
          "nest": {
            "subID": "sub1",
            "type": "some-type"
          },
          "timestamp": 1
        }
      ]
    }
  },
  {
    "_default": {
      "ID": "key-2",
      "history": [
        {
          "nest": {
            "subID": "sub1",
            "type": "some-type"
          },
          "timestamp": 1
        },
        {
          "nest": {
            "subID": "sub2",
            "type": "some-type"
          },
          "timestamp": 2
        }
      ]
    }
  },
  {
    "_default": {
      "ID": "key-3",
      "history": [
        {
          "nest": {
            "subID": "sub1",
            "type": "some-type"
          },
          "timestamp": 1
        },
        {
          "nest": {
            "subID": "sub2",
            "type": "some-type"
          },
          "timestamp": 2
        },
        {
          "nest": {
            "subID": "sub3",
            "type": "some-type"
          },
          "timestamp": 3
        }
      ]
    }
  }
]

My guess is that this works, because the right side is a function and the return value is not cached.

1 Like
2

Thanks for reporting. It looks like LEFT side value is constant and evaluated once and reused to set. instead of re evaluate/reset again. Track via MB-52963

Also following should work.

UPDATE Test
SET s.`nest` = {"type": "some-type", s.`subID`} FOR s IN `history` END
UNSET s.`subID` FOR s IN `history` END
RETURNING *;

UPDATE Test 
SET history[pos] = OBJECT_REMOVE(OBJECT_PUT(v,"nest": {"type": "some-type", v.subID }),"subID") FOR pos:v IN `history` END
RETURNING *;