Apache OpenOffice (AOO) Bugzilla – Issue 115420
Base: NULL date import breaks type recognition & crashes OOo 3.3.0
Last modified: 2011-03-30 11:01:30 UTC
When importing into Base any Calc spreadsheet with /y/ data rows and a column of formatted dates, if the max lines for auto-type recognition is /x/ and the cell on data row /x/+1 or /y/, whichever is smaller, in the date column is empty, OOo incorrectly auto-recognises the next column to have a DATE type if it's non-numeric (e.g., text). Manually changing it to a Text type shows correct data but the format example incorrectly shows a date string. Changing it to Memo type will result in *corrupt* data or, if any data contain the string 'ORA-20001' and the OS is Windows, even a *crash* on opening the imported table. [NB: The reason why this fails on data row /x/+1 vs. /x/ is because of another bug. See Issue #115308. If Issue #115308 is fixed, then the current bug will occur on data row /x/.] This problem is attested in the following builds and OS: - OOO320m12 on Ubuntu 10.04 in Oracle VM VirtualBox 3.2.10 r66523 on 64-bit English Windows Vista Business SP2 - OOO320m18 on 64-bit English Windows 7 Ultimate - OOO320m19 on Ubuntu 10.10 in Oracle VM VirtualBox 3.2.10 r66523 on 32-bit English Windows XP Professional SP3 - OOO330m12 on 32-bit English Windows XP Professional SP3 - OOO330m13 on 32-bit English Windows XP Professional SP3 Test case: I. Create new database: A. Launch Base. B. Select 'Create a new database'. C. Click 'Next'. D. Select 'Yes, register...'. E. Check 'Open the database for editing'. F. Uncheck 'Create tables...'. G. Click 'Finish'. H. Type an arbitrary file name. I. Click 'Save'. II. Open the attached "b-crash-import-null_date.ods" file. Note the following: A. It contains 2 sheets each having 10 data rows & a column of dates with data cells 5 and 10 being empty. B. The 'Date' column, excluding the header but including the empty cells, has a date format of YYYY-MM-DD set via <ctrl-1>|Numbers|Date. C. Data row 8 in sheet 'Wrong Type and Crash' contains the string 'ORA-20001', which is its only difference in content from sheet 'Wrong Type No Crash'. III. Test no crash scenarios: A. Select sheet 'Wrong Type No Crash'. B. Copy data: 1. Press <ctrl-home>. 2. Press <ctrl-shift-end>. 3. Press <ctrl-c>. C. Switch to Base. D. Test scenario 1: wrong format example 1. Create table: a. Select 'Tables' in the Database panel. b. Right-click in the 'Tables' pane. c. Select 'Paste'. d. Give the table an arbitrary name (or use default). e. Select 'Definition and data'. f. Check 'Use first line...'. g. Check or uncheck 'Create primary key'. (Doesn't matter.) h. Click 'Next'. i. Click double right arrows. j. Click 'Next'. k. Set max lines for auto-type recognition to 4 or any value >= 9 (i.e., actually reading 5 or 10 lines). l. Click 'Auto'. m. Select the 'Desc' field. Note the field type has been incorrectly recognised as 'Date [ DATE ]'. n. Change field type to a Text type. o. Click 'Create'. p. Depending on the setting in step g, a dialog box may pop up asking to confirm primary key creation. Click 'Yes' or 'No'. (Doesn't matter.) 2. Open table: a. Double-click on the table created in step 1. Note the 'Desc' column shows correct data. b. Close table data view window. c. Right-click on table. d. Select 'Edit'. e. Click on the 'Desc' field. Note the 'Format example' field at the bottom pane incorrectly shows a date string ('1900-01-01' on Win, '01/01/00' on Ubuntu, or even some other value depending on the OS locale). f. Close table design window. E. Test scenario 2: corrupt data 1. Repeat D.1.a-c. 2. Give the table a different name. 3. Repeat D.1.e-m. 4. Change field type to Memo. 5. Repeat D.1.o-p. 6. Double-click on the table created in step 5. Note the 'Desc' column shows *corrupt* data. 7. Repeat D.2.b-f. IV. Test crash scenario: A. Switch to Calc. B. Select sheet 'Wrong Type and Crash' in the .ods file. C. Repeat steps III.D.1.a-c. D. Give the table a name different from what has been created so far. E. Repeat steps III.D.e-j. F. Set max lines for auto-type recognition to any value >= 9. G. Repeat steps III.D.l-m. H. Repeat steps III.E.4-6. Now OOo displays *corrupt* data in the 'Desc' column up to data row 7 (data row 8 contains the string 'ORA-20001') and then *crashes* if the OS is Windows; if the OS is Ubuntu, *corrupt* data is shown w/o crashing. Control cases: If any of the following conditions is fulfilled, then the problem won't occur: 1. Data cell /x/+1 or /y/, whichever is smaller, of the date column contains a date (e.g., by setting max lines to other values in step III.D.1.k) even though some other cells in the column may be empty. 2. The next column is formatted with the @ format code via <ctrl-1>|Numbers|Text. Note the default format is 'General'. 3. Fields are manually given their correct types w/o clicking 'Auto'. 4. Importing from Excel. (Try repeating the above tests with the attached .xls file.) 5. Importing from Calc in OOO300m9. See attached test results across different builds and applications ("b-crash-import-null_date-results.ods"). They indicate a regression from a previous build, as text recognition was correct, data was correct, and there was no crash whereas starting at least from OOO320m12 text recogition may fail and result in corrupt data or even a crash.
Created attachment 72863 [details] Test .ods file
Created attachment 72864 [details] Test .xls file
Created attachment 72865 [details] Test results across different builds, apps, & OS
I tested on win7 with OOo 3.3.RC3 (OOO330m10) en_US, with en_US and HU locale, no crash when creating new table from "b-crash-import-null_date.ods" file, from sheet 'Wrong Type and Crash'. If I use default settings (line max 10), and press Auto, the Desc recognized wrongly as Date, If I change to Text, the table creates, with all data. Only thing is The Format example is '1900-01-01', if I open it shows Text settings. If I use 6 or 8 as line max all data recognized correctly, without any glitch, no crash, all data in resulting table. Set priority back to P3 according: http://qa.openoffice.org/scdocs/ddIssues_EnterModify.html#priority
Did you test with the field type changed to *Memo* & max lines >= 9 as mentioned in the description, i.e., step III.E & step IV? If you're unable to reproduce corrupt data or crash on your build, pls. test it with the builds mentioned in the description &/or ask someone else to test on their platforms. Thank you!
The older versions not count. When OOo version submitted to share the development stops, and bugs solved only in case of security problem. When next release is out the old version is abandoned, not touched any more. Now the OOo 3.3 in final phase it will be issued in near future, and developers working on OOo 3.4. Issues find in the older versions, checked in OOo 3.3RC if it can not be reproduced in it, this issue is invalid. I can not reproduce in OOo 3.3RC5 this issue, I set as invalid.
Invalid - > Closing.
@dbaneedsconfirm: Pls. cc some more people beside r4zoli to look at this issue. @r4zoli: PLEASE! Pls. don't just say this is invalid! Wasn't it you who said, "If I use default settings (line max 10), and press Auto, the Desc recognized wrongly as Date" & "The Format example is '1900-01-01'"? You yourself have already confirmed 2 wrong behaviours this bug manifests & you're now closing the issue as invalid??? What kind of service is this??? If you have difficulty understanding what's going on, pls. forward it to your team lead. We simply cannot leave such a defect, which may crash OOo in some cases, unaddressed. If we do, we're just inviting the public to use MS Office instead. And pls. specify what *exactly* you could not reproduce: corrupt data, crash, or both? For me, I can reproduce everything even in the *latest* build, OOO330m15, on 32-bit English Windows XP Pro SP3 (as well as in the *latest official* build, 320m19, on Ubuntu 10.10 in Oracle VM VirtualBox 3.2.10 r66523 on 32-bit English Windows XP Professional SP3--minus the crash since it's a Linux environment). Specifically, after clicking 'Auto', manually changing the field type of 'Desc' to Memo (yes, *Memo*, not Text), & creating the table, when you open the table, the data in the 'Desc' column are corrupt & OOo comes to a short freeze & then crashes! FYI, I'm attaching the crash error report here (b-crash-import-null_date-report.xml). OOo appears to crash in getMsFromTime in dbtoolsmi.dll. Pls. have someone with the knowledge to look at it. BTW, have you looked into Issue #11308 yet? As noted, fixing that will likely change the behaviour of the current defect such that failure will occur on data row /x/ instead of /x/+1. Again, if you're unable to reproduce whatever phenomena, PLEASE forward to another developer or your team lead for further testing. Right now only 2 people have tested so far & 1 person can reproduce it in multiple builds & OS--actually a colleague helped in testing one of the builds/OS so you may even consider that as 2 people--& 1 can't. We really cannot say the issue is invalid w/o having at least one more person to test the case. So PLEASE ask some more people to look at it.
Created attachment 75083 [details] Crash Report
@yutgor - following your steps I can reproduce the erroneous import. (but not with the OO.o RC5 so someone else will need to do that to be sure) It is actually not the data that is corrupt but there is an error, in the GUI default format property for the table, as you describe. To see that it is not the data that is corrupt. Download the ODB file i115420, attached here. The file has two tables, Table7 and Table8 - these are created from your example .ods file, with the Memo data type. Table7 appears to have correct data and Table8 incorrect. To Fix Table8, you must do ALL of this Open Table8 definition for edit. Select the Desc field Click on the button to the right of the "Format example" In this dialog you must clear the ERROR by 1 - change to any user defined value - i.e. '@@' 2 - change back to the correct defaut for text '@' Save the table definition. Correct data is there. So the error is right at that format variable... Finally - I used on Linux, so no comment on the crash part of this only the problem with the display after import.
Created attachment 75084 [details] Table8 has data directly after import from test ODS file - with bad display variable
@atjensen: Thank you for confirming the defect at least on the Linux side. I downloaded your odb &, as expected, OOo (330m15) crashed on opening the table since my OS is Win XP Pro. After OOo recovered, correct data could be displayed by following your steps (though they are quite non-trivial ;) Which build(s) did you test in on? Is there by any chance you could test it on 330m15 as well or ask someone to do so?
Grabbing
Fixed in cws dba34c. I fixed the recognation of the type. Desc is now a varchar. I couldn't reproduce the crash. Seems to fixed when the recognation was fixed.
The problem is that the 2nd column in the spreadsheet is formatted as Number.
Please verify. Thanks. Repro: - Open last bugdoc and copy it to hsqldb => The column types should now be correct to the number format which was chosen in calc
reopen, after inport the table from the bugdoc "test .ods file" the column type is text and not number as formated in the spreadsheet document
verified in cws dba34c. In the copy wizard you need to click on the auto button on 3. page.
.
Checked in DEV300m104, OK.