Bug 58692 - Microsoft Access truncates memo field to 255 characters in "Distinct values" queries
Summary: Microsoft Access truncates memo field to 255 characters in "Distinct values" ...
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.0.0.0.beta2
Hardware: Other Windows (All)
: medium normal
Assignee: Lionel Elie Mamane
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2012-12-23 20:14 UTC by Ken
Modified: 2013-11-16 23:05 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
One mdb file and one odb file connected using Access driver (232.53 KB, application/zip)
2013-01-30 14:41 UTC, Ken
Details
I tried to test the zip and there seemed to be an issue this is one of the files (1.20 MB, application/x-msaccess)
2013-01-30 14:55 UTC, Ken
Details
reproduction test case (232.53 KB, application/zip)
2013-01-30 15:03 UTC, Ken
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ken 2012-12-23 20:14:28 UTC
Problem description: 
Not all characters print in report
Steps to reproduce:
1. Create a report connected to an MS Access memo field.
2. Run report
3. Report runs but characters are limited. 

Current behavior:
Report is missing text
Expected behavior:
Expect all text to print in report.
              
Operating System: Windows 7
Version: 4.0.0.0.beta2
Comment 1 Joel Madero 2013-01-10 17:31:25 UTC
We need a test kit for this. Please attached a zipped package that contains everything we need to reproduce the problem and we'll take a look at it. I'm marking as NEEDINFO, once you attach the test kit please mark as REOPENED and we'll test it out. Thanks for your help and understanding
Comment 2 Alex Thurgood 2013-01-10 17:37:50 UTC
Ken,

We also need the following information :

- the version of Access that you are using ;
- how you are connecting to the Access file : directly from LO (using the built-in driver), or via ODBC ?


Alex
Comment 3 Robert Großkopf 2013-01-11 15:36:31 UTC
Note: Could be a problem of the fields in the report-builder. When you create the report, the field is limited by the height and width of the text-field. See https://bugs.freedesktop.org/show_bug.cgi?id=45789
When I read "memo field" it could contain more content than could be shown at one page.
Comment 4 Joel Madero 2013-01-11 21:01:58 UTC
@Ken - recent change in QA policy, when you attach said documentation please reopen as UNCONFIRMED
Comment 5 Ken 2013-01-30 14:41:50 UTC
Created attachment 73931 [details]
One mdb file and one odb file connected using Access driver

All please accept my apology for being a poor participant with this bug I have been traveling and overlooked your request. I have now attached a stripped data base with test data. To confirm this issue run the report called "01 Query_01 Call Report" enter a broad date range (01/01/13 - 01/29/13) then compare "comments 2" to the actual data located in "01 call report" you will note that the test text has been cut at a defined character count of (X). Hope this helps.
Comment 6 Ken 2013-01-30 14:46:52 UTC
(In reply to comment #2)
Alex, 
I have tested with both connection types. 
Access version is 2010 file format is Access 2000 (been using this for many years)

> Ken,
> 
> We also need the following information :
> 
> - the version of Access that you are using ;
> - how you are connecting to the Access file : directly from LO (using the
> built-in driver), or via ODBC ?
> 
> 
> Alex
Comment 7 Ken 2013-01-30 14:47:53 UTC
(In reply to comment #1)
Joel,
I have uploaded the files that should help.


> We need a test kit for this. Please attached a zipped package that contains
> everything we need to reproduce the problem and we'll take a look at it. I'm
> marking as NEEDINFO, once you attach the test kit please mark as REOPENED
> and we'll test it out. Thanks for your help and understanding
Comment 8 Ken 2013-01-30 14:55:00 UTC
Created attachment 73932 [details]
I tried to test the zip and there seemed to be an issue this is one of the files
Comment 9 Ken 2013-01-30 15:03:59 UTC
Created attachment 73933 [details]
reproduction test case

Okay I am a bit new to this, and am not sure why my files were turned to text. I have added a link to a hosted zip file that should be downloadable. Thank you to all for your patience with me.
Comment 10 Ken 2013-01-30 15:12:43 UTC
(In reply to comment #4)
Joel,
I was not able to choose "UNCONFIRMED" it was not in the list.

> @Ken - recent change in QA policy, when you attach said documentation please
> reopen as UNCONFIRMED
Comment 11 Joel Madero 2013-01-30 16:54:05 UTC
Thanks for all of that information, I'll try to test it out today. 

As for UNCONFIRMED, we're trying to change FDO so that UNCONFIRMED can be chosen after setting to NEEDINFO. We know about this limitation.

Thanks again!

Also got your emails so I have all the info. When you upload to FDO, if it's a normal file like zip, ods, doc, png, select "auto-detect" and it'll correctly detect the type but even as a text/plain not a big deal, we can still download it.
Comment 12 Ken 2013-01-30 17:15:44 UTC
Joel,
Thank you a ton. Have a great day! Should anymore questions arise I will
help.

Ken Coe
On Jan 30, 2013 11:54 AM, <bugzilla-daemon@freedesktop.org> wrote:

>   *Comment # 11 <https://bugs.freedesktop.org/show_bug.cgi?id=58692#c11>on bug
> 58692 <https://bugs.freedesktop.org/show_bug.cgi?id=58692> from Joel
> Madero <jmadero.dev@gmail.com> *
>
> Thanks for all of that information, I'll try to test it out today.
>
> As for UNCONFIRMED, we're trying to change FDO so that UNCONFIRMED can be
> chosen after setting to NEEDINFO. We know about this limitation.
>
> Thanks again!
>
> Also got your emails so I have all the info. When you upload to FDO, if it's a
> normal file like zip, ods, doc, png, select "auto-detect" and it'll correctly
> detect the type but even as a text/plain not a big deal, we can still download
> it.
>
>  ------------------------------
> You are receiving this mail because:
>
>    - You reported the bug.
>
>
Comment 13 Joel Madero 2013-02-21 19:31:48 UTC
I don't know a whole ton about Database but I see the issue so:

Microsoft XP
LibreOffice 4.0.0.2 rc

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Marking as:
New (confirmed)
Normal (can impact professional quality work)
Low (unfortunately because this only affects a very limited number of users, marking as Low)

CC'ing Lionel as Database is his baby

Lionel - interested?
Comment 14 Lionel Elie Mamane 2013-02-22 09:40:26 UTC
Reproduced. Not a dupe of bug 45789.

The weird thing is that if the report is based directly on the "01 Call Report" table instead of on the query, the memo field is shown in full.

Also, in the buggy case the memo field stops after 255 characters, so this very much looks like a "length in 8 bits" limit. Doesn't access limit VARCHAR fields to 255 characters?
Comment 15 Lionel Elie Mamane 2013-02-22 09:43:01 UTC
Also, if one removes the 'Distinct Values' from the query, the memo field is *also* printed in full.
Comment 16 Lionel Elie Mamane 2013-02-22 10:00:51 UTC
This is an MS Access / Jet bug.

If one opens the .mdb directly in MS Access (tested with version 2003), and one does exactly the same query INCLUDING THE "Distinct Values" property set to yes, then one gets the same result (truncation to 255 characters).

In the .mdb that Ken attached, there is a "01 Call Report Query". If one changes it to set the "Distinct Values" property to yes, then also in this query within Microsoft Access shows this truncation to 255 characters. If one sets the property to no, then no truncation in MS Access and no truncation in LibreOffice either.

This Microsoft Access bug seems to be known across the interwebs. See e.g.:

 http://allenbrowne.com/ser-63.html, issue "Uniqueness".
 http://bytes.com/topic/access/answers/887735-memo-field-gets-truncated-query-results
 http://stackoverflow.com/questions/1122280/access-query-memo-field-truncation-due-to-distinct
Comment 17 Joel Madero 2013-02-22 18:31:58 UTC
Thanks Lionel for doing the testing. I'll try to re-activate my MSO key so that I can do testing on Access also.