Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Semantics of .WRITE(NULL, NULL, NULL) when updating a VARBINARY(max) column

What are the semantics of the following query

UPDATE table
SET column .WRITE(NULL, NULL, NULL)

if column is of VARBINARY(max) type and its contents are not NULL?

A quick test suggests that the query is a no-op:

--DROP TABLE [table]
CREATE TABLE [table] ([column] VARBINARY(max))
INSERT INTO [table] VALUES (0x12345678)
UPDATE [table]
SET [column] .WRITE(NULL, NULL, NULL)
SELECT * FROM [table]

Executing it does not alter the data in the column. However, I can't seem to find evidence in the documentation. Have I overlooked something, or is this no-op behavior likely to change?

like image 602
krlmlr Avatar asked Feb 16 '23 12:02

krlmlr


1 Answers

Actually a careful reading of the doc that was linked to (here) logically implies that .WRITE(NULL,NULL,NULL) should try to truncate the column to the length that it already is. I.E., effectively a no-op.

Note the italicized sections in this extract from the Doc:

.WRITE (expression,@Offset,@Length):

Specifies that a section of the value of column_name is to be modified. expression replaces @Length units starting from @Offset of column_name. ...

expression: is the value that is copied to column_name. ... If expression is set to NULL, @Length is ignored, and the value in column_name is truncated at the specified @Offset.

@Offset: is the starting point in the value of column_name at which expression is written. ... If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored. ...

So if @Offset is NULL, then it is treated as though it were the current length of the column.

And, if expression is NULL, the column is truncated at the @Offset value.

Ergo, if both are NULL, then the column is truncated at its current length. Which I take to mean that it effectively does nothing.

like image 184
RBarryYoung Avatar answered May 03 '23 04:05

RBarryYoung