Bug 127210 - DDE function breaks if Item/range parameter is from another cell
Summary: DDE function breaks if Item/range parameter is from another cell
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.3.0.4 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2019-08-29 05:30 UTC by Bernard
Modified: 2021-04-09 16:36 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Test cases in zip file (16.71 KB, application/octet-stream)
2019-08-29 05:32 UTC, Bernard
Details
Test cases in zip file (18.25 KB, application/octet-stream)
2019-08-29 10:18 UTC, Bernard
Details
Test cases in zip file (18.37 KB, application/octet-stream)
2019-10-19 07:18 UTC, Bernard
Details
screenshot (66.47 KB, image/png)
2019-10-19 15:29 UTC, Oliver Brinzing
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Bernard 2019-08-29 05:30:35 UTC
Description:
Please use the attached test case spreadsheet file BadComplexTOS.xls

Calc is losing DDE function cell entries. The bad formulas were initially fully functional.
This happens after the spreadsheet is closed after saving it, and then re-opened.
All cells previously containing good DDE links now contain the following formula:

=NA()

The original content was like:

=DDE("TOS","Last",A2)

where A2 is a cell in the same row that contains the Item/range parameter value, in this case a stock symbol.

If I restore the formula, then it may work again, but in the practical case with about 50 DDE links I get an error in the status bar: "Error: Value not available"

An obvious anomaly with this failing test case is the fact that on opening the spreadsheet file, the following prompt at the top of the sheet is MISSING:

"Automatic update of external links has been disabled. [Enable Content] [X]"

I expect this to be presented when the spreadsheet file contains DDL links. This is of course logical after the links have been replaced with =NA() entries so the links are gone, but I have also seen this when the links were not yet replaced with =NA() entries. In that case, it could be that the failure to detect a DDE function if the parameter refers to another cell entry is part of the problem.

DDE using the same formula with the Item/range parameter value hard-coded works from another spreadsheet.

If I create a new sheet in the erroneous document, then the formula also breaks. This indicates that the file is broken not only the sheet.

I save the file with option "Use Excel 97-2003 Format" but I don't think that this matters.

I verified that Calc has no problem with the same DDE function using a hard-coded parameter value. Please see the attached test case file GoodSimpleTOS.xls.

I also verified that Calc has no problem generally to take the Item/range parameter value from another cell. Please see the attached test case GoodComplexFile.ods to check this.

It might be complex to reproduce this problem because I can also produce positive results with GoodComplexFile.ods. However, the bad case is reproducible and persistent at my end, and I cannot find a work-around solution. It would be great to have this resolved because DDE is such a powerful and useful function.


Steps to Reproduce:
Please refer to description.

Actual Results:
Please refer to description.

Expected Results:
Please refer to description.


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 Bernard 2019-08-29 05:32:16 UTC
Created attachment 153727 [details]
Test cases in zip file
Comment 2 Bernard 2019-08-29 05:32:59 UTC
Build is actually:

Version: 6.2.5.2 (x64)
Build ID: 1ec314fa52f458adc18c4f025c545a4e8b22c159
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; 
Locale: en-NZ (en_NZ); UI-Language: en-US
Calc: CL
Comment 3 Oliver Brinzing 2019-08-29 09:58:23 UTC
(In reply to Bernard from comment #0)
> Description:
> Please use the attached test case spreadsheet file BadComplexTOS.xls

Thank you for reporting the bug. 

Attached "testcase.zip" does not contain a file "BadComplexTOS.xls".
And please add a detailed description how the bug can be reproduced with this file.

I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.
Comment 4 Bernard 2019-08-29 10:18:49 UTC
Created attachment 153731 [details]
Test cases in zip file

My apologies. New zip file should replace old.

To reproduce this at your end might be difficult, and I might need to supply additional information. First, I am using the Ameritrade Thinkorswim platform as a DDE server "TOS" which is perhaps not available to you. Perhaps you have a different DDE client that might function in a similar manner. Perhaps you want to play around with these files to get an idea. I am truly sorry about this. But on the other hand, it is a trivial case in a way and in another way it is not. I am thinking that you might have the desire to break the DDE function in such a way that this reproduces at your end. That would be ideal.

I have been using DDE in Calc with the hard-coded Item/range parameter for years in hundreds of spreadsheets with the same TOS server for years and I did not find a single problem. In fact I switched to Calc from Excel because Excel locked up occasionally! Now I cannot get this slightly more complex version to work at all.

I think it makes so much more sense to fetch the Item/range parameter from a separate cell where it is visible.

Many thanks in advance.
Comment 5 Bernard 2019-08-30 12:09:56 UTC
It might be of interest that I tried a workaround by generating the DDE function with hard-coded parameters in order to avoid the complexity, using apache POI.

That failed as well:

Failure to generate DDE formula in HSSFWorkbook
https://bz.apache.org/bugzilla/show_bug.cgi?id=63709
Comment 6 Bernard 2019-10-01 19:06:07 UTC
Now I have a different situation, much easier:

1) I open an old spreadsheet with many DDE formulas that do actually work.
2) I click on the dialog "Automatic update of external links has been disabled. [Enable Content] [X]"
2) I copy from one cell in that sheet a working DDE formula to the clipboard.
3) I create a new sheet
4) I paste the formula into that sheet.

The DDE formula breaks in the new sheet. This does not make sense to me. It is as simple as it gets.

I suspect that the problem may come from the fact that I did not somehow enable "Automatic update of external links" in the new sheet. I don't get that dialog. 

If I save the sheet and re-open it, then I get the dialog and the formula works.

So getting this dialog seems to be essential.
Comment 7 Oliver Brinzing 2019-10-02 19:07:09 UTC
(In reply to Bernard from comment #6)

> The DDE formula breaks in the new sheet. This does not make sense to me. It
> is as simple as it gets.

i tried with a DDE formula like this:
=DDE("soffice";"d:\dde_src.ods";"sheet1.a1")

and it works immediately if i copy the formula cell to a new sheet.

Can you please add a test case for the new scenario?
Comment 8 Bernard 2019-10-02 21:50:11 UTC
Thanks for looking into this.

The different situation just illustrates the point that there IS a problem with DDE and LibreOffice that I should not be able to produce at my computer no matter how hard I try. This situation does not have a separate test case at this point, so please don't wait for one. Your case where it works with the soffice DDE server would not mean much as in my attached test cases, even the complex case works with the soffice DDE server at my end, where the other problems persist.

What the original issue would require is the attention of a DDE / Libre Office expert. To the extent that we get the chance to experiment with test DDE clients and servers with logging capabilities. At this stage, no serious questions have been asked with respect to the resolution of the problem.

I would imagine that DDE is perhaps one of the more complex parts of the system. It is quite possible that the TOS DDE server I use may play an important role in this, and we would need the ability to potentially pinpoint a problem to the extent that we can engage the vendor of that DDE server, in case that this server causes the problem. But to do that, we would first need to analyze.

So to get this issue into a confirmed state needs some expert analysis. I am more than happy to help there if required because I have computer programming skills (Java), but not Windows native and DDE.
Comment 9 Bernard 2019-10-15 04:37:29 UTC
I think that the status can be changed to NEW because TD Ameritrade support engineers could reproduce / confirm on multiple computers the case where a DDE link breaks in a new spreadsheet while this works in MS Excel spreadsheets.
Comment 10 Bernard 2019-10-19 05:50:13 UTC
The bug can be reproduced easily with a mature and well-documented DDE server sample provided at https://sourceforge.net/projects/jdde/ with instructions how to run it at 

http://jdde.pretty-tools.com/en/examples/ddeserverexample.htm

Prerequisite is a Java JRE (Java Runtime Environment) installed and available on the path in a command window.

To run the DDE server, from command line, we execute:
java -cp pretty-tools-JDDE-2.1.0.jar;pretty-tools-JDDE-2.1.0-examples.zip DDEServerExample

This runs the DDE server. This can be tested with the provided DDE clients:
http://jdde.pretty-tools.com/en/examples/requestexample.htm
java -cp pretty-tools-JDDE-2.1.0.jar;pretty-tools-JDDE-2.1.0-examples.zip RequestExample

and

http://jdde.pretty-tools.com/en/examples/adviceexample.htm
java -cp pretty-tools-JDDE-2.1.0.jar;pretty-tools-JDDE-2.1.0-examples.zip AdviceExample

Then in a new spreadsheet, we enter into two cells:

=DDE("MyServer", "MyTopic", "MyRequest")
=DDE("MyServer", "MyTopic", "MyAdvise")

1) Both cases do NOT work with a new unsaved sheet, but they DO work with a sheet where these formulas have been saved before.

2) If then you enhance the DDE formulas to get the "Mode" parameter values "MyRequest" and "MyAdvise" from other cells A1 and A2, then this reproduces the issue in such a way that after saving and retrieving the document, the new formula cells contain the text "=NA()", meaning that the formula is lost. While any simple formulas in the same sheet still work.

Cell entries, cell borders indicated by "|", line number shown first:

 |A|B|
1|MyRequest|=DDE("MyServer", "MyTopic", A1)|
2|MyAdvise|=DDE("MyServer", "MyTopic", A2)|
Comment 11 Bernard 2019-10-19 07:18:49 UTC
Created attachment 155144 [details]
Test cases in zip file

New test cases matching DDE MyServer.

There is an interesting additional bug with the formula getting the "Mode" parameter from another cell: If we copy the formula into the cell directly, then it works (before saving). However if we use the cell editing functionality [Ctrl+V] after [F2], then the entered formula breaks immediately. Just another bug that might show the way to a solution.
Comment 12 Oliver Brinzing 2019-10-19 15:29:26 UTC
Created attachment 155153 [details]
screenshot

after i managed to run the server and demo java client, i started a test with a spreadsheet using LO 6.1:

- new spreadsheet
- cell B1: =DDE("MyServer";"MyTopic"; "MyRequest")  -> #NV
- cell B2; =DDE("MyServer";"MyTopic"; "MyAdvise")   -> #NV
- save spreadsheet
- reload spreadsheet and enable link update

-> B1 shows a result, e.g. "MyRequest data 53"
-> B2 shows "MyAdvise data 54" and counts the values *permanently* high

- save & close spreadsheet
- load spreadsheet and enable link update again

-> B1 sometimes shows a result, e.g. "MyRequest data 53"
   but sometimes result is empty.
-> B2 shows "MyAdvise data 54" and counts the values *permanently* high
 
noticed: 
the problem ssems not to happen if i use only formula in cell B1