[drill] branch master updated: Drill-7813: Excel Reader Crashes on Formulae Which Return Strings

[cgivre]

This is an automated email from the ASF dual-hosted git repository.

cgivre pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/drill.git


The following commit(s) were added to refs/heads/master by this push:
     new e70a176  Drill-7813: Excel Reader Crashes on Formulae Which Return 
Strings
e70a176 is described below

commit e70a176739bb0e2711333ebdf806c1e12a9c6113
Author: Charles Givre <cgi...@apache.org>
AuthorDate: Tue Dec 1 14:08:30 2020 -0500

    Drill-7813: Excel Reader Crashes on Formulae Which Return Strings
---
 .../drill/exec/store/excel/ExcelBatchReader.java   |  10 ++++-
 .../drill/exec/store/excel/TestExcelFormat.java    |  41 +++++++++++++++++++++
 .../src/test/resources/excel/numeric-formula.xlsx  | Bin 0 -> 9208 bytes
 .../src/test/resources/excel/text-formula.xlsx     | Bin 0 -> 9260 bytes
 4 files changed, 50 insertions(+), 1 deletion(-)

diff --git 
a/contrib/format-excel/src/main/java/org/apache/drill/exec/store/excel/ExcelBatchReader.java
 
b/contrib/format-excel/src/main/java/org/apache/drill/exec/store/excel/ExcelBatchReader.java
index 1df4071..82901f1 100644
--- 
a/contrib/format-excel/src/main/java/org/apache/drill/exec/store/excel/ExcelBatchReader.java
+++ 
b/contrib/format-excel/src/main/java/org/apache/drill/exec/store/excel/ExcelBatchReader.java
@@ -459,7 +459,15 @@ public class ExcelBatchReader implements 
ManagedReader<FileSchemaNegotiator> {
     } else if (cellType == CellType.NUMERIC && 
DateUtil.isCellDateFormatted(cell)) {
       // Case if the column is a date or time
       addColumnToArray(rowWriter, excelFieldNames.get(colPosition), 
MinorType.TIMESTAMP, false);
-    } else if (cellType == CellType.NUMERIC || cellType == CellType.FORMULA || 
cellType == CellType.BLANK || cellType == CellType._NONE) {
+    } else if (cellType == CellType.FORMULA) {
+      // Cells with formulae can return either strings or numbers.
+      CellType formulaCellType = cell.getCachedFormulaResultType();
+      if (formulaCellType == CellType.STRING) {
+        addColumnToArray(rowWriter, excelFieldNames.get(colPosition), 
MinorType.VARCHAR, false);
+      } else {
+        addColumnToArray(rowWriter, excelFieldNames.get(colPosition), 
MinorType.FLOAT8, false);
+      }
+    } else if (cellType == CellType.NUMERIC || cellType == CellType.BLANK || 
cellType == CellType._NONE) {
       // Case if the column is numeric
       addColumnToArray(rowWriter, excelFieldNames.get(colPosition), 
MinorType.FLOAT8, false);
     } else {
diff --git 
a/contrib/format-excel/src/test/java/org/apache/drill/exec/store/excel/TestExcelFormat.java
 
b/contrib/format-excel/src/test/java/org/apache/drill/exec/store/excel/TestExcelFormat.java
index 934f78c..b79108b 100644
--- 
a/contrib/format-excel/src/test/java/org/apache/drill/exec/store/excel/TestExcelFormat.java
+++ 
b/contrib/format-excel/src/test/java/org/apache/drill/exec/store/excel/TestExcelFormat.java
@@ -423,6 +423,47 @@ public class TestExcelFormat extends ClusterTest {
   }
 
   @Test
+  public void testTextFormula() throws Exception {
+    String sql = "SELECT * FROM cp.`excel/text-formula.xlsx`";
+
+    RowSet results = client.queryBuilder().sql(sql).rowSet();
+
+    TupleMetadata expectedSchema = new SchemaBuilder()
+      .addNullable("Grade", MinorType.VARCHAR)
+      .addNullable("Gender", MinorType.VARCHAR)
+      .addNullable("Combined", MinorType.VARCHAR)
+      .buildSchema();
+
+    RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema)
+      .addRow("Seventh Grade", "Girls", "Seventh Grade Girls")
+      .addRow("Sixth Grade", "Girls", "Sixth Grade Girls")
+      .addRow("Fourth Grade", "Girls", "Fourth Grade Girls")
+      .build();
+
+    new RowSetComparison(expected).verifyAndClearAll(results);
+  }
+
+  @Test
+  public void testNumericFormula() throws Exception {
+    String sql = "SELECT * FROM cp.`excel/numeric-formula.xlsx`";
+
+    RowSet results = client.queryBuilder().sql(sql).rowSet();
+    TupleMetadata expectedSchema = new SchemaBuilder()
+      .addNullable("col1", MinorType.FLOAT8)
+      .addNullable("col2", MinorType.FLOAT8)
+      .addNullable("calc", MinorType.FLOAT8)
+      .buildSchema();
+
+    RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema)
+      .addRow(2.0, 8.0, 256.0)
+      .addRow(4.0, 6.0, 4096.0)
+      .addRow(6.0, 4.0, 1296.0)
+      .build();
+
+    new RowSetComparison(expected).verifyAndClearAll(results);
+  }
+
+  @Test
   public void testLimitPushdown() throws Exception {
     String sql = "SELECT id, first_name, order_count FROM 
cp.`excel/test_data.xlsx` LIMIT 5";
 
diff --git a/contrib/format-excel/src/test/resources/excel/numeric-formula.xlsx 
b/contrib/format-excel/src/test/resources/excel/numeric-formula.xlsx
new file mode 100644
index 0000000..6d65a55
Binary files /dev/null and 
b/contrib/format-excel/src/test/resources/excel/numeric-formula.xlsx differ
diff --git a/contrib/format-excel/src/test/resources/excel/text-formula.xlsx 
b/contrib/format-excel/src/test/resources/excel/text-formula.xlsx
new file mode 100644
index 0000000..9cce25e
Binary files /dev/null and 
b/contrib/format-excel/src/test/resources/excel/text-formula.xlsx differ