Derby
  1. Derby
  2. DERBY-568

ERROR 42Y07: Schema 'SchemaName' does not exist. But Schema already present in sql sentence

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1.1.0
    • Fix Version/s: None
    • Component/s: SQL
    • Environment:
      Win XP prof, Java SDK 1.4.2_06, Derby 10.1.1.0
    • Urgency:
      Normal

      Description

      Here files with short remarks to reproduce error
      /****************** create.bat **********************/
      java org.apache.derby.tools.ij derby.sql > create-schema.log
      java org.apache.derby.tools.ij derby-data.sql > init-data.log
      /****************** end create.bat **********************/

      /****************** derby.sql **********************/
      connect 'jdbc:derby:c:\data\test;create=true;databaseName=test';
      create schema lcp;

      CREATE TABLE lcp.transportgrp (
      transportgrp_id INTEGER GENERATED ALWAYS AS IDENTITY,
      name VARCHAR(128) NOT NULL,
      moddate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      moduser VARCHAR(32) NOT NULL DEFAULT USER,
      CONSTRAINT transportgrp_pk PRIMARY KEY (transportgrp_id)
      );
      CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication','true');
      CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.authentication.provider','BUILTIN');
      – DEFAULT USERS –
      CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.ADMIN', 'admin');
      /****************** end derby.sql **********************/

      /****************** derby-data.sql **********************/
      connect 'jdbc:derby:c:\data\lcpdb;user=ADMIN;password=admin';

      insert into lcp.transportgrp(name) values('test 1');
      insert into lcp.transportgrp(name) values('test 2');
      insert into lcp.transportgrp(name) values('test 3');

      – this sql will be passed
      select * from lcp.transportgrp where lcp.transportgrp.name = 'test 2';

      – this will be failed, but this select does not contains errors
      – the same sql run correctly on versions before 10.1.1.0
      select * from lcp.transportgrp where transportgrp.name = 'test 2';

      – this will be passed also
      select * from lcp.transportgrp a where a.name = 'test 2';
      /****************** end derby-data.sql **********************/

        Activity

        Igor Azarny created issue -
        Hide
        Daniel John Debrunner added a comment -

        The statment failing is as indicated:

        – this will be failed, but this select does not contains errors
        – the same sql run correctly on versions before 10.1.1.0
        select * from lcp.transportgrp where transportgrp.name = 'test 2';

        The exception is:

        ij> select * from lcp.transportgrp where transportgrp.name = 'test 2';
        ERROR 42Y07: Schema 'ADMIN' does not exist

        This seems to be the correct exception, the column name in the where clause is 'transportgrp.name', which is not qualified
        by a schema name, so it will resolve to an object in the current schema, which is ADMIN, matching the user name.
        This scheme does not exist, Derby's default schema at login matches the username and Derby allows the user to be
        in a schema which does not exist.

        Thus this seems to be not a bug. Unless some resolution rules automatically map transportgrp.name to the table lcp.transportgrp
        in the from list.

        Show
        Daniel John Debrunner added a comment - The statment failing is as indicated: – this will be failed, but this select does not contains errors – the same sql run correctly on versions before 10.1.1.0 select * from lcp.transportgrp where transportgrp.name = 'test 2'; The exception is: ij> select * from lcp.transportgrp where transportgrp.name = 'test 2'; ERROR 42Y07: Schema 'ADMIN' does not exist This seems to be the correct exception, the column name in the where clause is 'transportgrp.name', which is not qualified by a schema name, so it will resolve to an object in the current schema, which is ADMIN, matching the user name. This scheme does not exist, Derby's default schema at login matches the username and Derby allows the user to be in a schema which does not exist. Thus this seems to be not a bug. Unless some resolution rules automatically map transportgrp.name to the table lcp.transportgrp in the from list.
        Hide
        Øystein Grøvlen added a comment -

        This may not be a bug, but I do not think the message is very user
        friendly. I think a lot of users will not be aware of the mapping between
        user name and default schema, and especially the implication that the
        current schema may not exist. In my opinion, a better message would be
        "Table 'transportgrp' does not exist in the current schema" or something like that.

        Show
        Øystein Grøvlen added a comment - This may not be a bug, but I do not think the message is very user friendly. I think a lot of users will not be aware of the mapping between user name and default schema, and especially the implication that the current schema may not exist. In my opinion, a better message would be "Table 'transportgrp' does not exist in the current schema" or something like that.
        Hide
        Igor Azarny added a comment -

        I think this is a BUG.

        1. At sql sentence "select * from lcp.transportgrp where transportgrp.name = 'test 2';" schema name ALREADY present at from clause. So why we need to specify schema name where clause?

        2. The same sql run correctly on versions before 10.1.1.0 .

        3. This is common used situation when user connection to database not as schema owner. In same cases login can be disabled for schema owner from security point of view.

        4. Similar sql sentences will be run correct on DB2, Oracle & PostgreSQL.

        Show
        Igor Azarny added a comment - I think this is a BUG. 1. At sql sentence "select * from lcp.transportgrp where transportgrp.name = 'test 2';" schema name ALREADY present at from clause. So why we need to specify schema name where clause? 2. The same sql run correctly on versions before 10.1.1.0 . 3. This is common used situation when user connection to database not as schema owner. In same cases login can be disabled for schema owner from security point of view. 4. Similar sql sentences will be run correct on DB2, Oracle & PostgreSQL.
        Hide
        geoff hendrey added a comment -

        can you tell me if this a related issue?

        The following is the first statement I execute against a schema 'blogs'.
        I've got the database connection URL to ...create=true....

        and I'm connecting to the database with a user of "blogs" and password of "blogs". So presumably the act of connecting to the db to execute this statement should create the 'blogs' schema.

        CREATE TABLE BLOGSCOM_BLOGSUSERS(PK INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,username VARCHAR(16) NOT NULL CONSTRAINT BLOGSCOMBLOGSUSERS_UNIQUE_username UNIQUE CONSTRAINT BLOGSCOMBLOGS_USERS_PASSWORD_username CHECK(LENGTH(username)>7),password VARCHAR (32672) NOT NULL , PRIMARY KEY(PK))

        java.sql.SQLSyntaxErrorException: Schema 'BLOGS' does not exist
        at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
        at org.apache.derby.client.am.Statement.executeUpdate(Unknown Source)
        at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)

        But as you can see from the above, it says "BLOGS" does not exist.

        If I execute some other statement first, then the statement in question proceeds just fine. Also, if I eliminate the constraints related to the password length, this statement will happily execute as the first statement executed and the act of connecting will create the database and schema. So I believe this has something to do with the constraints.

        Show
        geoff hendrey added a comment - can you tell me if this a related issue? The following is the first statement I execute against a schema 'blogs'. I've got the database connection URL to ...create=true.... and I'm connecting to the database with a user of "blogs" and password of "blogs". So presumably the act of connecting to the db to execute this statement should create the 'blogs' schema. CREATE TABLE BLOGSCOM_ BLOGS USERS(PK INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,username VARCHAR(16) NOT NULL CONSTRAINT BLOGSCOM BLOGS USERS_UNIQUE_username UNIQUE CONSTRAINT BLOGSCOM BLOGS _USERS_PASSWORD_username CHECK(LENGTH(username)>7),password VARCHAR (32672) NOT NULL , PRIMARY KEY(PK)) java.sql.SQLSyntaxErrorException: Schema 'BLOGS' does not exist at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) at org.apache.derby.client.am.Statement.executeUpdate(Unknown Source) at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228) But as you can see from the above, it says "BLOGS" does not exist. If I execute some other statement first, then the statement in question proceeds just fine. Also, if I eliminate the constraints related to the password length, this statement will happily execute as the first statement executed and the act of connecting will create the database and schema. So I believe this has something to do with the constraints.
        Hide
        John H. Embretsen added a comment -

        geoff:

        I think your case is an issue related to implicit schema creation, while the original issue is more a request for an "automatic schema resolution in where clauses"-feature. I think you should file a new issue for your case.

        You are right that the exception is related to the constraints in your CREATE TABLE statement. In fact, your issue may be reproduced using the following ij script:

        connect 'jdbc:derby:db;create=true;user=blogs';
        create table users(username varchar(16) CHECK(LENGTH(username)>7));

        If the CHECK constraint is removed, it works fine. As far as I can see, the documentation does not mention any restrictions with regards to constraints used in CREATE TABLE statements, so this seems like a bug (a documentation bug if nothing else). For example, in the Reference Manual ("CONSTRAINT clause" section), I found:

        "Check constraints specified as part of a table definition can have column references identifying columns previously defined in the CREATE TABLE statement."

        It seems like the above is not true in the case of implicit schema creation, i.e. when the schema does not yet exist. There is more information about implicit schema creation in the FAQs (but not in the manuals as far as I know):

        http://db.apache.org/derby/faq.html#schema_exist

        Show
        John H. Embretsen added a comment - geoff: I think your case is an issue related to implicit schema creation, while the original issue is more a request for an "automatic schema resolution in where clauses"-feature. I think you should file a new issue for your case. You are right that the exception is related to the constraints in your CREATE TABLE statement. In fact, your issue may be reproduced using the following ij script: connect 'jdbc:derby:db;create=true;user=blogs'; create table users(username varchar(16) CHECK(LENGTH(username)>7)); If the CHECK constraint is removed, it works fine. As far as I can see, the documentation does not mention any restrictions with regards to constraints used in CREATE TABLE statements, so this seems like a bug (a documentation bug if nothing else). For example, in the Reference Manual ("CONSTRAINT clause" section), I found: "Check constraints specified as part of a table definition can have column references identifying columns previously defined in the CREATE TABLE statement." It seems like the above is not true in the case of implicit schema creation, i.e. when the schema does not yet exist. There is more information about implicit schema creation in the FAQs (but not in the manuals as far as I know): http://db.apache.org/derby/faq.html#schema_exist
        Hide
        Mike Matrigali added a comment -

        Triaged July 10, 2009: assigned normal urgency.

        Show
        Mike Matrigali added a comment - Triaged July 10, 2009: assigned normal urgency.
        Mike Matrigali made changes -
        Field Original Value New Value
        Urgency Normal
        Kathey Marsden made changes -
        Labels derby_triage10_5_2
        Gavin made changes -
        Workflow jira [ 12325341 ] Default workflow, editable Closed status [ 12798754 ]

          People

          • Assignee:
            Unassigned
            Reporter:
            Igor Azarny
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development