Decimal cast in orderings throws error upon postgres

[alekitto]

Ordering upon a text field on a postgres database throws SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type numeric

This is caused by an explicit cast to DECIMAL upon the ordered field into the QOMWalker::walkOrdering method:

jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Query/QOMWalker.php

Line 877 in be54135

$sql = sprintf('CAST(%s AS DECIMAL) %s, %s',

[dbu]

looking at the code on the line above, we try to look at the numerical_props column. as i understand, we expect it to be numerical, but have to cast because it is the result of an xml xpath operation. what would be the correct thing to do in postgres?

also, is this a changed behaviour in some postgres version? i think we do have tests that order query results and we do test with postgres, so this likely worked at some point...

[alekitto]

Could be an error in the persisted data, but i noticed that in the majority of the rows the props xml is replicated into numerical_props column. Could be a bug in a previous version of the library that produced this situation?

How this can be fixed? Should I re-process and re-save all the nodes in the workspace?

[dbu]

this library is not actively developed. you could have a look at the recent releases to see if anything could have changed this: https://github.com/jackalope/jackalope-doctrine-dbal/releases

or try if it works with 1.3.4 if you recently upgraded to 1.3.5. maybe that join fix had some unexpected side effect...