Can't sort a list based on a java.util.Date column?

Have a duplicate rows issue in my database I am trying to correct by keeping the latest record and then making a unique constraint to stop this from happening in the future.

It’s complicated so I am doing it with jython.

I am able to get the groups of my duplicate records via query. I then want to sort them latest to oldest so I can skip the first row, and then delete all other instances.

I am working on this in my console now some sample data looks like this -

listOfRecords
[(10049, 1112, 0, None, 496), (10049, 1112, 0, Wed Sep 25 12:14:44 EDT 2019, 2292)]
>>> listOfRecords[1][3]
Wed Sep 25 12:14:44 EDT 2019
>>> type(listOfRecords[1][3])
<type 'java.util.Date'>

When I try to run listOfRecords.sort(key=labmda x: x[3], reverse=True) to sort on the timestamp column in descending order, I get a java.lang.NullPointerException: java.lang.NullPointerException

Using Ignition 8.1.16.

Full traceback

java.lang.NullPointerException

	at java.base/java.util.Date.getMillisOf(Unknown Source)

	at java.base/java.util.Date.compareTo(Unknown Source)

	at java.base/java.util.Date.compareTo(Unknown Source)

	at org.python.core.PyJavaType$ComparableMethod.__call__(PyJavaType.java:1385)

	at org.python.core.PyObjectDerived.__gt__(PyObjectDerived.java:560)

	at org.python.core.PyObject._lt(PyObject.java:1591)

	at org.python.core.PyList$KVComparator.compare(PyList.java:959)

	at org.python.core.PyList$KVComparator.compare(PyList.java:943)

	at java.base/java.util.TimSort.countRunAndMakeAscending(Unknown Source)

	at java.base/java.util.TimSort.sort(Unknown Source)

	at java.base/java.util.Arrays.sort(Unknown Source)

	at java.base/java.util.ArrayList.sort(Unknown Source)

	at java.base/java.util.Collections.sort(Unknown Source)

	at org.python.core.PyList.sort(PyList.java:998)

	at org.python.core.PyList.sort(PyList.java:790)

	at org.python.core.PyList.list_sort(PyList.java:778)

	at org.python.core.PyList$list_sort_exposer.__call__(Unknown Source)

	at org.python.core.PyObject.__call__(PyObject.java:400)

	at org.python.pycode._pyx165.f$0(<input>:1)

	at org.python.pycode._pyx165.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1687)

	at org.python.core.Py.exec(Py.java:1731)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:611)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:599)

	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)

	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

	at java.base/java.lang.Thread.run(Unknown Source)

java.lang.NullPointerException: java.lang.NullPointerException


	at org.python.core.Py.JavaError(Py.java:547)

	at org.python.core.PyTableCode.call(PyTableCode.java:182)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1687)

	at org.python.core.Py.exec(Py.java:1731)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:611)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:599)

	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)

	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

	at java.base/java.lang.Thread.run(Unknown Source)

Caused by: java.lang.NullPointerException

	at java.base/java.util.Date.getMillisOf(Unknown Source)

	at java.base/java.util.Date.compareTo(Unknown Source)

	at java.base/java.util.Date.compareTo(Unknown Source)

	at org.python.core.PyJavaType$ComparableMethod.__call__(PyJavaType.java:1385)

	at org.python.core.PyObjectDerived.__gt__(PyObjectDerived.java:560)

	at org.python.core.PyObject._lt(PyObject.java:1591)

	at org.python.core.PyList$KVComparator.compare(PyList.java:959)

	at org.python.core.PyList$KVComparator.compare(PyList.java:943)

	at java.base/java.util.TimSort.countRunAndMakeAscending(Unknown Source)

	at java.base/java.util.TimSort.sort(Unknown Source)

	at java.base/java.util.Arrays.sort(Unknown Source)

	at java.base/java.util.ArrayList.sort(Unknown Source)

	at java.base/java.util.Collections.sort(Unknown Source)

	at org.python.core.PyList.sort(PyList.java:998)

	at org.python.core.PyList.sort(PyList.java:790)

	at org.python.core.PyList.list_sort(PyList.java:778)

	at org.python.core.PyList$list_sort_exposer.__call__(Unknown Source)

	at org.python.core.PyObject.__call__(PyObject.java:400)

	at org.python.pycode._pyx165.f$0(<input>:1)

	at org.python.pycode._pyx165.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	... 13 more

Traceback (most recent call last):
  File "<input>", line 1, in <module>
java.lang.NullPointerException

	at java.base/java.util.Date.getMillisOf(Unknown Source)

	at java.base/java.util.Date.compareTo(Unknown Source)

	at java.base/java.util.Date.compareTo(Unknown Source)

	at org.python.core.PyJavaType$ComparableMethod.__call__(PyJavaType.java:1385)

	at org.python.core.PyObjectDerived.__gt__(PyObjectDerived.java:560)

	at org.python.core.PyObject._lt(PyObject.java:1591)

	at org.python.core.PyList$KVComparator.compare(PyList.java:959)

	at org.python.core.PyList$KVComparator.compare(PyList.java:943)

	at java.base/java.util.TimSort.countRunAndMakeAscending(Unknown Source)

	at java.base/java.util.TimSort.sort(Unknown Source)

	at java.base/java.util.Arrays.sort(Unknown Source)

	at java.base/java.util.ArrayList.sort(Unknown Source)

	at java.base/java.util.Collections.sort(Unknown Source)

	at org.python.core.PyList.sort(PyList.java:998)

	at org.python.core.PyList.sort(PyList.java:790)

	at org.python.core.PyList.list_sort(PyList.java:778)

	at org.python.core.PyList$list_sort_exposer.__call__(Unknown Source)

	at org.python.core.PyObject.__call__(PyObject.java:400)

	at org.python.pycode._pyx165.f$0(<input>:1)

	at org.python.pycode._pyx165.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1687)

	at org.python.core.Py.exec(Py.java:1731)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:611)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:599)

	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)

	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

	at java.base/java.lang.Thread.run(Unknown Source)

java.lang.NullPointerException: java.lang.NullPointerException

Tagging @JordanCClark - I know you’re handy with a dataset.

Any clue whats going wrong? I suspect the fact I have None (NULLs in my db) might be causing an issue. Not sure how to get around this.

Yeah, that’s definitely the case. You could coalesce in your lambda, e.g.
listOfRecords.sort(key=lambda x: x[3] or False, reverse=True) - I think that would work and put your nulls at the end.

1 Like

That did the trick, thanks!

I do find it a bit odd/funny that it complains about comparing a date to a None, but knows how to compare a date to a boolean lol. Suppose there’s some conversion going on in the background or something. Anyways, thanks again!

Yeah, basically Jython is “smart” enough to see that Date implements Comparable<Date>, so it tries to use that logic, but Date’s compareTo implementation is not null safe. Normally sorting a list including nulls with Python is totally fine, it’s just an odd edge case here.

2 Likes