Issue 114304

Summary: Default not being set properly when creating a boolean field via SQL
Product: Base Reporter: anotheralan <mailbag>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: drewjensen.inbox, issues, r4zoli
Version: OOo 3.2.1   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
bog doc example
none
changed boolean field - and form control is set to tri-state true
none
aws-null-test-v2.odb - Demonstrates issues between default values defined in SQL and those defined in the GUI none

Description anotheralan 2010-09-04 12:06:02 UTC
Using OpenOffice 3.2.1 (Build: 9502) on Mac OSX 10.6.4.

When attempting to use a direct SQL Create Table statement to set a default
value for a boolean field (via the Tools->SQL menu), the field is created
properly, but the default does not get applied. 

Based off the documentation here:
http://hsqldb.org/doc/guide/ch09.html#create_table-section

If this statement is run:
CREATE TABLE "testTable" ("testBool" BOOLEAN DEFAULT TRUE NOT NULL);

a new table should be created that has a "testBool" field the defaults to TRUE
(which would show up as "Yes" in the Base display). However, with the above
statement the default is actual set to FALSE (which shows up as "No" in the Base
display).

(Note: after running an SQL command to create a table, you need to run the
View->Refresh Tables menu option to see new tables.)

It does not appear that the default is being set improperly, but rather that the
default is being ignored all together. 


For some further examples, running the following:

CREATE TABLE "testTableB" (

  "pKey" INTEGER NOT NULL IDENTITY PRIMARY KEY,
  
  "test01" BOOLEAN DEFAULT TRUE NOT NULL,
  "test02" BOOLEAN DEFAULT True NOT NULL,
  "test03" BOOLEAN DEFAULT true NOT NULL,
  "test04" BOOLEAN DEFAULT 1 NOT NULL,
  
  "test05" BOOLEAN DEFAULT FALSE NOT NULL,
  "test06" BOOLEAN DEFAULT False NOT NULL,
  "test07" BOOLEAN DEFAULT false NOT NULL,
  "test08" BOOLEAN DEFAULT 0 NOT NULL,
  
  "test09" BOOLEAN DEFAULT TRUE,
  "test10" BOOLEAN DEFAULT True,
  "test11" BOOLEAN DEFAULT true,
  "test12" BOOLEAN DEFAULT 1,
  
  "test13" BOOLEAN DEFAULT FALSE,
  "test14" BOOLEAN DEFAULT False,
  "test15" BOOLEAN DEFAULT false,
  "test16" BOOLEAN DEFAULT 0

);

Will produce a table where all the fields with "NOT NULL" end up with a default
value of "No" regardless of if "TRUE" or "FALSE" was requested. Any fields
without the "NOT NULL" spec end up with a default of <none>.
Comment 1 r4zoli 2010-09-04 13:25:23 UTC
I can confirm it in OOO330m6 on win7, changed OS, platform.

I tested more with Villeroy example from:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=61&t=33816

CREATE TABLE "testTable" (ID INT PRIMARY KEY, "testBool" BOOLEAN DEFAULT TRUE
NOT NULL);
Opened table data view, added data, only into the ID filed.
First record inserted default value, I used ID=1.
Second record with ID=2 gives error: Error inserting the new record
More on error:  
"SQL Status: 23000
Error code: -10

Attempt to insert null into a non-nullable column: column: testBool table:
testTable1 in statement [INSERT INTO "testTable1" ( "ID","testBool") VALUES ( ?,?)]"
No changes after closing reopening odb file, or restart OOo.

Set target, assign developer.
Comment 2 anotheralan 2010-09-04 15:41:58 UTC
Based off the things Villeroy saw, I've opened another issue
(http://www.openoffice.org/issues/show_bug.cgi?id=114309) that might be related
to this one where Boolean fields created with NOT NULL that do not have a
default value try to send NULL values when records are added. 
Comment 3 drewjensen.inbox 2010-09-04 15:46:26 UTC
Using Ubuntu 10.04, OOo 3.2 (go-oo) and 3.2.1 (vanilla) I find no difference -
Inserts via direct SQL with a default setting is working.
Inserts via data controls under Base not working.

Open the attached file - 

To see that SQL inserts are working open the SQL window and enter
INSERT INTO BOOLS ("Alpha") values ('yet another' )

After executing if you open the bools table or form you will see that the
inserted record has the default of TRUE set for the boolean field.

Now open either the table dataview or bools form and try to enter a new record -
you can do so without entering anything in the second "boolean" field - because
the setting on the datasource is set NOT to check for required fields, since the
boolean field is also defaulted NOT NULL.
Comment 4 drewjensen.inbox 2010-09-04 15:47:51 UTC
Created attachment 71557 [details]
bog doc example
Comment 5 drewjensen.inbox 2010-09-04 15:59:33 UTC
Sorry for splitting posts - last paragraph(s) should be
You can not enter record via the dataview without entering explicitly a value
for the boolean field, and the controls do not pick up the default value - but
that has always been true I think. 

In the form you can enter a record without touching the boolean field but then
the, when you do so the record is set with a FALSE in the boolean field - BUT -
this is because the checkbox field on the form is set to be for TRI-STATE =
FALSE - with this setting it is not picking up the default setting from the
database.

If you open that form and change the setting for that control to TRI-STATE
equals TRUE then the control will correctly pick up the default value TRUE and
you see at runtime that it will not accept the NULL state, since the table does
not accept it - try with a field set to default TRUE and don't add the NOT NULL
and you will see the difference in the form is picked up.

So there really are two issues here - one the checkbox control does not pickup
the table default setting when tri-state = FALSE

Two - the checkbox control in the dataview grid is not tri-state = TRUE

At least that's how it looks to me.
Comment 6 villeroy 2010-09-04 20:38:59 UTC
In this context I discovered that any field type except Boolean can be defined
as DEFAULT NULL NOT NULL.
The grids and the forms will leave the field blank and Base will complain one
way or the other when the user tries to store without filling the empty field.

Try this with a boolean field. For a mandatory boolean field (NOT NULL) Base
will always put a false False into the grid or form control respectively.
Comment 7 drewjensen.inbox 2010-09-05 04:18:34 UTC
@villeroy - I can't do that here - using 3.2.1 (vanilla) on linux if I try to
apply, using the SQL window, your example:

ALTER TABLE BOOLS ADD COLUMN ABOOLS BOOLEAN DEFAULT NULL NOT NULL

I get this result:
1: Attempt to insert null into a non-nullable column: column: ABOOLS table:
BOOLS in statement [ALTER TABLE BOOLS ADD COLUMN ABOOLS BOOLEAN DEFAULT NULL NOT
NULL]

Which is correct.

In fact here the only statement it will take, without a default setting is:
ALTER TABLE BOOLS ADD ABOOL BOOLEAN

And then yup your right - thee is no way to get a null with the form controls -
or display a null atually it seems - attached another file
Comment 8 drewjensen.inbox 2010-09-05 04:21:36 UTC
Created attachment 71564 [details]
changed boolean field - and form control is set to tri-state true
Comment 9 anotheralan 2010-09-06 03:27:55 UTC
I was going to start doing some additional testing on Boolean fields, but wanted
to get a baseline with text fields first and discovered something interesting.
It looks like the defaults that are set in the Table Design GUI and those that
are defined via SQL don't talk to each other properly. 

(Note: I'm attaching a file in the next comment that contains the following
examples for reference.)

##################################################
# Scenario 1
##################################################

If I create a new table (named "guiTable") via the "Create Table in Design
View..." GUI with the following:

Field 1 Name: pKey (set to primary key)
Field 1 Type: INTEGER
Field 1 AutoValue: Yes

Field 2 Name: notNullText
Field 2 Type: VARCHAR
Field 2 Entry required: Yes
Field 2 Default Value: DefaultText

Field 3 Name: nullableText
Field 3 Type: VARCHAR
Field 3 Entry required: No
(No Default value set)

I get the following results:

---

Table Data View:

As soon as I open the table grid in the Table Data View GUI, I see the
"DefaultText" already populated in the "notNullText" field where the next record
will go. As soon as one record is created, once again, the "DefaultText" shows
up in "notNullText". (Or, at a minimum, as soon as I start entering text into
the "nullableText" field the "notNullText" auto populates with "DefaultText").

Entering some text into "nullableText" and not messing with "notNullText" allows
for entries to be created with no issue.

---

SQL Insert Test 1 using the "Tools->SQL..." menu option to run:

INSERT INTO "guiTable" ("nullableText") VALUES ('SQL addition');

I get the error:

1: Attempt to insert null into a non-nullable column: column: notNullText table:
guiTable

---

SQL Insert Test 2 using the "Tools->SQL..." menu option to run:

INSERT INTO "guiTable" ("notNullText", "nullableText") VALUES ('SQL addition',
'SQL addition');

The command executes without error. 

---

Form Test 1 - Only contains "nullableText" field. 

I can add new records with no issue. Everything works as expected. Examining the
table in data view, each new entry has the requested "nullableText" and the
"notNullText" has the proper "DefaultText" string. (See form "guiTable Only
Nullable" in "aws-null-test-v2.odb" attached in the next comment.)

---

Form Test 2 - Contains both "notNullText" and "nullableText" fields. 

As soon as the form is open to create a new record, the "notNullText" field
displays the "DefaultText" as specified via the Default Vaule in the GUI when
the table was created. Entering new records works without issue. It's possible
to raise an error by deleting the text from the "notNullText" field, but that is
expected. (See form "guiTable Both Fields" in "aws-null-test-v2.odb" attached in
the next comment.)


##################################################
# Scenario 2
##################################################

If I create an identical table (named "sqlTable") via SQL using the
"Tools->SQL..." menu option instead of the GUI. The SQL command for the talbe
creation is:

CREATE TABLE "sqlTable" (
  "pKey" INTEGER NOT NULL IDENTITY PRIMARY KEY,
  "notNullText" VARCHAR(50) DEFAULT 'DefaultText' NOT NULL, 
  "nullableText" VARCHAR(50)
);

I get the following results:

---

Table Data View:

Unlike the guiTable, when the table is opened under the Table Data View GUI, the
"notNullText" field *do not* have the "DefaultText" value already in place for
the next record that will be created. 

I have to enter text into the "nullableText" field and then hit tab or return to
create the record before the "DefaultText" shows up. 

I don't have any issue with entering data, even though it behaves differently. 

---

SQL Insert Test 1 using the "Tools->SQL..." menu option to run:

INSERT INTO "sqlTable" ("nullableText") VALUES ('SQL addition');

The command executes without error. Even though no value was defined for the
"notNullText" field, when examining the table data directly, the "DefaultText"
value shows up as expected.  

---

SQL Insert Test 2 using the "Tools->SQL..." menu option to run:

INSERT INTO "sqlTable" ("notNullText", "nullableText") VALUES ('SQL addition',
'SQL addition');

The command executes without error. 

---

Form Test 1 - Only contains "nullableText" field. 

I can add new records with no issue. Eveyrthing works as expected. Examining the
table in data view, each new entry has the requested "nullableText" and the
"notNullText" has the proper "DefaultText" string. (See form "sqlTable Only
Nullable" in "aws-null-test-v2.odb" attached in the next comment.)

---

Form Test 2 - Contains both "notNullText" and "nullableText" fields. 

When the form is opened the "notNullText" field is empty. If you try to create a
new record without filling it in, an error is thrown. As long as a value is
entered, the record will be created properly. (See form " sqlTable Both Fields"
in "aws-null-test-v2.odb" attached in the next comment.)



##################################################
Comparison
##################################################

Here's a brief overview of the differences I see between the two types of tables. 

--- 

Table Data View:

In both scenarios, the "DefaultText" works properly when creating new records in
the Table Data View GUI, but they don't work the same. With a table that is
created with the "Create Table in Design View..." GUI the "DefaultText" appears
to get created before sending the request to the underlying database. In
contrast, the table created directly via SQL appears to populate the
"DefaultText" during/after record creation. 

---

SQL Inserts:

For a table that is created via the GUI, it's not possible to send SQL without
an value for a field that is defined as require even if the GUI has a Default
value defined in it. Doing so would results in an error. I think this means that
it's not possible to use SQL to insert a new record that contains a default
value that was defined in the GUI. This strikes me as a pretty big issue if you
are expecting to be able to interact with the database via SQL commands. 

In contrast, if SQL is used to define a default value, it is not necessary to
explicitly create a value for that field in the SQL command. This is the way I
would have expected both cases to work. 

---

Forms:

The difference I see here is that a DEFAULT value that is defined via SQL does
not propagate to the form itself. Similar to before, this appears to mean that
you can't define a default via SQL that can be used by the GUI. 



##################################################
# Conclusion
##################################################

From what I see here, it looks like there are two mechanisms that deal with
default values for fields and they don't fully interact. Specifically, defaults
that are defined in the GUI and those that are defined via SQL are largely
invisible to each other. For example, if you define a default value in SQL for a
text field. This value does not show up in the Table Design View GUI. Likewise,
if you set a default value in the GUI, direct SQL commands cannot rely on it
when inserting data.

The examples here were all based on text fields, but based on what I've seen,
this is the same root cause behind the original issue I had with the Boolean
values. 

This seems like a much larger issue than the symptom that originally sparked
creation of this ticket. The question becomes, should that issue be addressed in
this ticket, or should a new one be created?
Comment 10 anotheralan 2010-09-06 03:30:38 UTC
Created attachment 71576 [details]
aws-null-test-v2.odb - Demonstrates issues between default values defined in SQL and those defined in the GUI
Comment 11 ocke.janssen 2011-01-06 12:00:10 UTC
JFI:

The default value which you enter in the table design window is different to the
default value you define in the SQL statement. The default value in the table
design defines the value which should be shown in the control when you want to
insert new values.