Redmine - Defect #39443
Invalid statement query error on MSSQL when role filter is used in issues query2023-10-30 11:01 - Marius B

LTEANU

ステータス:

Closed

開始日:

優先度:

Normal期日:担当者:

Marius B

カテゴリ:

Database

対象バージョン:5.1.1

Resolution:

Fixed

LTEANU

進捗率:

0%

予定工数:

0.00時間Affected version:

説明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 toit.
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
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
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
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
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:2024-10-10

1/3

aconditionisaconditionisaconditionisaconditionisQuery::StatementInvalid: TinyTds::Error: An expression of non-boolean
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

typespecified

inacontext

where

関連するチケット:
関連している Redmine - Patch #38319: Optimize IssueQuery#sql̲fo...

Closed

関係しているリビジョン
リビジョン 22417 - 2023-11-11 07:00 - Marius B

LTEANUFix an expression of non-boolean type specified in a context where a condition is expected when MSSQL is used (#39443).
Patch by Pavel Rosický.
リビジョン 22420 - 2023-11-11 08:12 - Marius B

LTEANU

Merged r22417 from trunk to 5.1-stable (#39443).

履歴#1 - 2023-11-06 21:49 - Pavel Rosický
I'm not a mssql user, but it should be simple to fix... I'll investigate...
#2 - 2023-11-06 22:19 - Marius B

LTEANU

Pavel Rosický wrote in #note-1:
I'm not a mssql user, but it should be simple to fix... I'll investigate...

Thanks Pavel!
#3 - 2023-11-07 00:07 - Pavel RosickýMarius B

LTEANUthe 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
issuesWHERE
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 < Querysw = operator == "!*" ? 'NOT' : ''nl = operator == "!*" ? "#{Issue.table̲name}.assigned̲to̲id
subquery ="SELECT DISTINCT #{Member.table̲name}.user̲id" ++"SELECT 1" +2024-10-10

IS

NULL

OR":''

2/3

aconditionis" 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.tab
signed̲to̲id"+"(#{nl} #{sw} EXISTS (#{subquery}))"when "=", "!"role̲cond =if value.any?
@@ -581,10 +581,10 @@class IssueQuery < Querysw = 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.tabsigned̲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.
#4 - 2023-11-07 08:45 - Marius B

LTEANU- 対象バージョン を 5.1.1 にセット
#5 - 2023-11-07 08:46 - Marius B

LTEANU- 関連している Patch #38319: Optimize IssueQuery#sql̲for̲assigned̲to̲role̲field for PostgreSQL performance を追加#6 - 2023-11-07 08:47 - Marius B

LTEANUI can confirm that the tests pass now on my custom CI.
Vincent Robert, can you check this fix against your PostgreSQL data?
#7 - 2023-11-07 15:53 - Vincent Robert
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.
#8 - 2023-11-11 07:02 - Marius B

LTEANU- 題名 を Tests failing on MS SQL から Invalid statement query error on MSSQL に変更- カテゴリ を Database にセット
- ステータス を New から Resolved に変更- 担当者 を Marius B

LTEANU にセット

- Resolution を Fixed にセット
#9 - 2023-11-11 08:12 - Marius B

LTEANU- ステータス を Resolved から Closed に変更#10 - 2023-11-11 08:14 - Marius B

LTEANU- 題名 を Invalid statement query error on MSSQL から Invalid statement query error on MSSQL when role filter is used in issues query に変更

2024-10-10

3/3