Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.3.1.4
    • Component/s: SQL
    • Labels:
      None

      Description

      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 ...;

      1. Derby64Patch1.txt
        12 kB
        James F. Adams
      2. Derby64Patch2.txt
        11 kB
        James F. Adams
      3. Derby64Patch3.txt
        18 kB
        James F. Adams
      4. Derby64Patch4.txt
        21 kB
        James F. Adams

        Issue Links

          Activity

          Hide
          Bryan Pendleton added a comment -

          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?

          Show
          Bryan Pendleton added a comment - 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?
          Hide
          Christian d'Heureuse added a comment -

          No, the normal INSERT...SELECT statement does not create a table. The table must already exist for the normal INSERT...SELECT statement.

          Most SQL DBMS provide a method to store a query result into a table so that the table is automatically created.

          For DB2 and Oracle the syntax is:
          CREATE TABLE new_table AS SELECT ...

          For MS-SQL Server the syntax is:
          SELECT ... INTO new_table FROM ...

          This is convenient for complex SQL scripts that store intermediate results in temporary tables. Without such a statement, every intermediate table has to be created with a CREATE TABLE statement, before it can be filled with INSERT ... SELECT, and each CREATE TABLE statement for a temporary table must repeat the full columns definition.

          Show
          Christian d'Heureuse added a comment - No, the normal INSERT...SELECT statement does not create a table. The table must already exist for the normal INSERT...SELECT statement. Most SQL DBMS provide a method to store a query result into a table so that the table is automatically created. For DB2 and Oracle the syntax is: CREATE TABLE new_table AS SELECT ... For MS-SQL Server the syntax is: SELECT ... INTO new_table FROM ... This is convenient for complex SQL scripts that store intermediate results in temporary tables. Without such a statement, every intermediate table has to be created with a CREATE TABLE statement, before it can be filled with INSERT ... SELECT, and each CREATE TABLE statement for a temporary table must repeat the full columns definition.
          Hide
          Bryan Pendleton added a comment -

          Thanks for the clarification. I agree, that would be a useful feature to have.

          Show
          Bryan Pendleton added a comment - Thanks for the clarification. I agree, that would be a useful feature to have.
          Hide
          Daniel John Debrunner added a comment -

          Section 11.3 of the SQL2003 foundation describes the

          CREATE TABLE new_table AS <subquery>

          syntax and behaviour.

          Show
          Daniel John Debrunner added a comment - Section 11.3 of the SQL2003 foundation describes the CREATE TABLE new_table AS <subquery> syntax and behaviour.
          Hide
          James F. Adams added a comment -

          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.

          Show
          James F. Adams added a comment - 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.
          Hide
          James F. Adams added a comment -

          I have updated the patch due to numerous changes on the trunk. Hopefully someone will have time to review and comment.

          Show
          James F. Adams added a comment - I have updated the patch due to numerous changes on the trunk. Hopefully someone will have time to review and comment.
          Hide
          Manish Khettry added a comment -

          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.

          Show
          Manish Khettry added a comment - 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.
          Hide
          James F. Adams added a comment -

          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.

          Show
          James F. Adams added a comment - 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.
          Hide
          A B added a comment -

          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.

          Show
          A B added a comment - 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.
          Hide
          James F. Adams added a comment -

          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.

          Show
          James F. Adams added a comment - 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.
          Hide
          James F. Adams added a comment -

          I attached a new version of the patch (Derby64Patch3.txt) that adds tests for the functionality provided so far.

          Show
          James F. Adams added a comment - I attached a new version of the patch (Derby64Patch3.txt) that adds tests for the functionality provided so far.
          Hide
          A B added a comment -

          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!

          Show
          A B added a comment - 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!
          Hide
          James F. Adams added a comment -

          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.

          Show
          James F. Adams added a comment - 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.
          Hide
          Daniel John Debrunner added a comment -

          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!

          Show
          Daniel John Debrunner added a comment - 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!
          Hide
          James F. Adams added a comment -

          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.

          Show
          James F. Adams added a comment - 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.
          Hide
          A B added a comment -

          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!

          Show
          A B added a comment - 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!
          Hide
          Daniel John Debrunner added a comment -

          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

          Show
          Daniel John Debrunner added a comment - 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
          Hide
          A B added a comment -

          > 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.

          Show
          A B added a comment - > 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.
          Hide
          James F. Adams added a comment -

          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.

          Show
          James F. Adams added a comment - 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.
          Hide
          Bryan Pendleton added a comment -

          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?

          Show
          Bryan Pendleton added a comment - 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?
          Hide
          James F. Adams added a comment -

          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.

          Show
          James F. Adams added a comment - 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.
          Hide
          Daniel John Debrunner added a comment -

          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.

          Show
          Daniel John Debrunner added a comment - 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.
          Hide
          Daniel John Debrunner added a comment -

          DERBY-2288 has been added to implement the WITH DATA option.

          Show
          Daniel John Debrunner added a comment - DERBY-2288 has been added to implement the WITH DATA option.
          Hide
          A B added a comment -

          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...

          Show
          A B added a comment - 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...

            People

            • Assignee:
              James F. Adams
              Reporter:
              Christian d'Heureuse
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development