Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-27596

The JDBC 'query' option doesn't work for Oracle database

Log workAgile BoardRank to TopRank to BottomAttach filesAttach ScreenshotBulk Copy AttachmentsBulk Move AttachmentsVotersWatch issueWatchersCreate sub-taskConvert to sub-taskLinkCloneLabelsUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 2.4.2
    • 2.4.4, 3.0.0
    • SQL
    • None

    Description

      For the JDBC option `query`, we use the identifier name to start with underscore: s"(${subquery}) _SPARK_GEN_JDBC_SUBQUERY_NAME${curId.getAndIncrement()}". This is not supported by Oracle.

      The Oracle doesn't seem to support identifier name to start with non-alphabet character (unless it is quoted) and has length restrictions as well.
      https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements008.htm

      Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character as per below documentation - 
      Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.
      

      The alias name 'SPARK_GEN_JDBC_SUBQUERY_NAME<int value>' should be fixed to remove "_" prefix ( or make it quoted.not sure if it may impact other sources) to make it work for Oracle. Also the length should be limited as it is hitting below error on removing the prefix.

      java.sql.SQLSyntaxErrorException: ORA-00972: identifier is too long 
      

      It can be verified using below sqlfiddle link.

      http://www.sqlfiddle.com/#!4/9bbe9a/10050

      Attachments

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            dkbiswal Dilip Biswal Assign to me
            smilegator Xiao Li
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment