Issue 117443 - [cws hsqldb19] Table wizard override AutoValuee setting
Summary: [cws hsqldb19] Table wizard override AutoValuee setting
Status: CONFIRMED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: DEV300m101
Hardware: All All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
: 118657 (view as issue list)
Depends on:
Blocks:
 
Reported: 2011-03-17 14:36 UTC by r4zoli
Modified: 2014-04-04 19:44 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description r4zoli 2011-03-17 14:36:41 UTC
To reproduce it open any file in cws hsqldb19, I tested only win7 version.
 
Table wizard:
- create new table with wizard
- select Bussiness category and Assets sample table in first step
- Select all fields
- Change AssetID "AutoValue" property to "Yes", "Auto-increment statement" remains empty (in earlier versions, this get "IDENTITY") 
- On next page select AssetID as primary key (Autovalue not selected automatically as in earlier versions)
If you finish in that state the AssetID field "Autovalue" property set as "No"
You must select Autovalue option in 3rd wizard step.
Comment 1 r4zoli 2011-03-17 15:01:07 UTC
More test results. 

The Assets table created with table wizard and AssetID set PK with "AutoValue" property set as "No"
Save file and enter in datainput mode.
Created another "Categories" table in same way.

No autovalue in AssetID.

The database script part:
CREATE CACHED TABLE PUBLIC."Assets"("AssetID" INTEGER NOT NULL PRIMARY KEY,"AssetCategoryID" INTEGER,"BarcodeNumber" VARCHAR(50),"Comments" VARCHAR(65000),"CurrentValue" DECIMAL(10),"DateAcquired" DATE,"DateSold" DATE,"DepartmentID" INTEGER,"DepreciableLife" INTEGER,"DepreciationMethod" VARCHAR(50),"Description" VARCHAR(250),"EmployeeID" INTEGER,"Make" VARCHAR(50),"Model" VARCHAR(50),"ModelNumber" VARCHAR(50),"NextScheduledMaintenance" DATE,"PurchasePrice" DECIMAL(10),"SalvageValue" DECIMAL(10),"SerialNumber" VARCHAR(50),"StatusID" INTEGER,"VendorID" INTEGER)
CREATE CACHED TABLE PUBLIC."Categories"("CategoryID" INTEGER NOT NULL PRIMARY KEY,"CategoryName" VARCHAR(50))


To change it, I opened table in edit mode, tried to set "Yes" the "Autovalue" property.
When clicked on Save table settings, I get warning: 

Warning!
The column "AssetID" could not be changed. Should the column instead be deleted and the new format appended?
SQL Status: 42581
Error code: -5581

unexpected token: GENERATED


When clicked on Yes, old field deleted and new field added without any change in AutoValue property, it remains "No".
If I try to repeat it same results, as many times as I try to change this property.

After I closed database and reopend, and opened tables to edit, the property set to yes, but the key mark (PK) lost, and not any field selected as PK.
I could add PK by mouse, and save it.

Whe in table wizard last step I select modify design.
Change autovalue to yes, same error message in first try, new field adds, vith "No".
On second try I cold set as "Yes" and save changes, this changes remains.

But if you open table to data input the autovalue works.
If You open back in edit mode, the autoValue set as "No".

Categories table, similar problems.

In database script, you could find the following table structure:
CREATE CACHED TABLE PUBLIC."Assets"("AssetCategoryID" INTEGER,"BarcodeNumber" VARCHAR(50),"Comments" VARCHAR(65000),"CurrentValue" DECIMAL(10),"DateAcquired" DATE,"DateSold" DATE,"DepartmentID" DOUBLE,"DepreciableLife" INTEGER,"DepreciationMethod" VARCHAR(50),"Description" VARCHAR(250),"EmployeeID" INTEGER,"Make" VARCHAR(50),"Model" VARCHAR(50),"ModelNumber" VARCHAR(50),"NextScheduledMaintenance" DATE,"PurchasePrice" DECIMAL(10),"SalvageValue" DECIMAL(10),"SerialNumber" VARCHAR(50),"StatusID" INTEGER,"VendorID" INTEGER,"AssetID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL)
ALTER TABLE PUBLIC."Assets" ALTER COLUMN "AssetID" RESTART WITH 0
CREATE CACHED TABLE PUBLIC."Categories"("CategoryName" VARCHAR(50),"CategoryID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL)


If I created same tables with option in 3rd step with allowed "Auto value" the script is:  
CREATE CACHED TABLE PUBLIC."Assets"("AssetID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"AssetCategoryID" INTEGER,"BarcodeNumber" VARCHAR(50),"Comments" VARCHAR(65000),"CurrentValue" DECIMAL(10),"DateAcquired" DATE,"DateSold" DATE,"DepartmentID" INTEGER,"DepreciableLife" INTEGER,"DepreciationMethod" VARCHAR(50),"Description" VARCHAR(250),"EmployeeID" INTEGER,"Make" VARCHAR(50),"Model" VARCHAR(50),"ModelNumber" VARCHAR(50),"NextScheduledMaintenance" DATE,"PurchasePrice" DECIMAL(10),"SalvageValue" DECIMAL(10),"SerialNumber" VARCHAR(50),"StatusID" INTEGER,"VendorID" INTEGER)
ALTER TABLE PUBLIC."Assets" ALTER COLUMN "AssetID" RESTART WITH 0
CREATE CACHED TABLE PUBLIC."Categories"("CategoryID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"CategoryName" VARCHAR(50))
ALTER TABLE PUBLIC."Categories" ALTER COLUMN "CategoryID" RESTART WITH 0

Different ways creates different scripts in primary key.
Comment 2 Oliver-Rainer Wittmann 2012-06-13 12:18:06 UTC
getting rid of value "enhancement" for field "severity".
For enhancement the field "issue type" shall be used.
Comment 3 j.nitschke 2014-04-04 19:43:52 UTC
*** Issue 118657 has been marked as a duplicate of this issue. ***