Bug 45253 - EDITING postgreSQL-SDBC New Table wizard mishandles Memo field data types
Summary: EDITING postgreSQL-SDBC New Table wizard mishandles Memo field data types
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.5.0 RC2
Hardware: All All
: low normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Wizard
  Show dependency tree / graph
 
Reported: 2012-01-25 22:28 UTC by Drew Jensen
Modified: 2022-10-10 07:21 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
screen shot of data type field selector for memo field in table wizard (174.44 KB, image/png)
2012-01-25 22:28 UTC, Drew Jensen 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Drew Jensen 2012-01-25 22:28:03 UTC 
Created attachment 56165 [details]
screen shot of data type field selector for memo field in table wizard

Tested w/ Ubuntu 11.04, Libo 3.5 RC2, Postgresql 8.4 & 9.1

Open an ODB connected to a postgres server
Start the new table wizard.
Select a table template with a memo field.
Select all fields 
Next
Check the data type for the Memo field.

If this is the first table in the database it will set the type as an Array of abstime (not really what we wanted :)
If it is not the first table then it selects an invalid string for the data type.

In the first case if the user does not know to check the table is created with the incorrect column data type. In the second the db engine throws an error when the create table command is issued.

The screen shot is from the second case.
Comment 1 Lionel Elie Mamane 2012-02-02 02:50:24 UTC 
The whole type handling system of PostgreSQL-SDBC is a mess and needs serious love.

Preliminary analysis: most "unknown" types are mapped to "memo". From the described symptoms, the wizard seems to take the first alphabetical in the list of ones mapped to memo and this happens to be "[ _abstime ]" (an array type).

My first guess is that the PostgreSQL type "text" is the best match. However, it is mapped to what the LibreOffice UI calls "Text", which I understand is supposed to be SQL VARCHAR and similar. (There is a separate "Text (fix)" for SQL CHAR.)

Note: when testing, make sure to close the wizard between tests; when going back and forth between step 1 and 2, the wizard keeps changes you made in step 2, even if you remove and readd a field in step 1. This may make that bug seem like a Heisenbug.

Setting low priority as I'm going to focus on data manipulation issues before data definition issues. (data definition can conceivably be done with SQL statements rather than GUI and/or separate PostgreSQL-specific GUI).

If this causes an actual data manipulation issue, this will raise the priority.
Comment 2 Lionel Elie Mamane 2012-02-02 02:56:26 UTC 
Reproduction instruction: The Business / MailingList table has a "Notes" field which is of type "Memo".
Comment 3 Drew Jensen 2012-02-02 08:26:45 UTC 
"Note: when testing, make sure to close the wizard between tests; when going
back and forth between step 1 and 2, the wizard keeps changes you made in step
2, even if you remove and readd a field in step 1."

OK - I think you are referring to removing something in step 1 from the available fields list - not the selected field list.

If Yes then that is by design. You can pull field defs from different table templates or table groups.. So you could pick something from table x go to step 2, back up to step 1, change to table Y def and pull a field def from there, adding it to the list of fields for this wizard run. I just checked and if you do remove something from the selected list in step 1 it is removed even if you set some property in step 2, which is correct. 

Also, I really should have created a second issue though as I mixed two different issues in this one IMO - the screen shot does not show the second problem which is not just matching field types incorrectly, rather setting a field type that does not exist at all and requires different steps to reproduce. 

I will do that later today with full set of steps to recreate the problem.
Comment 4 Lionel Elie Mamane 2012-02-02 09:30:40 UTC 
(In reply to comment #3)
> "Note: when testing, make sure to close the wizard between tests; when going
> back and forth between step 1 and 2, the wizard keeps changes you made in step
> 2, even if you remove and readd a field in step 1."

> OK - I think you are referring to removing something in step 1 from the
> available fields list - not the selected field list.

No, from the selected field list.

Step 1: category business, table Assets. Add all fields
next to step 2
select field Description; change type to date/time
back to step 1
next to step 2
field description is still date/time: good, as you say.
back to step 1
remove field description
add field description
next to step 2: field description is still date/time. This I did not expect.

Note:

back to step 1
remove field description
next to step 2
back to step 1
add field description
next to step 2: now field description is reset to text

But you can even do:

change field description to date/time
back to step 1
remove field description
take sample table Deliveries
add field ArrivalDate
take sample table Assets
add field Description
next to step 2
next to step 2: field description is still date/time


I assumed it would be reset to defaults (since I removed it and added it back from template, it seemed to be a *new* field from template to me, not the same field), but it was not. So I nearly wrote "cannot reproduce" because I had changed the field to "Text [ text ]" once, and now it was stuck there (and not on "Memo [ _abstime ]"), even when I removed it and added it back.
Comment 5 Drew Jensen 2012-02-02 10:40:28 UTC 
OK - I'm with you now.

Yea, I'd agree that if you remove an item from the selected list on the step 1 tab and then add it back it should revert to the default setting from table template currently selected. So it appears that this update to what I guess is a shared data structure used to fill the controls in all tabs is deferred until the user is about to leave a tab, and this should happen instead when the list control contents are changed - does that make sense.
Comment 6 Alex Thurgood 2015-01-03 17:38:18 UTC Comment hidden (no-value)
Comment 7 QA Administrators 2016-01-17 20:02:25 UTC Comment hidden (obsolete)
Comment 8 QA Administrators 2017-03-06 13:54:09 UTC Comment hidden (obsolete)
Comment 9 QA Administrators 2020-10-06 03:44:34 UTC Comment hidden (obsolete)
Comment 10 QA Administrators 2022-10-07 03:38:13 UTC Comment hidden (obsolete)
Comment 11 Alex Thurgood 2022-10-07 13:04:26 UTC 
@Drew is unlikely to be responding to this report, so I'll have a look.
Please don't close it yet.
Comment 12 Alex Thurgood 2022-10-10 07:21:22 UTC 
I can not reproduce the first part of this report with

Version: 7.4.1.2 / LibreOffice Community
Build ID: 3c58a8f3a960df8bc8fd77b461821e42c061c5f0
CPU threads: 8; OS: Mac OS X 12.6; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded

I already have tables in my postgresql test db.

I choose to create a new table based on Assets template.
I selected all of the proposed fields for inclusion.
If I then select the "Notes" field in the field type definition dialog, I see that the field has been assigned the MEMO "text" data type.


Seems now to be WORKSFORME.