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