You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Christian d'Heureuse (JIRA)" <de...@db.apache.org> on 2004/11/10 22:30:24 UTC
[jira] Created: (DERBY-64) Create a table with a query
Create a table with a query
---------------------------
Key: DERBY-64
URL: http://nagoya.apache.org/jira/browse/DERBY-64
Project: Derby
Type: New Feature
Components: SQL
Reporter: Christian d'Heureuse
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:
http://nagoya.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James F. Adams reassigned DERBY-64:
-----------------------------------
Assignee: (was: James F. Adams)
I do not have time to continue to work on this at this time. I am setting the issue to unassigned in case someone else wants to finish the "WITH DATA" option.
> 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
> Attachments: Derby64Patch1.txt, Derby64Patch2.txt, Derby64Patch3.txt, Derby64Patch4.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
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-64?page=comments#action_12457191 ]
James F. Adams commented on DERBY-64:
-------------------------------------
Thanks for looking at my patch Manish. I ran derbyAll and the JUnit functionTests.suites.All and saw no errors. I think I will develop tests for the functionality I provided so far. After that I will see what I can do to implement the WITH DATA option.
> Create a table with a query
> ---------------------------
>
> Key: DERBY-64
> URL: http://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
>
>
> 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: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James F. Adams updated DERBY-64:
--------------------------------
Attachment: Derby64Patch4.txt
> 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, Derby64Patch4.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
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12463331 ]
A B commented on DERBY-64:
--------------------------
I noticed that the patch for this issue was posted about a month ago and has been idle since. So I did a quick review and it looks like a great start. I ran some simple tests and things work as I would expect them to. If I specify "WITH DATA" I get a reasonable "incremental" error message ("Feature not supported") instead of an internal failure, which is good. The code is well-contained and nicely commented--and the indentation even matches the surrounding code (nice job!). There are handful of lines in CreateTableNode and sqlgrammar.jj that go beyond 80 characters, but that's just me being picky.
I also looked at section 11.3 of SQL2003 foundation and so far as I can tell Derby64Patch2.txt lines up with the standard in terms of the required "NO DATA" syntax and functionality. As a sanity check I ran derbyall on Red Hat Linux with ibm142 and there were no failures.
The most recent comment for this issue is from James Adams saying:
"I think I will develop tests for the functionality I provided so far."
James, are you still planning to contribute test cases for the current (NO DATA) functionality? If you do so, I'd be willing to look at committing this patch as the first step in incremental development for this feature.
> 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
>
>
> 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
[jira] Updated: (DERBY-64) Create a table with a query
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Daniel John Debrunner updated DERBY-64:
---------------------------------------
Comment: was deleted
> 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
[jira] Assigned: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-64?page=all ]
James F. Adams reassigned DERBY-64:
-----------------------------------
Assignee: James F. Adams
> Create a table with a query
> ---------------------------
>
> Key: DERBY-64
> URL: http://issues.apache.org/jira/browse/DERBY-64
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Reporter: Christian d'Heureuse
> Assigned To: James F. Adams
>
> 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: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-64?page=all ]
James F. Adams updated DERBY-64:
--------------------------------
Attachment: Derby64Patch2.txt
I have updated the patch due to numerous changes on the trunk. Hopefully someone will have time to review and comment.
> Create a table with a query
> ---------------------------
>
> Key: DERBY-64
> URL: http://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
>
>
> 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: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-64) Create a table with a query
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Daniel John Debrunner resolved DERBY-64.
----------------------------------------
Resolution: Fixed
Fix Version/s: 10.3.0.0
Assignee: James F. Adams
I think this should be marked as fixed an a new issue related to supporting the WITH DATA option added.
Marking this as fixed means that it will appear in the list of fixed items for the next release.
Even without the WITH DATA option it is a useful feature. Thanks James.
> 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
> Fix For: 10.3.0.0
>
> Attachments: Derby64Patch1.txt, Derby64Patch2.txt, Derby64Patch3.txt, Derby64Patch4.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.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "Bryan Pendleton (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-64?page=comments#action_12443861 ]
Bryan Pendleton commented on DERBY-64:
--------------------------------------
Derby does support
INSERT INTO t SELECT ... FROM other_t;
For example you can do:
INSERT INTO t1 (a, b) SELECT x, y from t2 where x > 17;
Is this an acceptable solution to your problem?
> Create a table with a query
> ---------------------------
>
> Key: DERBY-64
> URL: http://issues.apache.org/jira/browse/DERBY-64
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Reporter: Christian d'Heureuse
>
> 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: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12463906 ]
James F. Adams commented on DERBY-64:
-------------------------------------
I have attached an updated patch (Derby64Patch4.txt) that should address the comments on my previous effort.
The tests have been re-implemented as JUnit tests.
I added code to verify that the created tables have the correct column names, types, and nullability.
I modified the changes I made to CreateTableNode.java and sqlgrammar.jj so none of the added/modified lines are longer than 80 characters.
> 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, Derby64Patch4.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
[jira] Updated: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James F. Adams updated DERBY-64:
--------------------------------
Attachment: Derby64Patch3.txt
> 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
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12463775 ]
James F. Adams commented on DERBY-64:
-------------------------------------
A B -- Thanks for reviewing my work.
I will convert the tests that I wrote to JUnit tests to be inline with the current testing philosophy. I will add additional tests to verify that the tables were created correctly, including the nullability of the columns.
> 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
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12465037 ]
Bryan Pendleton commented on DERBY-64:
--------------------------------------
Hi James, this feature is coming along great! Thanks for all the good work!
Regarding "WITH DATA", can you model that implementation to mimic what is done by INSERT ... SELECT?
> 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, Derby64Patch4.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
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12464383 ]
Daniel John Debrunner commented on DERBY-64:
--------------------------------------------
A B wrote:
--------------------------------------
I noticed that in the "suite()" method of the JUnit test you use:
+ suite.addTestSuite(CreateTableFromQueryTest.class);
instead of calling the default JUnit decorator, i.e.:
- suite.addTestSuite(CreateTableFromQueryTest.class);
+ suite.addTest(TestConfiguration.defaultSuite(
+ CreateTableFromQueryTest.class));
The latter ensures that the test runs in both embedded mode and client/server mode, whereas the former (the patch as committed) only runs the test in embedded mode. I think the general approach has been to try to run JUnit tests in both modes (by using the "defaultSuite()" method shown above) unless there is a specific reason to only run the test in one mode.
----------------------------------------
I don't think that's quite correct, a test class's suite method should run the test in the modes that provide useful testing.
For tests that are testing JDBC features it makes sense to test in both modes as it tests both JDBC drivers.
However for SQL language tests it doesn't always make sense to test also in the client mode, since what is being tested is the SQL behaviour of the embedded engine. For example a test of server side procedures makes little sense to also run in client mode. I also think that test like this one that are primary DDL tests have little value in being run in client mode. On the other hand a test that tests a new data type in the SQL language would make sense to run in the client mode to ensure that the data value is transfered over DRDA correctly.
It basically comes down to what value would running the test in both configurations add in terms of quality as opposed to the extra time required to run the two modes.
This is very very briefly covered in "Primary Configurations" section but could be expanded.
http://wiki.apache.org/db-derby/DerbyJunitTestConfiguration#head-74a803dbe5c01e00d93dc978dc502c4c17446460
> 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, Derby64Patch4.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
[jira] Updated: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James F. Adams updated DERBY-64:
--------------------------------
Attachment: Derby64Patch4.txt
> 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, Derby64Patch4.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
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "A B (JIRA)" <ji...@apache.org>.
[ 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
[jira] Updated: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-64?page=all ]
James F. Adams updated DERBY-64:
--------------------------------
Derby Info: [Patch Available]
> Create a table with a query
> ---------------------------
>
> Key: DERBY-64
> URL: http://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
>
>
> 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: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12463799 ]
Daniel John Debrunner commented on DERBY-64:
--------------------------------------------
James - here's the top-level wiki page on derby's Junit tests.
http://wiki.apache.org/db-derby/DerbyJUnitTesting
Feel free to ask questions on derby-dev as well!
> 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
[jira] Updated: (DERBY-64) Create a table with a query
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B updated DERBY-64:
---------------------
Derby Info: (was: [Patch Available])
Thank you for the latest patch, James. I ran derbyall on Red Hat Linux as a sanity check and then committed the patch to the 10.3 trunk with svn revision #495750.
I noticed that in the "suite()" method of the JUnit test you use:
+ suite.addTestSuite(CreateTableFromQueryTest.class);
instead of calling the default JUnit decorator, i.e.:
- suite.addTestSuite(CreateTableFromQueryTest.class);
+ suite.addTest(TestConfiguration.defaultSuite(
+ CreateTableFromQueryTest.class));
The latter ensures that the test runs in both embedded mode and client/server mode, whereas the former (the patch as committed) only runs the test in embedded mode. I think the general approach has been to try to run JUnit tests in both modes (by using the "defaultSuite()" method shown above) unless there is a specific reason to only run the test in one mode. To see what would happen I made the above change and ran the new CreateTableFromQueryTest in both modes without any problems.
This isn't a strict requirement, though, so I went ahead and committed the patch as it was. If you agree that this makes sense and you would like to change the test to use "defaultSuite()", you can post another follow-up patch with just that change and I'll gladly commit it.
Also: I don't think anyone ever answered your question about the creation of three new error messages that are almost identical to existing ones except for the word "VIEW". For what it's worth, my feeling is that it might be cleaner if you could in fact parameterize the existing messages so that the term "TABLE" or "VIEW" can be passed in. But that can be done as a follow-up patch if you are so inclined.
One other note: I noticed that there is a new "test" method for each query in the JUnit test. Generally speaking that is not a requirement: you should feel free to have multiple test scenarios/queries in the same "test" method if they have something in common. Maybe you knew that and just decided to have separate test methods, anyways--if that's the case, then no problem :) I just thought I'd bring it up in case you were thinking each query/test case required its own test method.
In any event, thank you for the contribution! Since the changes have been committed I am unchecking the "Patch Available" flag. I am leaving the issue open, though, since it sounds like you are still planning to work on the "WITH DATA" option?
Thanks again!
> 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, Derby64Patch4.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
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12463798 ]
Daniel John Debrunner commented on DERBY-64:
--------------------------------------------
James - here's the top-level wiki page on derby's Junit tests.
> 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
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12463457 ]
James F. Adams commented on DERBY-64:
-------------------------------------
I attached a new version of the patch (Derby64Patch3.txt) that adds tests for the functionality provided so far.
> 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
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12463447 ]
James F. Adams commented on DERBY-64:
-------------------------------------
A B,
Yes, I am still planning to create test cases for the the current (NO DATA) functionality. I should be able to have something to contribute by the end of this week.
> 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
>
>
> 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
[jira] Updated: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James F. Adams updated DERBY-64:
--------------------------------
Attachment: (was: Derby64Patch4.txt)
> 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
[jira] Updated: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-64?page=all ]
James F. Adams updated DERBY-64:
--------------------------------
Attachment: Derby64Patch1.txt
I uploaded the patch Derby64Patch1.txt.
This is certainly a work in progress and is not complete, but I uploaded it as I have a few questions and could use some guidance.
The patch includes the following:
SQLState.java and messages.xml were modified to add three messages to flag various errors. These error messages are similar to those used by CREATE VIEW. I was wondering if I should have parameterized the two messages that differ only in the use of TABLE instead of VIEW?
sqlgrammer.jj was modified to support the following syntax:
CREATE TABLE new_table AS subquery WITH [NO] DATA
CreateTableNode.java was modified to define a new init method to take a result column list and a query expression. The bindStatement method was modified to bind the quiery expression and create the table element list from the result column list and the query expression.
The current patch does not implement the WITH DATA option. When WITH DATA is specified the table should be populated with the result of the query expression. It is not obvious to me how this should be implemented. Any guideance would be greatly appreciated.
Things left to do:
Implement the WITH DATA option.
Implement tests.
Update documentation.
> Create a table with a query
> ---------------------------
>
> Key: DERBY-64
> URL: http://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
>
>
> 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: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12465036 ]
James F. Adams commented on DERBY-64:
-------------------------------------
A B,
Thank you for reviewing and committing the latest patch.
I would like to work on the "WITH DATA" option. I assume I will need to modify CreateTableConstantAction to load the data obtained via the query expression after the conglomerate is created. I am not at all familiar with this area of the code so any guidance would be greatly appreciated.
> 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, Derby64Patch4.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
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "Manish Khettry (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-64?page=comments#action_12457097 ]
Manish Khettry commented on DERBY-64:
-------------------------------------
I spent some time looking at the patch and it looks like a good first stab at the compile and bind phase. The execution of this DDL takes place in in CreateTableConstantAction. I would think that to populate the table with the results of the queryr, you would need to call generate on the query tree and somehow execute it. It may also help to see how a simple select query is compiled to byte code and executed.
I should add the caveat that I am not a commiter and not very familiar with the code since I work on it off and on, so hopefully others more familiar with the code will jump in with their take on the patch.
Do existing tests psas with your changes? It seems even without the WITH DATA option it is a useful addition to the sysetm.
> Create a table with a query
> ---------------------------
>
> Key: DERBY-64
> URL: http://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
>
>
> 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: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12504911 ]
A B commented on DERBY-64:
--------------------------
Just curious: has anyone thought about whether or not this new feature creates any holes in the existing grant/revoke functionality? That is, can a user use the CREATE TABLE AS ... syntax to select from tables that s/he otherwise does not have permission to access? Sort of like it (briefly) allowed users to create columns that they weren't supposed to be allowed to create...(DERBY-2605, now resolved).
I did some quick tests by creating a table and a view and then using the CREATE TABLE AS ... statement to try to select from that table and view. As far as I can tell things work correctly: if the user does not have permission to select from the table/view, the CREATE TABLE AS statement fails with the appropriate error. Ex:
ij(CONNECTION1)> create table t_oops as select * from app.v1 with no data;
ERROR 42502: User 'INIGO' does not have select permission on column 'I' of table 'APP'.'V1'
So my guess is that everything is okay here. But I thought I'd raise the issue anyways, just to see if anyone out there might know of any problematic scenarios...
If there does turn out to be an issue here, it's probably not *that* big of a deal since we don't support the "WITH DATA" option yet (DERBY-2288) and thus there's no way to use this new syntax to see data. So all a user would be able to do is see the column names and types of the table/view, which s/he can do via the ij "describe" command already.
But still, it'd be good to understand whether not such "holes" exist...
> 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
> Assignee: James F. Adams
> Fix For: 10.3.0.0
>
> Attachments: Derby64Patch1.txt, Derby64Patch2.txt, Derby64Patch3.txt, Derby64Patch4.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.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-64) Create a table with a query
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-64?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12464384 ]
A B commented on DERBY-64:
--------------------------
> I don't think that's quite correct, a test class's suite method should run the test
> in the modes that provide useful testing.
Okay, I stand corrected. Thanks for pointing this out.
> 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, Derby64Patch4.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