New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
sql: prepared selects that filter a column with a constant casted to the wrong type fail to return the expected results #81315
Comments
We saw this bug in a support case, and captured statement bundles of the working and non-working versions of the query. In that example, the decimal value was |
Fixed a bug where we were incorrectly using the placeholder fast path if the type of the placholder did not match the type of the column. We should disallow mixed-type comparisons for the placeholder fast path because it results in incorrect encodings, and therefore incorrect results from the scan. We now disable the placeholder fast path if the types do not match. Fixes cockroachdb#81315 Release note (bug fix): Fixed a bug in which some prepared statements could result in incorrect results when executed. This could occur when the prepared statement included an equality comparison between an index column and a placeholder, and the placholder was cast to a type that was different from the column type. For example, if column a was of type DECIMAL, the following prepared query could produce incorrect results when executed: SELECT * FROM t_dec WHERE a = $1::INT8;
Fixed a bug where we were incorrectly using the placeholder fast path if the type of the placholder did not match the type of the column. We should disallow mixed-type comparisons for the placeholder fast path because it results in incorrect encodings, and therefore incorrect results from the scan. We now disable the placeholder fast path if the types do not match. Fixes cockroachdb#81315 Release note (bug fix): Fixed a bug in which some prepared statements could result in incorrect results when executed. This could occur when the prepared statement included an equality comparison between an index column and a placeholder, and the placholder was cast to a type that was different from the column type. For example, if column a was of type DECIMAL, the following prepared query could produce incorrect results when executed: SELECT * FROM t_dec WHERE a = $1::INT8;
Fixed a bug where we were incorrectly using the placeholder fast path if the type of the placholder did not match the type of the column. We should disallow mixed-type comparisons for the placeholder fast path because it results in incorrect encodings, and therefore incorrect results from the scan. We now disable the placeholder fast path if the types do not match. Fixes cockroachdb#81315 Release note (bug fix): Fixed a bug in which some prepared statements could result in incorrect results when executed. This could occur when the prepared statement included an equality comparison between an index column and a placeholder, and the placholder was cast to a type that was different from the column type. For example, if column a was of type DECIMAL, the following prepared query could produce incorrect results when executed: SELECT * FROM t_dec WHERE a = $1::INT8;
81331: opt: do not use placeholder fast path if types do not match r=rytaft a=rytaft Fixed a bug where we were incorrectly using the placeholder fast path if the type of the placholder did not match the type of the column. We should disallow mixed-type comparisons for the placeholder fast path because it results in incorrect encodings, and therefore incorrect results from the scan. We now disable the placeholder fast path if the types do not match. Fixes #81315 Release note (bug fix): Fixed a bug in which some prepared statements could result in incorrect results when executed. This could occur when the prepared statement included an equality comparison between an index column and a placeholder, and the placholder was cast to a type that was different from the column type. For example, if column `a` was of type `DECIMAL`, the following prepared query could produce incorrect results when executed: ``` SELECT * FROM t_dec WHERE a = $1::INT8; ``` Co-authored-by: Rebecca Taft <becca@cockroachlabs.com>
81253: sql: inverted index accelerate json ? string, json ?& array, and json ?| array ops r=jordanlewis a=jordanlewis Closes #81114 This commit adds support for accelerating the json ? string operator when the json argument is a column that has an inverted index over it. Release note (sql change): the json ? string operator is now index accelerated if there is an inverted index over the json column referred to on the left hand side of the expression. 81331: opt: do not use placeholder fast path if types do not match r=rytaft a=rytaft Fixed a bug where we were incorrectly using the placeholder fast path if the type of the placholder did not match the type of the column. We should disallow mixed-type comparisons for the placeholder fast path because it results in incorrect encodings, and therefore incorrect results from the scan. We now disable the placeholder fast path if the types do not match. Fixes #81315 Release note (bug fix): Fixed a bug in which some prepared statements could result in incorrect results when executed. This could occur when the prepared statement included an equality comparison between an index column and a placeholder, and the placholder was cast to a type that was different from the column type. For example, if column `a` was of type `DECIMAL`, the following prepared query could produce incorrect results when executed: ``` SELECT * FROM t_dec WHERE a = $1::INT8; ``` Co-authored-by: Jordan Lewis <jordanthelewis@gmail.com> Co-authored-by: Rebecca Taft <becca@cockroachlabs.com>
Fixed a bug where we were incorrectly using the placeholder fast path if the type of the placholder did not match the type of the column. We should disallow mixed-type comparisons for the placeholder fast path because it results in incorrect encodings, and therefore incorrect results from the scan. We now disable the placeholder fast path if the types do not match. Fixes #81315 Release note (bug fix): Fixed a bug in which some prepared statements could result in incorrect results when executed. This could occur when the prepared statement included an equality comparison between an index column and a placeholder, and the placholder was cast to a type that was different from the column type. For example, if column a was of type DECIMAL, the following prepared query could produce incorrect results when executed: SELECT * FROM t_dec WHERE a = $1::INT8;
Fixed a bug where we were incorrectly using the placeholder fast path if the type of the placholder did not match the type of the column. We should disallow mixed-type comparisons for the placeholder fast path because it results in incorrect encodings, and therefore incorrect results from the scan. We now disable the placeholder fast path if the types do not match. Fixes #81315 Release note (bug fix): Fixed a bug in which some prepared statements could result in incorrect results when executed. This could occur when the prepared statement included an equality comparison between an index column and a placeholder, and the placholder was cast to a type that was different from the column type. For example, if column a was of type DECIMAL, the following prepared query could produce incorrect results when executed: SELECT * FROM t_dec WHERE a = $1::INT8;
Fixed a bug where we were incorrectly using the placeholder fast path if the type of the placholder did not match the type of the column. We should disallow mixed-type comparisons for the placeholder fast path because it results in incorrect encodings, and therefore incorrect results from the scan. We now disable the placeholder fast path if the types do not match. Fixes cockroachdb#81315 Release note (bug fix): Fixed a bug in which some prepared statements could result in incorrect results when executed. This could occur when the prepared statement included an equality comparison between an index column and a placeholder, and the placholder was cast to a type that was different from the column type. For example, if column a was of type DECIMAL, the following prepared query could produce incorrect results when executed: SELECT * FROM t_dec WHERE a = $1::INT8;
Add a flag to disable this optimization in case it bites us again. Informs cockroachdb#81315 Release note: none
Describe the problem
After upgrading from 21.1.10 to 21.2.7, prepared statements on a table with a
DECIMAL
primary key do not return the matching rows if theWHERE
clause casts the primary key parameter toINT::8
. This statement previously worked on a 21.1.10 cluster.To Reproduce
This small test case on a demo cluster reproduces the issue
Expected behavior
EXECUTE stmt1 (1);
is expected to return the (1, 100) row on 21.2. The row is returned if these steps are reproduced on a 21.1 cluster.Additional data / screenshots
Environment:
Jira issue: CRDB-15244
The text was updated successfully, but these errors were encountered: