Issue 56535

Summary: queryPrecedents (XFormulaQuery) on Calc-cells don't recognize cross-table-references
Product: App Dev Reporter: leguff <mathias>
Component: apiAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues, nick
Version: 3.3.0 or older (OOo)   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
File demonstrating the described behaviour none

Description leguff 2005-10-24 12:11:30 UTC
try the following:
- create a new spreadsheet document and name the first table "table1"
  and the second "table"
- enter a value into table1.D24
- enter formula "=table1.D24" into table2.C6
- now get the precedents cells of table2.C6 via API and you'll find some
  unexpected values:
Sub PrintFormulaPrecedents

	spreadSheetDocument = ThisComponent
	sheet1              = spreadSheetDocument.getSheets().getByIndex(0)
	sheet2              = spreadSheetDocument.getSheets().getByIndex(1)
	
	' "table2.C2"
	formulaCell         = sheet2.getCellByPosition(2, 5)
	
	' get the precedents of this cell (Aspect: XFormulaQuery)
	ranges              = formulaCell.queryPrecedents(true)
	
	' prints: table2.C6; table2.D24 (should be table1.D24 only)
	print ranges.getRangeAddressesAsString()
	
	e = ranges.getCells().createEnumeration()
	while e.hasMoreElements()
		cell = e.nextElement()
		' prints: table2.5.2 (should be table1.23.3)
		print   "Precedents " & cell.SpreadSheet.Name _
		      & "." & cell.RangeAddress.StartRow _
		      & "." & cell.RangeAddress.StartColumn
	wend
	
End Sub
Comment 1 leguff 2005-10-24 12:13:28 UTC
Sorry, there's a bug in my description:
the beginning is meant to be:
try the following:
- create a new spreadsheet document and name the first table "table1"
  and the second "table2"
                  ^^^^^^
Comment 2 leguff 2005-10-24 12:20:47 UTC
Created attachment 30772 [details]
File demonstrating the described behaviour
Comment 3 stephan.wunderlich 2005-10-31 17:09:31 UTC
*** Issue 56534 has been marked as a duplicate of this issue. ***
Comment 4 stephan.wunderlich 2005-11-01 15:11:02 UTC
sw->nn: looks like one for you :-) ... I'm not sure why the cells itself if part
of the precedents but it definitely shouldn't be "table2.D24" in the given
sample, but "table1.D24"
Comment 5 niklas.nebel 2006-07-07 19:25:55 UTC
changing target
Comment 6 villeroy 2006-12-18 12:53:26 UTC
Same with queryDependents. I was about to file an issue about queryDependents
and found this one.
The following basic-code demonstrates both.
REM run this basic code against any spreadsheet, having 2 sheets at least.
REM it creates in A2:A6 simple dependents of A1, prints the dependents' addresses
REM Then it moves away A2 to A1 of 2nd sheet and prints the dependetnts'
addresses again.
REM Then it prints the precedents of moved cell.
REM the moved dependent sheet(1)A2 gets lost, while the detective indicates a
dependent on another sheet.
REM the moved dependent has no precedent other than itself 
Sub Main
Dim oCellAddr as new com.sun.star.table.CellAddress
Dim oRangeAddr as new com.sun.star.table.CellRangeAddress
	oSh = ThisComponent.getSheets.getByIndex(0)
	oSh.getCellByPosition(0,1).setFormula("=$A$1")
	oSh.showDependents(oCellAddr)
	sN = oSh.getName
	oSh.getCellRangeByPosition(0,1,0,5).fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM,1)
	cell_A1 = thiscomponent.sheets(0).getCellByPosition(0,0)
	printDependents cell_A1, TRUE," of "& sN &".A1"
	REM Move A2 to Sheet2.A1
	oCellAddr.Sheet = 1
	oRangeAddr.StartRow = 1
	oRangeAddr.EndRow = 1
	oSh.moveRange(oCellAddr,oRangeAddr)
	printDependents cell_A1, TRUE," of "& sN &".A1 after moving A2"
	sN = ThisComponent.getSheets.getByIndex(oCellAddr.Sheet).getName
	cell_Moved =
thisComponent.getSheets.getByIndex(oCellAddr.Sheet).getCellByPosition(oCellAddr.Column,oCellAddr.Row)
	printPrecedents cell_Moved, TRUE," of "& sN &".A1"
End Sub
Sub printDependents(oCell,bRecursive As Boolean,sCaption$)
	thiscomponent.calculateAll
	sRangeNames = oCell.queryDependents(bRecursive).getRangeAddressesAsString
	aNames() = Split(sRangeNames,";")
	MsgBox Join(aNames(),Chr(10)),0,"DEPENDENTS"& sCaption
End Sub
Sub printPrecedents(oCell,bRecursive As Boolean,sCaption$)
	thiscomponent.calculateAll
	sRangeNames = oCell.queryPrecedents(bRecursive).getRangeAddressesAsString
	aNames() = Split(sRangeNames,";")
	MsgBox Join(aNames(),Chr(10)),0,"PRECEDENTS"& sCaption
End Sub
Comment 7 Marcus 2017-05-20 11:27:55 UTC
Reset assigne to the default "issues@openoffice.apache.org".
Comment 8 Nick Hollon 2017-07-05 20:01:28 UTC
I can confirm that this bug still exists.

And I think I have spotted where the cause lies. 

Internally, XFormulaQuery is correctly finding the cell references (with the proper table id). But it is using a class called ScMarkData to keep track of the references, and this class is only storing the row and column data, not the table id.

In main/sc/inc/markdata.hxx, there is a TODO comment in German:

//!    todo:
//!    Es muss auch die Moeglichkeit geben, MarkArrays pro Tabelle zu halten,
//!    damit "alle suchen" ueber mehrere Tabellen wieder funktioniert!

Translation:
"There must also be the possibility to keep MarkArrays per table,
So "search all" over several tables again works!"

So somebody was already aware that this needed to be fixed.

Unfortunately, I don't feel qualified to actually submit a patch, but I hope my small bit of detective work will be helpful. Thanks!