Bug Hunting Session
Bug 92538 - EDITING: PostgreSQL direct connection - Default for Text isn't CharacterType of PostgreSQL
Summary: EDITING: PostgreSQL direct connection - Default for Text isn't CharacterType ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.0.4 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:5.2.0 target:5.1.2
Keywords:
Depends on:
Blocks:
 
Reported: 2015-07-04 07:08 UTC by Robert Großkopf
Modified: 2016-10-25 19:07 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2015-07-04 07:08:57 UTC
This bug could only be reproduced with PostgreSQL and direct connection to it from Base.
Open the database with this connection.
Create a new table.
Create some fields.
There was suggested "Text [character_data]" for the fields.
Try to save the table.

Doesn't work because PostgreSQL doesn't know which type it should be. Seems there is no datatype like this:
http://www.postgresql.org/docs/9.5/static/datatype-character.html

You will get the same if you will copy a table from another database and want to insert. "Text [varchar]" is changed from Base to "Text [character_data]", which doesn't work.

Don't know the first time the bug appears. Have tried this first with LO 4.2 and now again with LO 4.4. It's the same. So I set this to first version of LO 4.2.
My system: OpenSUSE 13.2 64bit rpm Linux.
Comment 1 Alex Thurgood 2015-07-06 13:48:27 UTC
Tried grepping for character_data in the code, but the only place I can find it is 

shell/source/all/xml_parser.cxx
static void xml_character_data_handler(void* UserData, const XML_Char* s, int len)

shell/source/all/xml_parser.cxx
xml_character_data_handler)

so no idea where the "character_data" element for postgresql comes from.
Comment 2 Alex Thurgood 2015-07-12 11:58:16 UTC
Confirming on

LO dev 5.1 alpha

OSX 10.10.4
Comment 3 Alex Thurgood 2015-07-12 12:05:57 UTC
Rather unhelpfully, as compared to other db connections, it is impossible to get a full printout of the error message. On other db connections, this message is available via the More button, but seemingly not here.
Comment 4 Stéphane Aulery 2016-03-02 13:02:36 UTC
Confirmed with version 4.3.3 on Debian

-----------------------------------------------------------------------------

Hallo,

Setup:
- base
- postgresql backend
- sdbc connection (jdbc will most likely have the same result)

Problem:
- base takes a datatype character_data which doens't exist in postgresql, it should be varchar for example

Simulation:
1. Take a table in calc (spreatsheet) with some columns of different structure (eg int, float, string, date)
2. copy the table from calc
3. paste it in base, table section, in a writable schema (a connection to a postgresql instance was already setup)
4. a nice wizard will popup
5. at one point in the wizard the datatypes have to be setup, u can auto, or just leave it as it is,
6. this is where the problem occurs, the default datatype for text is character_data which does not exist in postgresql or (the ISO standard?)
7. a good default would be character varying(n) (ISO standard) or varchar(n) (an alias)

At this point the only workaround is to manually change the datatypes that don't match or exist.

This could apply to other databases as well.

hth,
Wim
Comment 5 Commit Notification 2016-03-03 18:51:02 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ac2505632c96b2653aea2d65178053d1ad9430ef

tdf#92538 use proper schema name for type names

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 6 Lionel Elie Mamane 2016-03-03 18:56:29 UTC
The character_data type exists, but "only" in the information_schema schema.

It is indeed a lousy default for "Text". "varchar" would be much better. I'm afraid LibreOffice just takes the first alphabetically :-(

The part "it does not work" is solved by the proper use of schema in type name.

The part "should not be used as default" is not fixed.
Comment 7 Stéphane Aulery 2016-03-03 19:49:50 UTC
Even half-corrected I take :))
Comment 8 Lionel Elie Mamane 2016-03-03 20:23:47 UTC
Actually, not that I look at it more closely, it looks like the alphabetical sort is done by the driver, and LibreOffice just takes the first (of the right general type category: integer, text, ...) in the order returned by the driver.

So by manipulating the sort order in the driver, it looks like we can choose the defaults. It looks like the driver already forces "int4" to be the default for Integer by forcing it to be the first Integer in the list.

I'm testing that and coming back. See you tomorrow :)
Comment 9 Commit Notification 2016-03-04 08:25:53 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=280f234e5e9fb2879ef99d6888bd0b5b30d16b45

fixup tdf#92538

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2016-03-04 08:25:57 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a70586d9980d71bd3310fcda69f16ac1dafae2d4

fixup tdf#92538

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 11 Commit Notification 2016-03-04 11:49:57 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=95b10e373d117f3ed3e53987aa40a65d1ca6d1a0&h=libreoffice-5-1

tdf#92538 use proper schema name for type names

It will be available in 5.1.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 12 Commit Notification 2016-03-04 12:19:34 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0b0c99edbbd29a89ddc5af60f9abba141e42a4a4

tdf#92538 pgsql-sdbc make a reasonable sorting of types

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2016-03-07 12:46:22 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=43bca025961c4d78082e18efb95f9212b27f964b&h=libreoffice-5-1

tdf#92538 pgsql-sdbc make a reasonable sorting of types

It will be available in 5.1.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 14 Lionel Elie Mamane 2016-03-07 13:47:52 UTC
Should be fully fixed now.