Issue #648 new

Number calculated field not properly sorted

Mislav Ugrin
created an issue

Hi, I am using a little bit more complex math expression in number calculated field and jira is not sorting by it's value. Value is being calculated properly.

Comments (6)

  1. Fidel Castro Armario repo owner

    Hi Mislav Ugrin,

    The problem is that you expression takes too long to be calculated, and JIRA withdraws from trying to sort by the field.

    You can do the following 3 optimizations in your formula:

    Replace term:

        (
            (modulus(floor(({00057}-{00009})/{HOUR}), 24) = 0) ? 0 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 1) ? 1 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 2) ? 2 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 3) ? 3 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 3) ? 3 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 4) ? 4 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 5) ? 5 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 6) ? 6 :
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 7) ? 7 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 8) ? 8 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 9) ? 9 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 10) ? 10 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 11) ? 11 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 12) ? 12 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 13) ? 13 :
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 14) ? 14 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 15) ? 15 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 16) ? 16 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 17) ? 17 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 18) ? 18 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 19) ? 19 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 20) ? 21 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 21) ? 23 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 22) ? 25 : 
            ((modulus(floor(({00057}-{00009})/{HOUR}), 24) = 23) ? 27 : 0))))))))))))))))))))))))
        )
    

    with term

    getMatchingValue(modulus(floor(({00057}-{00009})/{HOUR}), 24),
    [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23],
    [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 21, 23, 25, 27])
    

    Replace term:

        (
            ((floor(({00057}-{00009})/{DAY}) = 0) ? 0 : 
            ((floor(({00057}-{00009})/{DAY}) = 1 ) ? 5 : 
            ((floor(({00057}-{00009})/{DAY}) = 2 ) ? 8 : 
            ((floor(({00057}-{00009})/{DAY}) = 3 ) ? 10 : 
            ((floor(({00057}-{00009})/{DAY}) = 4 ) ? 11 : 
            ((floor(({00057}-{00009})/{DAY}) = 5 ) ? 12 : 
            ((floor(({00057}-{00009})/{DAY}) = 6 ) ? 12 : 
            ((floor(({00057}-{00009})/{DAY}) = 7 ) ? 12 : 
            ((floor(({00057}-{00009})/{DAY}) = 8 ) ? 13 : 
            ((floor(({00057}-{00009})/{DAY}) = 9 ) ? 13 :
            ((floor(({00057}-{00009})/{DAY}) = 10) ? 13 : 
            ((floor(({00057}-{00009})/{DAY}) = 11) ? 14 : 
            ((floor(({00057}-{00009})/{DAY}) = 12) ? 14 : 
            ((floor(({00057}-{00009})/{DAY}) = 13) ? 14 : 
            ((floor(({00057}-{00009})/{DAY}) = 14) ? 15 : 
            ((floor(({00057}-{00009})/{DAY}) = 15) ? 15 : 
            ((floor(({00057}-{00009})/{DAY}) = 16) ? 15 : 
            ((floor(({00057}-{00009})/{DAY}) = 17) ? 16 : 
            ((floor(({00057}-{00009})/{DAY}) = 18) ? 16 : 
            ((floor(({00057}-{00009})/{DAY}) = 19) ? 16 : 
            ((floor(({00057}-{00009})/{DAY}) = 20) ? 17 : 
            ((floor(({00057}-{00009})/{DAY}) = 21) ? 17 : 
            ((floor(({00057}-{00009})/{DAY}) = 22) ? 17 : 
            ((floor(({00057}-{00009})/{DAY}) = 23) ? 18 : 
            ((floor(({00057}-{00009})/{DAY}) = 24) ? 18 : 
            ((floor(({00057}-{00009})/{DAY}) = 25) ? 18 : 
            ((floor(({00057}-{00009})/{DAY}) = 26) ? 19 : 
            ((floor(({00057}-{00009})/{DAY}) = 27) ? 19 : 
            ((floor(({00057}-{00009})/{DAY}) = 28) ? 19 : 
            ((floor(({00057}-{00009})/{DAY}) = 29) ? 20 : 
            ((floor(({00057}-{00009})/{DAY}) = 30) ? 20 : 
            ((floor(({00057}-{00009})/{DAY}) = 31) ? 20 : 
            ((floor(({00057}-{00009})/{DAY}) = 32) ? 21 : 
            ((floor(({00057}-{00009})/{DAY}) = 33) ? 21 : 
            ((floor(({00057}-{00009})/{DAY}) = 34) ? 21 : 
            ((floor(({00057}-{00009})/{DAY}) >= 35) ? 22 : 22))))))))))))))))))))))))))))))))))))
        )
    

    with term

    getMatchingValue(floor(({00057}-{00009})/{DAY}),
    [00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34],
    [00, 05, 08, 10, 11, 12, 12, 12, 13, 13, 13, 14, 14, 14, 15, 15, 15, 16, 16, 16, 17, 17, 17, 18, 18, 18, 19, 19, 19, 20, 20, 20, 21, 21, 21, 22])
    

    Replace term:

    (%{11620} = "Da") ? 3 : ((%{11620} = "Ne") ? 0 : 0)
    

    with term

    %{11620} = "Da" ? 3 : 0
    

    Try not to use more parentheses than the necessaries, since they also take some time to be evaluated.

  2. Mislav Ugrin reporter

    Hi Fidel Castro Armario Ok, getMatchingValue is a great function to use for this case. I optimised my expression as you proposed but it is still timing out.

    I took out all other logic except this one, still not being sorted.

    getMatchingValue(floor(({00057}-{00009})/{DAY}),
    [00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34],
    [00, 05, 08, 10, 11, 12, 12, 12, 13, 13, 13, 14, 14, 14, 15, 15, 15, 16, 16, 16, 17, 17, 17, 18, 18, 18, 19, 19, 19, 20, 20, 20, 21, 21, 21, 22])
    
  3. Mislav Ugrin reporter

    Hi Fidel Castro Armario, sorry for not answering earlier, was waiting to our sys admin to create test jira instance. We performed reindex, but still the same, sorting is not working. We have a quite large jira instance, more then 1300000 tickets, maybe this is making performance problems?

  4. Log in to comment