db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "John H. Embretsen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-568) ERROR 42Y07: Schema 'SchemaName' does not exist. But Schema already present in sql sentence
Date Mon, 03 Sep 2007 08:49:19 GMT

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

John H. Embretsen commented on DERBY-568:
-----------------------------------------

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

> ERROR 42Y07: Schema 'SchemaName' does not exist. But Schema already present in sql sentence
> -------------------------------------------------------------------------------------------
>
>                 Key: DERBY-568
>                 URL: https://issues.apache.org/jira/browse/DERBY-568
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.1.1.0
>         Environment: Win XP prof, Java SDK  1.4.2_06, Derby 10.1.1.0
>            Reporter: Igor Azarny
>
> 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 **********************/

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message