feat(spanner): allow untyped nil values in parameterized queries #4482
Conversation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Removes the restriction that nil valued parameters must be typed. The current restriction prevents the usage of DML statements with one or more untyped nil parameter values to be used to insert/update a value to NULL. Cloud Spanner does not require all parameters to be typed, only when it would otherwise be ambigous what type should be used (e.g. non-NULL STRING/BYTES values). This restriction also prevents the usage of the go/sql driver with gorm, as gorm will generate statements with untyped nil parameter values. Fixes #4481
google-cla
bot
added
the
cla: yes
Skip testing DML with untyped parameters on the emulator as it does not support untyped parameters. See GoogleCloudPlatform/cloud-spanner-emulator#31
hengfengli
changed the title
hengfengli
changed the title
hengfengli
reviewed
Jul 22, 2021
spanner/integration_test.go
Outdated
| // Write rows into table first using DML. Only do this on real Spanner | ||
| // as the emulator does not support untyped parameters. | ||
| // TODO: Remove when the emulator supports untyped parameters. | ||
| if isEmulatorEnvSet() { |
There was a problem hiding this comment.
Shouldn't this be !isEmulatorEnvSet() if the emulator env is not set?
hengfengli
reviewed
Jul 22, 2021
| { | ||
| nil, | ||
| nullProto(), | ||
| nil, |
There was a problem hiding this comment.
Just curious: would this work? Because it expects a *sppb.Type but we give nothing, e.g.,
func numericType() *sppb.Type {
return &sppb.Type{Code: sppb.TypeCode_NUMERIC}
}
There was a problem hiding this comment.
In this test it would fail, as the test will verify that the param type is not set (or set to nil). That comparison will fail if we set it to TypeCode_NUMERIC. If we were to do something like that on production, it will also fail. I tried it out with the integration test, and it will refuse to insert a NULL value with type NUMERIC into a STRING column:
failed to insert values using DML: spanner: code = "InvalidArgument", desc = "Value has type NUMERIC which cannot be inserted into column String, which has type STRING [at 1:50]\\nINSERT INTO Types (RowId, `String`) VALUES (@id, @value)
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
Removes the restriction that nil valued parameters must be typed. The current restriction prevents the usage of DML statements with one or more untyped nil parameter values to be used to insert/update a value to NULL. Cloud Spanner does not require all parameters to be typed, only when it would otherwise be ambiguous what type should be used (e.g. non-NULL STRING/BYTES values).
This restriction also prevents the usage of the go/sql driver with gorm, as gorm will generate statements with untyped nil parameter values.
Fixes #4481