[MDEV-11511] Collation aggregation for IN works differenly for scalar and ROW Created: 2016-12-09  Updated: 2019-11-18

Status: Open
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: datatype, upstream

Issue Links:
Relates
relates to MDEV-16454 Bad results for IN with ROW Closed
Epic Link: Data type cleanups

 Description   

Collation aggregation for IN works differently for scalar and row arguments.

This script demonstrates the same problem:

SET NAMES utf8;
 
SELECT
 
  'i' = 'j' COLLATE utf8_roman_ci  AS c1,
 
   ('i') IN ('j' COLLATE utf8_roman_ci) AS c2,
 
   ('i',1) IN (('j' COLLATE utf8_roman_ci,1),('j' COLLATE utf8_roman_ci,1)) AS c3;


+----+----+----+
 
| c1 | c2 | c3 |
 
+----+----+----+
 
|  1 |  1 |  0 |
 
+----+----+----+

i and j are equal letters in utf8_roman_ci
So:

  • c1 doing IN for scalar data returns the expected result
  • c2 doing IN for ROW data with one value returns the expected result
  • c3 doing IN for ROW data with more than one values returns a wrong result

 Comments   
Comment by Alexander Barkov [ 2018-06-27 ]

Note, according to the standard:

pred IN (value1, value2, value3)

is a synonym for:

pred = ANY (VALUES (value1), (value2), (value3))

Therefore, we should probably revise data type aggregation for IN entirely, according to the standard requirement, as follows:
1. Collect data types of value1, value2, value3 (for result)
2. Then collect the data type that we got on step 1 with the data type of "pred" (for comparison)

Generated at Thu Feb 08 07:50:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.