第26回/Issueのフィルタ仕様の問題の確認・改善

第26回: solysombra73, tohosaku, nagase

第27回: solysombra73, ishikawa999, tohosaku, nagase

第28回: solysombra73, nagase, tohosaku

概要:

fixturesをロード後、IssueのフィルタでカスタムフィールドSearchable fieldを追加して、各条件で検索をかけると、合計値が合わない

ステータス: すべて

フィルタなしだと 14件

含む(contains, "125") 2件 (カスタムフィールドに値が入っていて、かつ、引数を含むもの)

含まない(doesn't contain, "125") 1=>2件 (カスタムフィールドに値が入っていて、かつ、引数を含まないもの)

=> ※ここに、カスタムフィールドに入ってないものも含めて欲しい

すべて(any): 3件 (カスタムフィールドに値が入っているもの、もしくは、空白でないもの)

なし(none): 10=>11件 (カスタムフィールドに値が入っていないもの、もしくは、空白のもの)

=> 11件でないとおかしいはず

矢印右は、Searchable fieldのトラッカーに"Feature request"を追加後

環境設定:

bundle exec rake db:fixtures:load

やること:

/var/lib/redmine/app/models/query.rb

code:rb

custom_values.value IS NULL OR custom_values.value = ''"

code:sql

SELECT COUNT(*) FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" INNER JOIN "issue_statuses" ON "issue_statuses"."id" = "issues"."status_id" WHERE (projects.status <> 9 AND projects.status <> 10 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')) AND ((issues.status_id IS NOT NULL) AND issues.id IN (SELECT issues.id FROM issues LEFT OUTER JOIN custom_values ON custom_values.customized_type='Issue' AND custom_values.customized_id=issues.id AND custom_values.custom_field_id=2 WHERE (custom_values.value IS NULL OR custom_values.value = '') AND (((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 2)) AND (EXISTS (SELECT 1 FROM custom_fields ifa WHERE ifa.is_for_all = TRUE AND ifa.id = 2) OR issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 2)) AND (projects.status <> 9 AND projects.status <> 10 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking'))))))

pgAdminより

code:sql

SELECT * FROM custom_values

WHERE

customized_type = 'Issue' AND

custom_field_id = 2;

=> 結果

code:csv

"id","customized_type","customized_id","custom_field_id","value"

6,"Issue",3,2,"125"

4,"Issue",1,2,"125"

5,"Issue",2,2,""

9,"Issue",7,2,"this is a stringforcustomfield search"

冒頭ですべてとなしの合計が合わなかったのは、Fixturesの方でSearchable fieldのトラッカーに"Feature request"が入ってなかったのが原因のよう。

含む + 含まない は、暗黙のうちにカスタムフィールドの存在を暗黙の前提としている。 => 「含まない」にカスタムフィールドが存在していない場合も含めた方が妥当か？

custom_values の値に null が入りこんでいるケースでは、「含む」「含まない」の合計が合致しないと思われる。nullが入りこむことは想定されているのか？

含まない =>

code:rb

"!~" => :label_not_contains,

以下の既存のチケットでは、別の条件("なし"の場合)でNULLを検索に含める対応を行っていたようなので、こちらを参考にする。

修正コード:

code:ruby

when "!~"

# sql = sql_contains("#{db_table}.#{db_field}", value.first, :match => false)

sql = "#{db_table}.#{db_field} IS NULL"

sql += " OR " + sql_contains("#{db_table}.#{db_field}", value.first, :match => false)

テスト:

カスタムフィールドに対するフィルタ条件のテストは作成されていない模様

code:rb

def test_operator_does_not_contain_on_text_custom_field

query = IssueQuery.new(:name => '_')

# query.add_filter('cf_2', '!*', [])

result = find_issues_with_query(query)

assert_equal 10, result.size

end

見ているデータベースがおかしい(development環境のデータベースを見ている)？

code:sh

@advisory_locks_enabled=true,

@config=

{:adapter=>"postgresql",

:database=>"app_test",

:username=>"postgres",

:password=>"postgres",

:host=>"postgresdb",

:encoding=>"utf8"},

@connection=

@connection_parameters=

今後のアクション:

Scrapboxの整理

GitHub Issueの起票

GitHub PRの作成(一旦、Draftで作成)

10/30 長瀬追記

~~ ここから第27回 ~~~~~~~~~~~~

custom_values.value IS NULL OR custom_values.value NOT ILIKE '%125%'

EXISTS (SELECT ct.id FROM issues ct LEFT OUTER JOIN custom_values ON custom_values.customized_type='Issue' AND custom_values.customized_id=ct.id AND custom_values.custom_field_id=2 WHERE issues.id = ct.id AND (custom_values.value IS NULL OR custom_values.value NOT ILIKE '%125%') AND (((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 2)) AND (EXISTS (SELECT 1 FROM custom_fields ifa WHERE ifa.is_for_all = TRUE AND ifa.id = 2) OR issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 2)) AND (projects.status <> 9 AND projects.status <> 10 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')))))

"(issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=FALSE)) AND EXISTS (SELECT ct.id FROM issues ct LEFT OUTER JOIN custom_values ON custom_values.customized_type='Issue' AND custom_values.customized_id=ct.id AND custom_values.custom_field_id=2 WHERE issues.id = ct.id AND (custom_values.value IS NULL OR custom_values.value NOT ILIKE '%125%') AND (((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 2)) AND (EXISTS (SELECT 1 FROM custom_fields ifa WHERE ifa.is_for_all = TRUE AND ifa.id = 2) OR issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 2)) AND (((projects.status <> 9 AND projects.status <> 10 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')) AND (((projects.is_public = TRUE AND projects.id NOT IN (SELECT project_id FROM members WHERE user_id IN (6,13))) AND ((issues.is_private = FALSE)))))))))"

EXISTS (SELECT ct.id FROM issues ct LEFT OUTER JOIN custom_values ON custom_values.customized_type='Issue' AND custom_values.customized_id=ct.id AND custom_values.custom_field_id=2 WHERE issues.id = ct.id AND (custom_values.value IS NULL OR custom_values.value NOT ILIKE '%125%') AND (((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 2)) AND (EXISTS (SELECT 1 FROM custom_fields ifa WHERE ifa.is_for_all = TRUE AND ifa.id = 2) OR issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 2)) AND (((projects.status <> 9 AND projects.status <> 10 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')) AND (((projects.is_public = TRUE AND projects.id NOT IN (SELECT project_id FROM members WHERE user_id IN (6,13))) AND ((issues.is_private = FALSE)))))))))

EXISTS (SELECT ct.id FROM issues ct LEFT OUTER JOIN custom_values ON custom_values.customized_type='Issue' AND custom_values.customized_id=ct.id AND custom_values.custom_field_id=2 WHERE issues.id = ct.id AND (custom_values.value IS NULL OR custom_values.value NOT ILIKE '%125%') AND (((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 2)) AND (EXISTS (SELECT 1 FROM custom_fields ifa WHERE ifa.is_for_all = TRUE AND ifa.id = 2) OR issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 2)) AND (projects.status <> 9 AND projects.status <> 10 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')))))

(途中コードのバックアップ)

code:ruby

def test_operator_does_not_contain_on_text_custom_field

# with_current_user User.find(1) do

query = IssueQuery.new(:name => '_')

# query.add_filter('cf_2', '!*', [])

# binding.pry

result = find_issues_with_query(query)

# result = query.issues

binding.pry

assert_equal 6, result.size

# end

end

標題: カスタムフィールドを含むチケットのフィルタの結果に一貫性がない。

再現手順

code:text

1. 動作確認環境を用意する

$ RAILS_ENV=development bin/rails db:fixtures:load

3. フィルタから "Status" のチェックを外し、"Add filter" で "Tracker" を選択し、Searchable fieldカスタムフィールドが利用可能なトラッカー(Bug・Support request)を "is" で含めて検索して、チケットが8件であることを確認する

4. さらに "Add filter" で "Searchable field" を選択し、条件で "contains" を選択し125を入力する

結果が2件となる

5. 条件を "doesn't contain" に変更し適用する

結果が1件となる。

4,5 の件数の合計が3件となりチケットの総数8件と一致しない

https://gyazo.com/2640736caadacdfe59f8754894113cd2

https://gyazo.com/2f48188a80187da14b5a91411410c2cc

https://gyazo.com/eb3ffcc9bddfc094e52ae99945a5c49b

期待する挙動

code:text

"contains" の結果と "doesn't contain"の結果の和がチケットの総数と合致する。

条件を "is" と "is not" とした場合は結果の和とチケットの総数が合致する。

考えられる原因

code:text

"doesn't contain" の場合に カスタムフィールド "Searchable filter" が存在していないチケットが無視されている

挙動を “is”, “is not” とそろえるために、”doesn’t contain” の結果に、カスタムフィールドを持たないチケットの件数を含めるべきではないでしょうか。

Inconsistent results for filtering issues containing custom fields.

How to reproduce

code:text

1. Prepare a working environment

$ RAILS_ENV=development bin/rails db:fixtures:load

3. From filters, check off "Status" then add "Tracker" filter with operator "is" and value "Bug" and "Support request" (Because "Searchable field" custom field is only available in those trackers).

The result is 8 issues.

4. Select "Searchable field" from "Add filter", select operator "contains" and value 125.

The result will be 2 issues.

5. Change the operator to "doesn't contain" and apply it.

The result is 1.

The total number of cases in 4 and 5 is 3 which does not match the total number of issues (6)

Expected behavior

code:text

The sum of the results of "contains" and "doesn't contain" matches the total number of issues.

If the condition is "is" and "is not", the sum of the results matches the total number of issues.

Possible Causes

code:text

The filter ignore issues which the custom field "Searchable filter" is not present when the filter is "doesn't contain".

To make the behavior consistent with "is" and "is not", the "doesn't contain" result should include the number of issues that do not have the custom field.

~~ ここから第28回 ~~~~~~~~~~~~

昨晩のCI実行時に、以下のUserQueryTestでエラーが出ているようでしたので、こちらは追加で対応が必要かもしれません。

code:text

Failure:

rails test test/unit/user_query_test.rb:93

画面上でも問題確認(somenetが含まれてはいけないのに、含まれてしまっている)

binding.pryを変更箇所に仕込むと以下が返ってきていた

code:sql

email_addresses.address IS NULL OR email_addresses.address NOT ILIKE '%somenet%'

code:sql

SELECT "users".* FROM "users" WHERE "users"."type" = 'User' AND (users.status <> 0) AND ((users.status IN ('1')) AND EXISTS

(SELECT 1 FROM email_addresses WHERE

email_addresses.user_id = users.id AND

email_addresses.address IS NULL OR email_addresses.address NOT ILIKE '%somenet%')

) ORDER BY users.login ASC LIMIT 25 OFFSET 0;

"=" => :label_equals, => is

"!" => :label_not_equals, => is not

"!*" => :label_none, => none

"*" => :label_any, => any

"~" => :label_contains,

"!~" => :label_not_contains,

参考にしたところは、後ろに何か書いてあった

code:rb

when "!*"

sql = "#{db_table}.#{db_field} IS NULL"

code:ruby

when "!~"

sql = sql_contains("#{db_table}.#{db_field}", value.first, :match => false)