Inconsistent table comparison

[nimatrueway]

Hi again, Sualeh !

I encountered a little bug in TableType.compareTo() that causes table sorting to throw exception (for instance sorting happens in TableColumnRetriever.retrieveTableColumnsFromMetadata(), when in a for-loop you try to get allTables values). To reproduce this bug, I wrote this piece of code for you in which two MutableTable objects are compared to each other and comparison in either direction returns smallerThan:

val tbl = new MutableTable(new SchemaReference(null, "public"), "booking_detail")
  tbl.setTableType(new TableType("table"))
val view = new MutableView(new SchemaReference(null, "public"), "blog_monthly_stat_fa")
  view.setTableType(new TableType("materialized view"))
println(tbl.compareTo(view)) // --> returns -1
println(view.compareTo(tbl)) // --> returns -7

This bug could be traced back to this workaround you used in here to put tables in top of the list after sorting which only works in one direction:

if (compareTo != 0 && "TABLE".equalsIgnoreCase(thisToString)) {
      return -1;
}

Describe the solution you'd like We could workaround this bug by this simple patch:

-if (compareTo != 0 && "TABLE".equalsIgnoreCase(thisToString))
-{
-    return -1;
-}
+boolean isThisTable = "TABLE".equalsIgnoreCase(thisToString);
+boolean isOtherTable = "TABLE".equalsIgnoreCase(other.toString());
+if (isThisTable && !isOtherTable)
+    return -1;
+else if (!isThisTable && isOtherTable)
+    return 1;

Describe alternatives you've considered We could also use a map to give different table types different priorities, and in unknown cases do a simple string-comparison.

[sualeh]

Thanks. I will take a look.

[sualeh]

Please use SchemaCrawler 15.02.01 or better.