[Upstream] Calc database functions gives Err:504 when external sheet is used

[Lucio]

Binary package hint: openoffice.org

Openoffice 3.1.1, Karmic AMD64

When using database functions (eg DB.MEDIA -translated- DB. MEAN) i gest an error if I refer to data contained in external sheet.

Steps to reproduce:

1) create "prova1" sheet with source data (attached)
2) create "prova2" sheet with DB.MEDIA function (attached)

DB.MEDIA function "=DB.MEDIA('file:///home/lucio/Comodo/prova1.ods'#$Foglio1.D1:E5,'file:///home/lucio/Comodo/prova1.ods'#$Foglio1.D1,'file:///home/lucio/Comodo/prova1.ods'#$Foglio1.G2:G3)" returns Err:504

when running in "prova1" DB.MEDIA funtion works
when running a non-DB function (eg. SUM) no problems appear

ProblemType: Bug
Architecture: amd64
Date: Mon Jan 25 18:15:16 2010
DistroRelease: Ubuntu 9.10
Package: openoffice.org-core 1:3.1.1-5ubuntu1 [modified: var/lib/openoffice/basis3.1/program/services.rdb]
ProcEnviron:
 LANGUAGE=it_IT.UTF-8
 PATH=(custom, no user)
 LANG=it_IT.UTF-8
 SHELL=/bin/bash
ProcVersionSignature: Ubuntu 2.6.31-18.55-generic
SourcePackage: openoffice.org
Uname: Linux 2.6.31-18-generic x86_64

[Lucio (lumatemp-nospam)]

Forgot to say that the very same DB functions with external sheets were working in previous OOO version (cannot remember which one, probably the deafult one coming with Jaunty) and I have a bunch of old calc sheets linked each other that I need to use!

[Chris Cheney (ccheney)]

[This is an automatic notification.]

This bug was reported against an earlier version of Ubuntu, can you test if it still occurs on Ubuntu 10.04 LTS (Lucid Lynx)?

Please note we also provide technical support for older versions of Ubuntu, but not in the bug tracker. Instead, to raise the issue through normal support channels, please see:

    http://www.ubuntu.com/support

If you are the original reporter and can still reproduce the issue on Lucid, please run the following command to refresh the report:

  apport-collect 512413

Bear in mind that you may need to install the python-launchpadlib package from the universe repository. Additionally, when prompted to give apport-collect permissions for Launchpad you will need to give it at least the ability to "Change Non-Private" data as it will be adding information to your bug report.

If you are not the original reporter, please file a new bug report, so we can work with you as the original reporter instead (you can reference bug 512413 in your report if you think it may be related):

  ubuntu-bug openoffice.org

If by chance you can no longer reproduce the issue on Lucid or if you feel it is no longer relevant, please mark the bug report 'Fix Released' or 'Invalid' as appropriate, at the following URL:

  https://bugs.launchpad.net/ubuntu/+bug/512413

[Lucio (lumatemp-nospam)]

Tested the same sheets in Openoffice 3.2 under Ubuntu 10.04 up to date.

I get "#VALORE!" error instead of "Err:504" (should be "#VALUE!" i believe), so the function still doesn't work.

[The-inf (the-inf)]

I've received file, made in MS Office 2003 (original.xls in attachment).
When I'm opening it in MS Office 2007 or OpenOffice.org 3.0.0 it works fine (msoffice2007.jpg)
In LibreOffice (3.3 release and 3.3.1 rc1):
When I'm opening it without updating links, in sheet "взвеш дистрибуция" I see Err:504 (original_504.jpg)
When I'm opening it with updating links I see Err:502 (original_502.jpg)
I've tried to re-save this file in .xlsx (saved_as_xlsx.xlsx) and open it in LibreOffice and get an Err:509 (saved_as_xlsx_509.jpg)

Sorry for Russian in spreadsheet and screenshots.

[The-inf (the-inf)]

attachment is 5.51mb, so uploaded it to rapidshare
http://rapidshare.com/files/447934714/archive.zip

[Iamtester8 (iamtester8)]

Reproduced with LO 3.3 RC4 Ubuntu 10.04 x86 except not error 509 but freezing on saved_as_xlsx.

[Paulo de Souza Lima (paulo-s-lima)]

That does not happens only with MsOffice files. That happens with any formula which tries to link an external spreadsheet. That happens also in Ubuntu Linux (32bit/64bit), and it was reported before (one year ago) at https://bugs.launchpad.net/ubuntu/+source/openoffice.org/+bug/512413.

The issue has not been treated yet, at least in LibreOffice 3.3.1.

[Paulo de Souza Lima (paulo-s-lima)]

(In reply to comment #3)
> That does not happens only with MsOffice files. That happens with any formula
> which tries to link an external spreadsheet. That happens also in Ubuntu Linux
> (32bit/64bit), and it was reported before (one year ago) at
> https://bugs.launchpad.net/ubuntu/+source/openoffice.org/+bug/512413.
>
> The issue has not been treated yet, at least in LibreOffice 3.3.1.

Steps to reproduce the error:
1 - create an empty spreadsheet called teste.ods and save it.
2 - create another spreadsheet called test1.ods and save it in the same folder as teste.ods.
3 - Place the following formula to any cell in test1.ods: =IF('file:///[path_to_the_folder]/teste.ods'#$Planilha1.A9=C9;'file:///[path_to_the_folder]/teste.ods'#$Planilha1.B9;15)
4 - Save test1.ods.

Even if you mark all cells in both spreadsheets as numeric format, the error still happens.

[Björn Michaelsen (bjoern-michaelsen)]

[This is an automated message.]
There are no new official OpenOffice.org releases in Ubuntu packaging anymore => Won't Fix

If the problem persists, please mark this bug as "also affects project Libreoffice" or "also affects distribution Libreoffice (Ubuntu)" if that has not happened already.

Please leave references to upstream OpenOffice.org bugs in place to allow cross pollination.

[Björn Michaelsen (bjoern-michaelsen)]

[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html

[Reisi007 (reisi007)]

Dear bug submitter!

Due to the fact, that there are a lot of NEEDINFO bugs with no answer within the last six months, we close all of these bugs.

To keep this message short, more infos are available @ https://wiki.documentfoundation.org/QA/NeedinfoClosure#Statement

Thanks for understanding and hopefully updating your bug, so that everything is prepared for developers to fix your problem.

Yours!

Florian

[penalvch (penalvch)]

lucio, thank you for reporting this bug and helping make Ubuntu better. Please answer these questions:

* Is this still reproducible?
* If so, what specific steps should we take to recreate this bug?
* Do you have any extensions installed? If so, which?

As well, according to this report, you are not using the most recent version of LibreOffice in a supported Ubuntu release. Please upgrade to the most recent version and let us know if you are still having this issue.

Thank you for your understanding.

Helpful Bug Reporting Link:
https://help.ubuntu.com/community/ReportingBugs#Bug_Reporting_Etiquette

[Lucio (lumatemp-nospam)]

I can confirm that the bug is still present in LibreOffice 3.5.4.2 under Ubuntu 10.04 AMD64. The function results in the "Err:504" error.
The steps to reproduce are the same described in my first mail in this thread.
Extensions installed:
- American English spell checking
- Dizionario ortografico italiano
- Fornitore di script per Python
- PDF importer

[Björn Michaelsen (bjoern-michaelsen)]

confirmed by downstream bug -> reopen

[penalvch (penalvch)]

Lucio, regarding your comments https://bugs.launchpad.net/ubuntu/+source/openoffice.org/+bug/512413/comments/13 :

>"The steps to reproduce are the same described in my first mail in this thread."

Unfortunately, the steps provided are not click for click, as advised in https://wiki.ubuntu.com/LibreOfficeBugWrangling , and remain as ambiguous as when you first wrote them.

You are welcome to provided click for click instructions, so this may be marked Triaged as soon as possible.

Thank you for your understanding.

[Lucio (lumatemp-nospam)]

I actually cannot figure out where is the ambiguity. The database to work on has to be provided in an attached worksheet as I did with "Prova1.ods". The following step is to create the DB.MEDIA function is a separate file as I did with "Prova2.ods". The DB.MEDIA (DAVERAGE) function is:

=DB.MEDIA('PATH_TO/prova1.ods'#$Foglio1.D1:E5;'PATH_TO/prova1.ods'#$Foglio1.D1;'PATH_TO/prova1.ods'#$Foglio1.G2:G3)

considering the syntax of the function DB.MEDIA (DAVERAGE):

DAVERAGE(Database; DatabaseField; SearchCriteria)

Database consists into

a b
1 1
2 1
3 1
100 0

in Prova1 sheet

DatabaseField consists into D1 in Prova1 sheet

SearchCriteria consists into

b
1

in Prova1 sheet

I am not able to be more specific.

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[jisaac (jisaac)]

I'm able to reproduce this bug with SUMIF() and LibreOffice 4.0.2.2 installed by default with Ubuntu 13.04 :(

All my previous sheets were done with the default LibreOffice version of Ubuntu 12.04 and a lot have external sheets linked :(

Is there a workaround?

Regards,

Jacques.

[jisaac (jisaac)]

Sorry, I made a mistake, this is not the right place for this issue!

[Jmadero-dev (jmadero-dev)]

Never confirmed by QA team - moving to UNCONFIRMED.

[Raal (raal)]

Tested with LO 4.3.2.2, win7. Steps from comment 4, closing as worksforme because no Err:XXX appear. Tested with .ods, .xlsx and .xls.

[Raal (raal)]

Created attachment 108836
printscreen