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

Support specification of column names in INSERT INTO

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Resolved
    • Minor
    • Resolution: Incomplete
    • 2.0.0
    • None
    • SQL

    Description

      Some databases allow you to specify column names when specifying the target of an INSERT INTO. For example, in SQLite:

      sqlite> CREATE TABLE twocolumn (x INT, y INT); INSERT INTO twocolumn(x, y) VALUES (44,51), (NULL,52), (42,53), (45,45)
         ...> ;
      sqlite> select * from twocolumn;
      44|51
      |52
      42|53
      45|45
      

      I have a corpus of existing queries of this form which I would like to run on Spark SQL, so I think we should extend our dialect to support this syntax.

      When implementing this, we should make sure to test the following behaviors and corner-cases:

      • Number of columns specified is greater than or less than the number of columns in the table.
      • Specification of repeated columns.
      • Specification of columns which do not exist in the target table.
      • Permute column order instead of using the default order in the table.

      For each of these, we should check how SQLite behaves and should also compare against another database. It looks like T-SQL supports this; see https://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx under the "Inserting data that is not in the same order as the table columns" header.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              joshrosen Josh Rosen
              Votes:
              3 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: