Bug 57475 - The Match function does not perform implicit conversion
Summary: The Match function does not perform implicit conversion
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.11-FINAL
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-01-21 14:37 UTC by cquezel
Modified: 2015-02-08 14:45 UTC (History)
0 users



Attachments
Testcase (3.60 KB, text/plain)
2015-01-29 06:48 UTC, cquezel
Details
proposed patch (23.31 KB, patch)
2015-01-29 07:22 UTC, cquezel
Details | Diff
proposed patch (23.12 KB, patch)
2015-01-29 12:21 UTC, cquezel
Details | Diff
Excel spreadsheet to test MATCH (9.22 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-01-29 13:55 UTC, cquezel
Details

Note You need to log in before you can comment on or make changes to this bug.
Description cquezel 2015-01-21 14:37:48 UTC
If I call MATCH (EQUIV) with a SearchCriterion whose type is different from the type of the elements of the LookupArray, the function returns N/A even if the types are convertable.


if SearchCriterion is of type string whose value is convertable to a number ('1' for example) and LookupArray contains elements whose type is Number ({1,2,3} for example) then Match returns N/A while Excel and LibreOffice find a match.


public final class Match extends Var2or3ArgFunction {
...
private static int findIndexOfValue(ValueEval lookupValue, ValueVector lookupRange,
			boolean matchExact, boolean findLargestLessThanOrEqual) throws EvaluationException {

...			
	// Next line return false when classes dont match
	if(lookupComparer.compareTo(lookupRange.getItem(i)).isEqual()) {

Because ...

		public final CompareResult compareTo(ValueEval other) {
...
			// Next line does not convert
			if (_targetClass != other.getClass()) {
				return CompareResult.TYPE_MISMATCH;
			}
			return compareSameType(other);
		}
Comment 1 Nick Burch 2015-01-21 17:35:40 UTC
First up, are you able to write a very short junit unit test that shows the problem? Say, create a new workbook with a sheet, create a cell with a problematic formula, evaluate and check the result?

Second, with that done, if you can, are you able to do a patch to add the missing conversion?
Comment 2 cquezel 2015-01-29 03:48:22 UTC
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.Assert;
import org.testng.annotations.Test;

/**
 * Test the Match function
 */
public class MatchTest {
	
	/**
	 * Sets the cell value.
	 * @param cell
	 * @param value a Number or a String
	 */
	private static void setCellValue(XSSFCell cell, Object value) {
		if (value instanceof String) {
			cell.setCellValue((String)value);
		} else if (value instanceof Number) {
			cell.setCellValue(((Number)value).doubleValue());
		} else {
			throw new IllegalArgumentException("Unsupported type " + value.getClass().getName());
		}
	}

	/**
	 * Evaluates Assert.assertEquals(MATCH(lookup, A1:C1, 0), expected).
	 * <pre>
	 * test("2", 1, 2, 3, 2) is
	 * Assert.assertEquals(MATCH(2, A1:C1, 0), 2)
	 * where A1 = 1, B1 = 2, C1 = 3
	 * </pre>
	 *     
	 * @param lookup the lookup value. Add double quotes around the value for strings.
	 * @param A1 the content of A1
	 * @param B1 ...
	 * @param C1 ...
	 * @param expected the expected value. 
	 */
	private static void test(String lookup, Object A1, Object B1, Object C1, int expected) {
		
		try {
			   //Create a new Workbook
	        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
	            //Create a blank sheet
	            XSSFSheet sheet = workbook.createSheet("MatchTest");
	            
	            XSSFRow row1 = sheet.createRow(0);
	       		XSSFCell a1 = row1.createCell(0);
	       		setCellValue(a1, A1);
	       		XSSFCell b1 = row1.createCell(1);
	       		setCellValue(b1, B1);
	            XSSFCell c1 = row1.createCell(2);
	       		setCellValue(c1, C1);
	            XSSFCell d1 = row1.createCell(3);
	            
	            d1.setCellType(Cell.CELL_TYPE_FORMULA);
	            d1.setCellFormula("MATCH(" + lookup + ", A1:C1, 0)");
				XSSFFormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
				int typeOfFormulaResult = evaluator.evaluateFormulaCell(d1);
				Assert.assertEquals(typeOfFormulaResult, Cell.CELL_TYPE_NUMERIC);
				
	            double value = d1.getNumericCellValue();
	            Assert.assertEquals(value, expected, 0.0);
	        }
		} catch (IOException ex) {
			throw new RuntimeException(ex.getMessage(), ex);
		}
	}
	
	/**
	 * OK
	 * Assert.assertEquals(MATCH(2, {1, 2, 3}, 0), 2)
	 */
	@Test
	public static void testNumerics() {
		test("2", 1, 2, 3, 2); 
	}

	/**
	 * OK
	 * Assert.assertEquals(MATCH("2", {"1", "2", "3"}, 0), 2)
	 */
	@Test
	public static void testStrings() {
		test("\"2\"", "1", "2", "3", 2); 
	}
	
	/**
	 * Fails
	 * Assert.assertEquals(MATCH("2", {1, 2, 3}, 0), 2)
	 */
	@Test
	public static void testStringNumerics() {
		test("\"2\"", 1, 2, 3, 2); 
	}

	/**
	 * Fails
	 * Assert.assertEquals(MATCH(2, {"1", "2", "3"}, 0), 2)
	 */
	@Test
	public static void testNumericStrings() {
		test("2", "1", "2", "3", 2); 
	}
}
Comment 3 cquezel 2015-01-29 06:48:41 UTC
Created attachment 32408 [details]
Testcase

The original test case I posted is wrong.
This test case includes 1 test that fails and others that pass.
Comment 4 cquezel 2015-01-29 07:22:43 UTC
Created attachment 32409 [details]
proposed patch
Comment 5 cquezel 2015-01-29 12:21:48 UTC
Created attachment 32410 [details]
proposed patch

code cleanup (same logic)
Comment 6 cquezel 2015-01-29 13:55:00 UTC
Created attachment 32411 [details]
Excel spreadsheet to test MATCH

Excel spreadsheet to test MATCH (EQUIV), VLOOKUP (RECHERCHEV), HLOOKUP (RECHERCHEH)
Comment 7 cquezel 2015-01-29 15:58:44 UTC
Now that is embarassing!

I can't reproduce the problem on Excel anymore. It happens on OpenOffice only.

I would close this bug for now.
Comment 8 Dominik Stadler 2015-02-08 14:45:57 UTC
Closing this one for now based on your last comments.