Actions
Defect #39443
closed
Invalid statement query error on MSSQL when role filter is used in issues query
Start date:
Due date:
% Done:
0%
Estimated time:
Resolution:
Fixed
Affected version:
Description
The following tests are failing on MS SQL. Unfortunately, I don't have a MS SQL local environment (M1 Mac) to take a quick look to it.
At the same time, maybe it's better to drop support for SQL on Redmine 6.0 and stick only with MySQL and PostgreSQL.
Error:
QueryTest#test_assigned_to_role_filter_should_search_assigned_to_for_users_without_the_Role:
Query::StatementInvalid: TinyTds::Error: An expression of non-boolean type specified in a context where a condition is expected, near ','.
app/models/issue_query.rb:424:in `rescue in issues'
app/models/issue_query.rb:380:in `issues'
test/unit/query_test.rb:161:in `block in assert_query_result'
test/unit/query_test.rb:160:in `assert_query_result'
test/unit/query_test.rb:2898:in `block in <class:QueryTest>'
rails test test/unit/query_test.rb:2894
................................................................E
Error:
QueryTest#test_assigned_to_role_filter_should_search_assigned_to_for_users_with_the_Role_on_the_issue_project:
Query::StatementInvalid: TinyTds::Error: An expression of non-boolean type specified in a context where a condition is expected, near ','.
app/models/issue_query.rb:424:in `rescue in issues'
app/models/issue_query.rb:380:in `issues'
test/unit/query_test.rb:161:in `block in assert_query_result'
test/unit/query_test.rb:160:in `assert_query_result'
test/unit/query_test.rb:2883:in `block in <class:QueryTest>'
rails test test/unit/query_test.rb:2877
...............................................................................E
Error:
QueryTest#test_assigned_to_role_filter_should_return_an_empty_set_with_empty_role:
Query::StatementInvalid: TinyTds::Error: An expression of non-boolean type specified in a context where a condition is expected, near ','.
app/models/issue_query.rb:424:in `rescue in issues'
app/models/issue_query.rb:380:in `issues'
test/unit/query_test.rb:161:in `block in assert_query_result'
test/unit/query_test.rb:160:in `assert_query_result'
test/unit/query_test.rb:2891:in `block in <class:QueryTest>'
rails test test/unit/query_test.rb:2886
...................................E
Error:
QueryTest#test_assigned_to_role_filter_should_search_assigned_to_for_users_with_the_Role:
Query::StatementInvalid: TinyTds::Error: An expression of non-boolean type specified in a context where a condition is expected, near ','.
app/models/issue_query.rb:424:in `rescue in issues'
app/models/issue_query.rb:380:in `issues'
test/unit/query_test.rb:161:in `block in assert_query_result'
test/unit/query_test.rb:160:in `assert_query_result'
test/unit/query_test.rb:2874:in `block in <class:QueryTest>'
rails test test/unit/query_test.rb:2870
.............................................................E
Error:
QueryTest#test_assigned_to_role_filter_should_return_issues_with_!_empty_role:
Query::StatementInvalid: TinyTds::Error: An expression of non-boolean type specified in a context where a condition is expected, near ','.
app/models/issue_query.rb:424:in `rescue in issues'
app/models/issue_query.rb:380:in `issues'
test/unit/query_test.rb:161:in `block in assert_query_result'
test/unit/query_test.rb:160:in `assert_query_result'
test/unit/query_test.rb:2920:in `block in <class:QueryTest>'
rails test test/unit/query_test.rb:2915
Related issues
Updated by Pavel Rosický 12 months ago
I'm not a mssql user, but it should be simple to fix... I'll investigate...
Updated by Marius BĂLTEANU 12 months ago
Pavel Rosický wrote in #note-1:
I'm not a mssql user, but it should be simple to fix... I'll investigate...
Thanks Pavel!
Updated by Pavel Rosický 12 months ago
Marius BĂLTEANU the issue is caused by this optimization #38319
I couldn't confirm the performance issue on MySQL 8 (all 3 variants behave exactly the same) and I don't have a large postgres db.
in order to fix the issue we can either
1/ revert the original change
2/ or here's an alternative approach
SELECT SQL_NO_CACHE
issues.id
FROM
issues
WHERE
EXISTS (
SELECT
1
FROM
members
INNER JOIN member_roles ON members.id = member_roles.member_id
WHERE
member_roles.role_id = 3
AND issues.project_id = members.project_id
AND members.user_id = issues.assigned_to_id)
diff --git a/app/models/issue_query.rb b/app/models/issue_query.rb
index 235bf2fbe..08240c117 100644
--- a/app/models/issue_query.rb
+++ b/app/models/issue_query.rb
@@ -567,10 +567,10 @@ class IssueQuery < Query
sw = operator == "!*" ? 'NOT' : ''
nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
subquery =
- "SELECT DISTINCT #{Member.table_name}.user_id" +
+ "SELECT 1" +
" FROM #{Member.table_name}" +
- " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id"
- "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (#{subquery}))"
+ " WHERE #{Issue.table_name}.project_id = #{Member.table_name}.project_id AND #{Member.table_name}.user_id = #{Issue.table_name}.assigned_to_id"
+ "(#{nl} #{sw} EXISTS (#{subquery}))"
when "=", "!"
role_cond =
if value.any?
@@ -581,10 +581,10 @@ class IssueQuery < Query
sw = operator == "!" ? 'NOT' : ''
nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
subquery =
- "SELECT DISTINCT #{Member.table_name}.user_id, #{Member.table_name}.project_id" +
- " FROM #{Member.table_name}, #{MemberRole.table_name}" +
- " WHERE #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}"
- "(#{nl} (#{Issue.table_name}.assigned_to_id, #{Issue.table_name}.project_id) #{sw} IN (#{subquery}))"
+ "SELECT 1" +
+ " FROM #{Member.table_name} inner join #{MemberRole.table_name} on members.id = member_roles.member_id" +
+ " WHERE #{Issue.table_name}.project_id = #{Member.table_name}.project_id AND #{Member.table_name}.user_id = #{Issue.table_name}.assigned_to_id AND #{role_cond}"
+ "(#{nl} #{sw} EXISTS (#{subquery}))"
end
end
I'm not sure if it makes any difference on postgres. Perhaps the original reporter could retest it.
Updated by Marius BĂLTEANU 12 months ago
- Related to Patch #38319: Optimize IssueQuery#sql_for_assigned_to_role_field for PostgreSQL performance added
Updated by Marius BĂLTEANU 12 months ago
I can confirm that the tests pass now on my custom CI.
Vincent Robert, can you check this fix against your PostgreSQL data?
Updated by Vincent Robert 12 months ago
I have just tested this change with Postgres and a large dataset. Everything ran fine, and performance is good. This simpler code seems to work perfectly.
Updated by Marius BĂLTEANU 12 months ago
- Subject changed from Tests failing on MS SQL to Invalid statement query error on MSSQL
- Category set to Database
- Status changed from New to Resolved
- Assignee set to Marius BĂLTEANU
- Resolution set to Fixed
Updated by Marius BĂLTEANU 12 months ago
- Subject changed from Invalid statement query error on MSSQL to Invalid statement query error on MSSQL when role filter is used in issues query
Actions