Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-2972

Update or select with function in the where clause causes with TERRITORY_BASED collation fails with ERROR 42818: Comparisons between 'VARCHAR' and 'VARCHAR' are not supported.

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.1.3
    • Fix Version/s: 10.3.2.1, 10.4.1.3
    • Component/s: SQL
    • Labels:
      None

      Description

      The following update fails with ERROR 42818
      ij> update testing set a = PADSTRING('aa',2024) where a = PADSTRING('a',2024);
      ERROR 42818: Comparisons between 'VARCHAR' and 'VARCHAR' are not supported.

      See full script below
      onnect 'jdbc:derby:nordb;territory=no_NO;collation=TERRITORY_BASED';

      CREATE FUNCTION PADSTRING (DATA VARCHAR(32000), LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.padString' LANGUAGE JAVA PARAMETER STYLE JAVA;

      create table testing
      (a varchar(2024), b varchar(1024), c varchar(1024), d varchar(2048), e varchar(300)) ;

      – insert 9 rows into the table
      insert into testing values (PADSTRING('1',2024), PADSTRING('2',1024),
      PADSTRING('3',1024), PADSTRING('4',2048), PADSTRING('5',300));

      insert into testing values (PADSTRING('10',2024),
      PADSTRING('20',1024), PADSTRING('30',1024),
      PADSTRING('40',2048), PADSTRING('50',300));

      insert into testing values (PADSTRING('100',2024),
      PADSTRING('200',1024), PADSTRING('300',1024),
      PADSTRING('400',2048), PADSTRING('500',300));

      insert into testing values (PADSTRING('1000',2024),
      PADSTRING('2000',1024), PADSTRING('3000',1024),
      PADSTRING('4000',2048), PADSTRING('5000',300));

      insert into testing values (PADSTRING('10000',2024),
      PADSTRING('20000',1024), PADSTRING('30000',1024),
      PADSTRING('40000',2048), PADSTRING('50000',300));

      insert into testing values (PADSTRING('100000',2024),
      PADSTRING('200000',1024), PADSTRING('300000',1024),
      PADSTRING('400000',2048), PADSTRING('500000',300));

      insert into testing values (PADSTRING('1000000',2024),
      PADSTRING('2000000',1024), PADSTRING('3000000',1024),
      PADSTRING('4000000',2048), PADSTRING('5000000',300));

      insert into testing values (PADSTRING('10000000',2024),
      PADSTRING('20000000',1024), PADSTRING('30000000',1024),
      PADSTRING('40000000',2048), PADSTRING('50000000',300));

      insert into testing values (PADSTRING('100000000',2024),
      PADSTRING('200000000',1024), PADSTRING('300000000',1024),
      PADSTRING('400000000',2048), PADSTRING('500000000',300));

      update testing set a = PADSTRING('aa',2024) where a = PADSTRING('a',2024);

        Attachments

        1. tempFunction_stat.txt
          0.9 kB
          Mamta A. Satoor
        2. tempFunction_diff.txt
          65 kB
          Mamta A. Satoor
        3. derby-2972_stat.txt
          0.2 kB
          Katherine Marsden
        4. derby-2972_diff.txt
          6 kB
          Katherine Marsden
        5. derby_2972_stat.txt
          0.5 kB
          Katherine Marsden
        6. derby_2972_diff.txt
          11 kB
          Katherine Marsden

        Issue Links

          Activity

            People

            • Assignee:
              kmarsden Katherine Marsden
              Reporter:
              kmarsden Katherine Marsden

              Dates

              • Created:
                Updated:
                Resolved:

                Issue deployment