Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-754

LOCATE() function isn't being translated correctly by Oracle Connector

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 6.0.0, 6.1.0, 6.2.0
    • Fix Version/s: 6.2.0
    • Component/s: JDBC Connector
    • Labels:
      None
    • Estimated Difficulty:
      Low

      Description

      The rewritten/translated query for the MMx LOCATE() function to Oracle's instr() function does not appear to be correct.

      SELECT locate(INTNUM, '234567890', 1) FROM SMALLA WHERE INTKEY = 26

      Is being rewritten for Oracle as:

      SELECT instr('234567890', to_char(SmallA.IntNum), 2) FROM SmallA WHERE SmallA.IntKey = 26

      In this case Oracle will return 0 for instr() because Oracle starts at position 1.

      The query should be:

      SELECT instr('234567890', to_char(SmallA.IntNum), 1) FROM SmallA WHERE SmallA.IntKey = 26

      Furthermore, if I pass a negative value to LOCATE() it appears we assume position 1. If the negative value is sent to Oracle, Oracle goes from the end of the string. We should prevent this (if not already).

      In 5.5.1 the rewritten query is even different and may ore may not be correct. I have not checked 5.5.3/5.5.4 and/or Westport/Teiid.

      Here are the test cases that should cover this issue:

      public void testRewriteLocate() throws Exception

      { String input = "SELECT locate(INTNUM, 'chimp', 1) FROM SMALLA"; //$NON-NLS-1$ String output = "SELECT instr('chimp', to_char(SmallA.IntNum), 1) FROM SmallA"; //$NON-NLS-1$ helpTestVisitor(getTestVDBPath(), input, new Integer(TranslatedCommand.EXEC_TYPE_QUERY), output); }

      public void testRewriteLocate2() throws Exception

      { String input = "SELECT locate(STRINGNUM, 'chimp') FROM SMALLA"; //$NON-NLS-1$ String output = "SELECT instr('chimp', SmallA.StringNum) FROM SmallA"; //$NON-NLS-1$ helpTestVisitor(getTestVDBPath(), input, new Integer(TranslatedCommand.EXEC_TYPE_QUERY), output); }

      public void testRewriteLocate3() throws Exception

      { String input = "SELECT locate(INTNUM, '234567890', 1) FROM SMALLA WHERE INTKEY = 26"; //$NON-NLS-1$ String output = "SELECT instr('234567890', to_char(SmallA.IntNum), 1) FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$ helpTestVisitor(getTestVDBPath(), input, new Integer(TranslatedCommand.EXEC_TYPE_QUERY), output); }

      public void testRewriteLocate4() throws Exception

      { String input = "SELECT locate('c', 'chimp', 1) FROM SMALLA"; //$NON-NLS-1$ String output = "SELECT 1 FROM SmallA"; //$NON-NLS-1$ helpTestVisitor(getTestVDBPath(), input, new Integer(TranslatedCommand.EXEC_TYPE_QUERY), output); }

      public void testRewriteLocate5() throws Exception

      { String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM SMALLA"; //$NON-NLS-1$ String output = "SELECT instr('chimp', SmallA.StringNum, 1) FROM SmallA"; //$NON-NLS-1$ helpTestVisitor(getTestVDBPath(), input, new Integer(TranslatedCommand.EXEC_TYPE_QUERY), output); }

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                loleary Larry O'Leary
                Reporter:
                loleary Larry O'Leary
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 30 minutes
                  30m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 30 minutes
                  30m