Apache OpenOffice (AOO) Bugzilla – Issue 114304
Default not being set properly when creating a boolean field via SQL
Last modified: 2013-08-07 15:45:09 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>.
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.
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.
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.
Created attachment 71557 [details] bog doc example
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.
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.
@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
Created attachment 71564 [details] changed boolean field - and form control is set to tri-state true
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?
Created attachment 71576 [details] aws-null-test-v2.odb - Demonstrates issues between default values defined in SQL and those defined in the GUI
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.