• The problem is not related to not-yet-pushed order by optimization fixes, MDEV-6657 or MDEV-6402 or MDEV-6796
• The problem is repeatable on mysql-5.6

Note that the ref optimizer is able to make use of "pk1='123'". It's only ORDER BY optimizer that can't.

Narrowed it down to this code:

test_if_equality_guarantees_uniqueness(Item *l, Item *r)

{

  return (r->const_item() || !(r->used_tables() & ~OUTER_REF_TABLE_BIT)) &&

    item_cmp_type(l->cmp_type(), r->cmp_type()) == l->cmp_type() &&

    (l->cmp_type() != STRING_RESULT ||

     l->collation.collation == r->collation.collation);

}

ORDER BY optimizer uses it to check whether rows that satisfy "item=const" are all equal from the point of view of "ORDER BY item".

Here,

• l is "pk1" (integer column).
• r is either integer constant or a string constant.
• item_cmp_type(l, r) = DOUBLE_RESULT.

Apparently, the restriction in this function is too strict.

Discussed the problem with bar. Came to conclusion that whenever the column's result_type is in

then the comparison guarantees uniqueness.

Consider a counter-example:

(gdb) p 0xFFFF FFFF FFFF FFFF
$1 = 18446744073709551615

create table t1 (a bigint unsigned);

insert into t1 values 

(18446744073709551000),

(18446744073709551100),

(18446744073709551200);

select a, 

       a='18446744073709551200',

       a=18446744073709551200

from t1;

+----------------------+--------------------------+------------------------+

| a                    | a='18446744073709551200' | a=18446744073709551200 |

+----------------------+--------------------------+------------------------+

| 18446744073709551000 |                        0 |                      0 |

| 18446744073709551100 |                        0 |                      0 |

| 18446744073709551200 |                        1 |                      1 |

+----------------------+--------------------------+------------------------+

Hmm this was supposed to be a counter example, but it's not. If the comparison a='...' has been done by comparing double values, then all values in the table would have been equal to the constant, because of insufficient precision. This didn't happen, because of this code:

 void Item_bool_func2::fix_length_and_dec()

...

    Make a special case of compare with fields to get nicer comparisons

    of bigint numbers with constant string.

    This directly contradicts the manual (number and a string should

    be compared as doubles), but seems to provide more

    "intuitive" behavior in some cases (but less intuitive in others).

...

and the code here sets args[i]->cmp_context to show which type will be used for comparison.

Hi Sergei,

Discussed the problem with Alexander Barkov. Came to conclusion that whenever the column's result_type is in

Unknown macro: { REAL_RESULT, INT_RESULT, DECIMAL_RESULT , TIME_RESULT}

then the comparison guarantees uniqueness.

It's cmp_type(), not result_type()!!!
Note: temporal types (DATE, TIME, DATETIME) have resul_type()==STRING_RESULT,
but cmp_type()==TIME_RESULT.

Apparently, the restriction in this function is too strict.

the restriction is formally correct. But it doesn't take into account that hack in Item_bool_func2::fix_length_and_dec().

Perhaps, instead of having that hack and — on top of that — introducing another hack in test_if_equality_guarantees_uniqueness() it'd be cleaner to replace the string literal with a numeric literal (under the same conditions that Item_bool_func2::fix_length_and_dec() checks now).

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.