Summary

      When the request type is set to a non-latin word, in some cases, the search by "Customer Request Type" returns error. This issue is more like an regression in JSD as it cannot be reproduced in JSD 3.2.x

      Environment

      • JSD 3.3
      • Database encoding/collation settings are configured as recommended
      • JIRA and JVM's encoding related properties are set to UTF-8

      Steps to Reproduce

      1. Create a sample Service Desk with the project key = "SITP"
      2. Change the name of a request type to "Шаблонные сайты"
      3. Change the name of another request type to "解决 系统问题"
      4. Open issue navigator and execute the following JQLs :
        "Customer Request Type" = "解决 系统问题 (SITP)" 
        
        "Customer Request Type" = "Шаблонные сайты (SITP)" 
        

      Expected Results

      Both JQL get executed without any error.

      Actual Results

      The first JQL returned correct result :

      but the second failed with following error

      Notes

      On JSD 3.2, when user picks the request type from auto suggestion list, the value is converted into a key :

      on JSD 3.3 the value is not converted and it remains as what we see in the suggestion list :

      Workaround

      Use the following SQL (tested for PostgreSQL) to identify the key of each request type and use it in the JQL instead:

      select vp."KEY" || '/' || vpf."KEY" as key, vpf."NAME" 
      from project p inner join "AO_54307E_VIEWPORT" vp on p.id=vp."PROJECT_ID" inner join "AO_54307E_VIEWPORTFORM" vpf on vp."ID"=vpf."VIEWPORT_ID" 
      where p.pname='<project name>';
      

        1. 2017-02-24_auto-suggestion.png
          2017-02-24_auto-suggestion.png
          69 kB
        2. 2017-02-24_jql-error.png
          2017-02-24_jql-error.png
          24 kB
        3. 2017-02-24_jql-ok-chinese.png
          2017-02-24_jql-ok-chinese.png
          60 kB
        4. 2017-02-24_jsd-3.2.png
          2017-02-24_jsd-3.2.png
          43 kB

            [JSDSERVER-4860] Error in searching by "Customer Request Type"

            cmao

            A bit more follow up here.

            I was testing on my Postgres instance, and with the Postgres default locale of C, this did not work, as you described. I did some digging, and discovered that this locale of C only understands ASCII, and is therefore unable to upper or lowercase cyrillic characters.

            You can find your current cluster locale, by running the follow command on database

            SHOW LC_COLLATE;
            

            I was able to test by initialising a new database cluster using: initdb --locale=ru_RU.ISO8859-5. You can get a list of the locales on system using "locale -a". I chose the RU ISO locale, as it seems to be the most obvious to support Russian and other characters.

            Creating the cluster was detailed https://www.postgresql.org/docs/9.5/static/creating-cluster.html

            Once I did this, and started up this database server, when I ran

            select lower('Шаблонные сайты');
            

            it actually returned the correct result of "шаблонные сайты"

            It seems that the real underlying problem here is that the Postgres collation must be using C locale, or another locale that does not understand how to upper/lower case cyrillic characters and therefore does not do it, meaning the SELECT fails.

            In code, we can not do the lowercase of the constant in Java code, and pass the text directly to the database as a lower function, so if the collation does not understand it would essentially be doing a direct EQUALS check, instead of EQUALS_IGNORE_CASE, but in most cases that should return the result you need.

            However to fix the issue properly, would require creating a new database cluster in required locale, and restoring data onto that instance.

            Regards
            Matt
            JIRA Service Desk developer

            NB: My links are all over the place in regards to Postgres versions, but they worked for my testing, but offcourse you probably should find the correct docs for your version just to be 100% certain

            Matthew McMahon (Inactive) added a comment - - edited cmao A bit more follow up here. I was testing on my Postgres instance, and with the Postgres default locale of C, this did not work, as you described. I did some digging, and discovered that this locale of C only understands ASCII, and is therefore unable to upper or lowercase cyrillic characters. You can find your current cluster locale, by running the follow command on database SHOW LC_COLLATE; I was able to test by initialising a new database cluster using: initdb --locale=ru_RU.ISO8859-5 . You can get a list of the locales on system using "locale -a". I chose the RU ISO locale, as it seems to be the most obvious to support Russian and other characters. Creating the cluster was detailed https://www.postgresql.org/docs/9.5/static/creating-cluster.html Once I did this, and started up this database server, when I ran select lower( 'Шаблонные сайты' ); it actually returned the correct result of "шаблонные сайты" It seems that the real underlying problem here is that the Postgres collation must be using C locale, or another locale that does not understand how to upper/lower case cyrillic characters and therefore does not do it, meaning the SELECT fails. In code, we can not do the lowercase of the constant in Java code, and pass the text directly to the database as a lower function, so if the collation does not understand it would essentially be doing a direct EQUALS check, instead of EQUALS_IGNORE_CASE, but in most cases that should return the result you need. However to fix the issue properly, would require creating a new database cluster in required locale, and restoring data onto that instance. Regards Matt JIRA Service Desk developer NB: My links are all over the place in regards to Postgres versions, but they worked for my testing, but offcourse you probably should find the correct docs for your version just to be 100% certain

            It looks like can make this query pass, byou explicitly passing lower as the function on both sides.

            But in this case it appears the real problem is that the database collation for Postgres does not know how to upper/lower case the Cyrillic cases.

            I found a stack overflow on topic at http://stackoverflow.com/questions/8638576/upper-doesnt-work-with-cyrillic-symbols-in-postgresql-8-2-database

            I would guess that if want proper ignore case matching the database collation used may not be able to understand Cyrillic and therefore not function. In code we can make it basically an equals check in this case, but proper fix might involve creating a new database cluster with a collation that understands Cyrillic and perform backup/restore of data.

            Matt

            Matthew McMahon (Inactive) added a comment - It looks like can make this query pass, byou explicitly passing lower as the function on both sides. But in this case it appears the real problem is that the database collation for Postgres does not know how to upper/lower case the Cyrillic cases. I found a stack overflow on topic at http://stackoverflow.com/questions/8638576/upper-doesnt-work-with-cyrillic-symbols-in-postgresql-8-2-database I would guess that if want proper ignore case matching the database collation used may not be able to understand Cyrillic and therefore not function. In code we can make it basically an equals check in this case, but proper fix might involve creating a new database cluster with a collation that understands Cyrillic and perform backup/restore of data. Matt

            Interesting.

            It seems that the query is built, and inside Java it lowercases the name, which in some locales is incorrect at the database level.

            I believe the best thing to do is lowercase both sides at the database level, so it can perform the correct assertions.

            As in result in the query that worked for you

            lower(rt."NAME") = lower('Шаблонные сайты')
            

            Matthew McMahon (Inactive) added a comment - - edited Interesting. It seems that the query is built, and inside Java it lowercases the name, which in some locales is incorrect at the database level. I believe the best thing to do is lowercase both sides at the database level, so it can perform the correct assertions. As in result in the query that worked for you lower(rt. "NAME" ) = lower( 'Шаблонные сайты' )

            Hi mmcmahon,
            I have tested on PostgreSQL 9.5 database.
            The following SQL query gives me the expected results :

            select rt.*
            from "AO_54307E_VIEWPORTFORM" rt inner join "AO_54307E_VIEWPORT" vp on rt."VIEWPORT_ID"=vp."ID"
            where rt."VIEWPORT_ID"=6 and lower(rt."NAME") = lower('Шаблонные сайты');
            

            I notice that following SQL query is executed by JIRA when calling the getRequestTypeByName method in RequestTypeQStoreImpl class :

            select "AO_54307E_VIEWPORTFORM"."CALL_TO_ACTION", "AO_54307E_VIEWPORTFORM"."DESCRIPTION", "AO_54307E_VIEWPORTFORM"."FORM_ORDER", "AO_54307E_VIEWPORTFORM"."ICON", "AO_54307E_VIEWPORTFORM"."ICON_ID", "AO_54307E_VIEWPORTFORM"."ID", "AO_54307E_VIEWPORTFORM"."INTRO", "AO_54307E_VIEWPORTFORM"."ISSUE_TYPE_ID", "AO_54307E_VIEWPORTFORM"."KEY", "AO_54307E_VIEWPORTFORM"."NAME", "AO_54307E_VIEWPORTFORM"."VIEWPORT_ID" from "public"."AO_54307E_VIEWPORTFORM" "AO_54307E_VIEWPORTFORM" inner join "public"."AO_54307E_VIEWPORT" "AO_54307E_VIEWPORT" on "AO_54307E_VIEWPORTFORM"."VIEWPORT_ID" = "AO_54307E_VIEWPORT"."ID" where "AO_54307E_VIEWPORTFORM"."VIEWPORT_ID" = 6 and lower("AO_54307E_VIEWPORTFORM"."NAME") = 'шаблонные сайты' order by "AO_54307E_VIEWPORT"."NAME" asc, "AO_54307E_VIEWPORTFORM"."FORM_ORDER" asc
            

            which doesn't return any result. However if I change the condition to lower("AO_54307E_VIEWPORTFORM"."NAME") = 'Шаблонные сайты' the query will give the expected result.

            Cheers,
            Chen

            Chen Mao (Inactive) added a comment - Hi mmcmahon , I have tested on PostgreSQL 9.5 database. The following SQL query gives me the expected results : select rt.* from "AO_54307E_VIEWPORTFORM" rt inner join "AO_54307E_VIEWPORT" vp on rt. "VIEWPORT_ID" =vp. "ID" where rt. "VIEWPORT_ID" =6 and lower(rt. "NAME" ) = lower( 'Шаблонные сайты' ); I notice that following SQL query is executed by JIRA when calling the getRequestTypeByName method in RequestTypeQStoreImpl class : select "AO_54307E_VIEWPORTFORM" . "CALL_TO_ACTION" , "AO_54307E_VIEWPORTFORM" . "DESCRIPTION" , "AO_54307E_VIEWPORTFORM" . "FORM_ORDER" , "AO_54307E_VIEWPORTFORM" . "ICON" , "AO_54307E_VIEWPORTFORM" . "ICON_ID" , "AO_54307E_VIEWPORTFORM" . "ID" , "AO_54307E_VIEWPORTFORM" . "INTRO" , "AO_54307E_VIEWPORTFORM" . "ISSUE_TYPE_ID" , "AO_54307E_VIEWPORTFORM" . "KEY" , "AO_54307E_VIEWPORTFORM" . "NAME" , "AO_54307E_VIEWPORTFORM" . "VIEWPORT_ID" from " public " . "AO_54307E_VIEWPORTFORM" "AO_54307E_VIEWPORTFORM" inner join " public " . "AO_54307E_VIEWPORT" "AO_54307E_VIEWPORT" on "AO_54307E_VIEWPORTFORM" . "VIEWPORT_ID" = "AO_54307E_VIEWPORT" . "ID" where "AO_54307E_VIEWPORTFORM" . "VIEWPORT_ID" = 6 and lower( "AO_54307E_VIEWPORTFORM" . "NAME" ) = 'шаблонные сайты' order by "AO_54307E_VIEWPORT" . "NAME" asc, "AO_54307E_VIEWPORTFORM" . "FORM_ORDER" asc which doesn't return any result. However if I change the condition to lower("AO_54307E_VIEWPORTFORM"."NAME") = 'Шаблонные сайты' the query will give the expected result. Cheers, Chen

            Hi cmao / 78608451133782511

            Can i please enquire about the database that is used?

            If you perform a SQL command yourself that is something like:

            select rt.*
            from "AO_54307E_VIEWPORTFORM" rt inner join "AO_54307E_VIEWPORT" vp on rt."VIEWPORT_ID"=vp."ID"
            where rt."VIEWPORT_ID"=<portal id> and lower(rt."NAME") = lower('<request type name');
            

            Do you get the expected results, or is there a database level exception that means it does not return any results?

            If I can know which database is being used, will be able to perform some testing myself.

            Regards
            Matt
            JIRA Service Desk developer

            Matthew McMahon (Inactive) added a comment - Hi cmao / 78608451133782511 Can i please enquire about the database that is used? If you perform a SQL command yourself that is something like: select rt.* from "AO_54307E_VIEWPORTFORM" rt inner join "AO_54307E_VIEWPORT" vp on rt. "VIEWPORT_ID" =vp. "ID" where rt. "VIEWPORT_ID" =<portal id> and lower(rt. "NAME" ) = lower( '<request type name' ); Do you get the expected results, or is there a database level exception that means it does not return any results? If I can know which database is being used, will be able to perform some testing myself. Regards Matt JIRA Service Desk developer

            Hi, there.

            This is a very critical for me. Me and my department cannot use JQL with Customer Request Type as we used to do every day. 

            Please, fix it back!

            Alexander Demchenko added a comment - Hi, there. This is a very critical for me. Me and my department cannot use JQL with Customer Request Type as we used to do every day.  Please, fix it back!

              mmcmahon Matthew McMahon (Inactive)
              cmao Chen Mao (Inactive)
              Affected customers:
              1 This affects my team
              Watchers:
              5 Start watching this issue

                Created:
                Updated:
                Resolved: