Issue 115420 - Base: NULL date import breaks type recognition & crashes OOo 3.3.0
Summary: Base: NULL date import breaks type recognition & crashes OOo 3.3.0
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOO330m13
Hardware: PC All
: P3 Trivial (vote)
Target Milestone: 3.4.0
Assignee: marc.neumann
QA Contact: issues@dba
Depends on: 115308
  Show dependency tree
Reported: 2010-11-05 04:56 UTC by nrs
Modified: 2011-03-30 11:01 UTC (History)
3 users (show)

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

Test .ods file (10.94 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-11-05 04:58 UTC, nrs
no flags Details
Test .xls file (7.00 KB, application/
2010-11-05 04:59 UTC, nrs
no flags Details
Test results across different builds, apps, & OS (13.41 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-11-05 05:01 UTC, nrs
no flags Details
Crash Report (10.13 KB, text/xml)
2010-11-22 06:09 UTC, nrs
no flags Details
Table8 has data directly after import from test ODS file - with bad display variable (3.99 KB, application/vnd.sun.xml.base)
2010-11-22 08:19 UTC, drewjensen.inbox
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description nrs 2010-11-05 04:56:47 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

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
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.
Comment 1 nrs 2010-11-05 04:58:52 UTC
Created attachment 72863 [details]
Test .ods file
Comment 2 nrs 2010-11-05 04:59:53 UTC
Created attachment 72864 [details]
Test .xls file
Comment 3 nrs 2010-11-05 05:01:37 UTC
Created attachment 72865 [details]
Test results across different builds, apps, & OS
Comment 4 r4zoli 2010-11-05 09:07:17 UTC
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:
Comment 5 nrs 2010-11-08 06:49:09 UTC
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!
Comment 6 r4zoli 2010-11-19 12:29:42 UTC
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. 
Comment 7 r4zoli 2010-11-19 12:30:21 UTC
Invalid - > Closing.
Comment 8 nrs 2010-11-22 06:07:02 UTC
@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.
Comment 9 nrs 2010-11-22 06:09:09 UTC
Created attachment 75083 [details]
Crash Report
Comment 10 drewjensen.inbox 2010-11-22 08:17:07 UTC
@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.
Comment 11 drewjensen.inbox 2010-11-22 08:19:32 UTC
Created attachment 75084 [details]
Table8 has data directly after import from test ODS file - with bad display variable
Comment 12 nrs 2010-11-23 01:43:47 UTC
@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?
Comment 13 ocke.janssen 2010-12-13 13:59:15 UTC
Comment 14 ocke.janssen 2010-12-13 14:01:00 UTC
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.
Comment 15 ocke.janssen 2010-12-13 14:02:07 UTC
The problem is that the 2nd column in the spreadsheet is formatted as Number.
Comment 16 ocke.janssen 2011-01-14 08:34:31 UTC
Please verify. Thanks.


- Open last bugdoc and copy it to hsqldb

=> The column types should now be correct to the number format which was chosen
in calc
Comment 17 marc.neumann 2011-02-28 08:02:15 UTC
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
Comment 18 marc.neumann 2011-03-01 08:34:32 UTC
verified in cws dba34c. In the copy wizard you need to click on the auto button on 3. page.
Comment 19 ocke.janssen 2011-03-10 11:29:40 UTC
Comment 20 r4zoli 2011-03-30 11:01:30 UTC
Checked in DEV300m104, OK.