[jira] [Updated] (DERBY-6918) Problem with schema name starting with number followed by a dot

["Bryan Pendleton (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Bryan Pendleton updated DERBY-6918:
-----------------------------------
    Attachment: schemaNames.diff

Attached "schemaNames.diff" is a small patch that
cleans up the handling of schema names in the
foreign key maintenance logic in DMLModStatementNode
and DeleteNode: rather than pasting the schema name
and table name together, then bursting them back apart,
the schema name and table name are simply treated
separately, which seems cleaner and simpler to me.

The test case from the repro script is also added to the
regression test suites.

From a quick scan of the Subversion history, it seems like
this part of the code dates back to the original donation
of the Derby code by IBM. So this is old, old code; it's a
bit interesting that we hadn't encountered this problem
until now. I didn't take the repro script and hunt back through
old releases to confirm that it's "always" been broken, though.

But if this patch proves to work, it could certainly be
back-ported to older Derby releases, if anyone so desired.

I'm running the full set of tests and will report back on my findings.

> Problem with schema name starting with number followed by a dot
> ---------------------------------------------------------------
>
>                 Key: DERBY-6918
>                 URL: https://issues.apache.org/jira/browse/DERBY-6918
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.13.1.1
>            Reporter: Hooman Valibeigi
>            Assignee: Bryan Pendleton
>         Attachments: schemaNames.diff
>
>
> It seems that there is a problem with schema names that start with a number followed by a dot.
> Please take a look at the following script. Everything works fine but the last line which is a delete statement. Note that having the foreign key constraints was necessary to make it reproduce the problem.
> create schema "1.a";
> create table "1.a"."role"
> (
> "id" integer generated always as identity,
> "name" varchar(255) not null
> );
> alter table "1.a"."role" add constraint "role_pk"
> primary key ("id");
> create table "1.a"."user"
> (
> "id" integer generated always as identity,
> "name" varchar(255) not null
> );
> alter table "1.a"."user" add constraint "user_pk"
> primary key ("id");
> create table "1.a"."user_role"
> (
> "role" integer not null,
> "user" integer not null
> );
> alter table "1.a"."user_role" add constraint "user_role_fk1"
> foreign key ("role")
> references "1.a"."role" ("id")
> on delete cascade;
> alter table "1.a"."user_role" add constraint "user_role_fk2"
> foreign key ("user")
> references "1.a"."user" ("id")
> on delete cascade;
> alter table "1.a"."user_role" add constraint "user_role_u1"
> unique ("user", "role");
> insert into "1.a"."role" ("name") values ('r1');
> insert into "1.a"."user" ("name") values ('u1');
> insert into "1.a"."user_role" ("role","user") values (1,1);
> select * from "1.a"."user";
> delete from "1.a"."user";
> Last delete statement fails. The error is:
> Schema '1' does not exist [SQL State=42Y07, DB Errorcode=20000] 



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)