Re: AW: ArrayIndexOutOfBoundsException when creating view

Thanks for the test case.  I was able to reproduce the problem.

The issue is that in this query, the column aliases in the view are 
conflicting with the identical table column names.  Defining a column 
alias the same as a column name is normally permitted, but in a view it 
causes a column mapping problem.

The problem is not present in the following rewrite;

CREATE VIEW V_DATEN_DBA AS
SELECT 	DA.SCHLUESSEL,
		DA.BENUTZERGRUPPE,
		DA.USERNAME,
		DB.KUNDE,
		DB.STANDZEIT,
		DB.EINSATZDAUER,
		V.VERSION
FROM	DATEN_ALLG DA, DATEN_BEARB DB, VERSION V
WHERE 	DA.SCHLUESSEL=DB.DATEN_ALLG_FK
AND 	DA.SCHLUESSEL=V.REFNR
AND 	DB.DATEN_ALLG_FK=V.REFNR;

The problem also is not present when an alias is defined that is 
different than the column name.  It only happens when the alias is the 
same as the column name.

I will fix the bug.  Thanks,
Toby.


Klaudio Kaiser wrote:

> Hi,
> 
> 
>>Did you get the error as soon as you ran the CREATE VIEW command?  Did 
>>you alter one of the tables the view is based on?
> 
> 
> The error ocuured as soon as i ran the CREATE VIEW command. I did not
> alter any involved table.
> 
> 
>>Does only the 'SELECT' part of the query work as expected?  eg. the 
>>following query;
>>
>>SELECT 	DA.SCHLUESSEL AS SCHLUESSEL,
>>		DA.BENUTZERGRUPPE AS BENUTZERGRUPPE,
>>		DA.USERNAME AS USERNAME,
>>		DB.KUNDE AS KUNDE,
>>		DB.STANDZEIT AS STANDZEIT,
>>		DB.EINSATZDAUER AS EINSATZDAUER,
>>		V.VERSION AS VERSION
>>FROM	DATEN_ALLG DA, DATEN_BEARB DB, VERSION V
>>WHERE 	DA.SCHLUESSEL=DB.DATEN_ALLG_FK
>>AND 	DA.SCHLUESSEL=V.REFNR
>>AND 	DB.DATEN_ALLG_FK=V.REFNR;
> 
> 
> the "SELECT...." part for itself works without problems...
> 
> 
>>Could you explain layout of the 'DATEN_ALLG', 'DATEN_BEARB' and 
>>'VERSION' tables so I can try and reproduce the error.
> 
> 
> The tables have the following layout:
> 
> ---------------------------------------------------------------------
> CREATE TABLE DATEN_ALLG
> (
> 	"SCHLUESSEL" CHARACTER (29) NOT NULL,
> 	"BENUTZERGRUPPE" VARCHAR (16) NOT NULL,
> 	"USERNAME" VARCHAR (16) NOT NULL,
> 	PRIMARY KEY (SCHLUESSEL)
> );
> 
> CREATE TABLE DATEN_BEARB
> (
> 	"SCHLUESSEL" INTEGER DEFAULT UNIQUEKEY('DATEN_BEARB') NOT NULL,
> 	"DATEN_ALLG_FK" CHARACTER (29) NOT NULL,
> 	"KUNDE" VARCHAR (128),
> 	"STANDZEIT" SMALLINT,
> 	"EINSATZDAUER" SMALLINT,
> 	PRIMARY KEY (SCHLUESSEL),
> 	CONSTRAINT C02_DATEN_ALLG_FK FOREIGN KEY (DATEN_ALLG_FK) REFERENCES
> DATEN_ALLG (SCHLUESSEL) ON DELETE CASCADE
> );
> 
> CREATE TABLE VERSION
> (
> 	"REFNR" CHARACTER (29) NOT NULL,
> 	"VERSION" INTEGER NOT NULL,
> 	"CHANGEDATE" TIMESTAMP NOT NULL,
> 	PRIMARY KEY (REFNR),
> 	CONSTRAINT C_CHECK_UNIQUE UNIQUE (REFNR, VERSION, CHANGEDATE)
> );
> 
> CREATE VIEW V_DATEN_DBA AS
> SELECT 	DA.SCHLUESSEL AS SCHLUESSEL,
> 		DA.BENUTZERGRUPPE AS BENUTZERGRUPPE,
> 		DA.USERNAME AS USERNAME,
> 		DB.KUNDE AS KUNDE,
> 		DB.STANDZEIT AS STANDZEIT,
> 		DB.EINSATZDAUER AS EINSATZDAUER,
> 		V.VERSION AS VERSION
> FROM	DATEN_ALLG DA, DATEN_BEARB DB, VERSION V
> WHERE 	DA.SCHLUESSEL=DB.DATEN_ALLG_FK
> AND 	DA.SCHLUESSEL=V.REFNR
> AND 	DB.DATEN_ALLG_FK=V.REFNR;
> ---------------------------------------------------------------------



---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]