Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11511

Collation aggregation for IN works differenly for scalar and ROW

Details

    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

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16454 Bad results for IN with ROW

          • Major - Major loss of function.
          • Closed

          Activity

            bar Alexander Barkov added a comment - - edited

            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)

            bar Alexander Barkov added a comment - - edited 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)

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.