Details
-
Bug
-
Resolution: Unresolved
-
None
-
None
-
Medium
-
Empty show more show less
Description
problem
The usage of segment grid filters on nullable columns (comment column for example) is producing an SQL error when using that column as sort column and then on opening a segment.
Background
The way how the calculation of the next editable segment is done, does not work if the sort column is nullable and contains null values.
If the reference Segment (the opened one) contains null itself for that column we get an SQL error:
ERROR Zend_Db_Statement_Exception: E9999 - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?) OR ((LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5.`comments` = ?) AND...' at line 2, query was: SELECT if(count(pos.id), count(list.id), null) AS cnt FROM `LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5` AS `list` INNER JOIN (SELECT `LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5`.`comments`, `LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5`.`id` FROM `LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5` WHERE (`LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5`.`targetEdit` LIKE '%<del%' OR `LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5`.`targetEdit` LIKE '%<ins%') AND (`LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5`.editable) AND ((LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5.`comments` > ?) OR ((LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5.`comments` = ?) AND (LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5.id> '269425' ))) ORDER BY `LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5`.`comments` ASC, `LEK_segment_view_b08ce2a1c525e37f3ef353048f382eb5`.`id` asc LIMIT 1) AS `pos` WHERE ((list.`comments` < pos.`comments`) OR ((list.`comments` = pos.`comments`) AND (list.`id` < pos.`id`))) ORDER BY `list`.`comments` ASC, `list`.`id` asc
The main problem is, that we are combining ORDER BY and <> operators for sorting but null values are not usable for <> operators and will always return null here.