Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <23534326.1168447887826.JavaMail.jira@brutus>
Date: Wed, 10 Jan 2007 08:51:27 -0800 (PST)
From: "A B (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-64) Create a table with a query
In-Reply-To: <1984807828.1100122224169.JavaMail.apache@nagoya>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12463626 ] 

A B commented on DERBY-64:
--------------------------

James -- Thank you for the test cases.  This is a good start.  Some quick notes:

  1. The name of the new test is simply "tables.sql", which is a bit vague--based on the name alone I don't think I could guess what it's actually testing.  Is it possible to rename the test to something more descriptive, such as "CreateTableFromQuery.sql"?

  2. The test is an ij (.sql) test, which is okay but is not ideal.  Over the past several months we have been trying to move the test harness to JUnit, so it'd be better if all new tests were added as JUnit tests.  In this particular case I think it should be easy to do: I ran the "ijToJUnit" tool that is attached to DERBY-2151 against the .out file in your patch and the tool ran without problem, creating a default JUnit test that should (in theory) run.  You would of course have to add a package declaration and license header, etc., but you should be able to do that by looking at existing JUnit tests.  If you have any problems with the tool or with the resulting JUnit test, please feel free to post.

  3. The test cases that you have ensure that the CREATE TABLE statements execute without error, but they do not show what was actually created.  In order to verify that the correct columns were created with the correct names, it might be good to do a "select * " from the newly created tables before deleting them.  Ex: 

  -- create table based on t1
  create table t2 as select * from t1 with no data;
  select * from t2;
  drop table t2;

It would be even better if you could add tests to check that the types of the columns are what they should be, as well.  This is one thing that would probably be easier in ij than in JUnit (because ij has the "describe table" commands), but it should be doable in JUnit, too.

  4. The SQL spec indicates that if a column in the query is known to be non-nullable then the corresponding column in the new table should be non-nullable, as well.  I did a quick test and this is in fact correct with your patch--but it doesn't look like you have any test cases to show this.  Is it possible to add one?

Thanks for replying to my comment in such timely fashion, and for the initial test cases.  If you have any questions/concerns about my feedback above, please do ask!

> Create a table with a query
> ---------------------------
>
>                 Key: DERBY-64
>                 URL: https://issues.apache.org/jira/browse/DERBY-64
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>            Reporter: Christian d'Heureuse
>         Assigned To: James F. Adams
>         Attachments: Derby64Patch1.txt, Derby64Patch2.txt, Derby64Patch3.txt
>
>
> I suggest to implement a SQL statement to create and fill a table with a query, without having to write the columns definition.
> e.g.:
>  CREATE TABLE new_table AS SELECT ...;
> or:
>  SELECT ... INTO new_table FROM ...;

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira