Formatted time via SQL command FROM_UNIXTIME fails to replace %*

[admin@…]

Formatting timestamps (e.g. form the ticket table) via the MySQL command FORM_UNIXTIME sometimes (but then constantly for that special report) fails to replace every %* (like %d or %Y) appearance.

Expected Result (for the 'last change' column): Displayed Date/Time? field like 13.05.2009 18:06 by dk

Actual Result: The timestamp is shown as %d.%m.%Y %H:%i by dk

Example: the report with this query (which replaces fine under the mysql CLI) fails to show the date/time:

SELECT DISTINCT p.value AS __colour__,
   (CASE
     WHEN (owner LIKE '%$USER%') THEN 5
     WHEN (cc LIKE '%$USER%') THEN 4
     ELSE 0
     END) AS __color__,
   CONCAT('CMSurf v', version) AS __group__,
   id AS ticket, summary, keywords, t.type AS type, 
   (CASE status WHEN 'assigned' THEN concat(owner, ' *') ELSE owner END) AS owner,
   t.time AS created,
   changetime AS _changetime,
   reporter AS _reporter, 
   (CASE t.changetime
     WHEN t.time THEN CONCAT(FROM_UNIXTIME(t.changetime, '%d.%m.%Y %H:%i'), ' by ', t.reporter)
     ELSE CONCAT(FROM_UNIXTIME(t.changetime, '%d.%m.%Y %H:%i'), ' by ', c.author)
     END) AS 'last change'
  FROM ticket t, enum p, ticket_change c
  WHERE status IN ('new', 'assigned', 'reopened') AND p.name = t.priority 
    AND p.type = 'priority' AND (t.id = c.ticket OR t.changetime = t.time) 
    AND (c.time IN (SELECT MAX(time) FROM ticket_change GROUP BY ticket))
    AND (milestone LIKE 'CMSurf%' OR summary LIKE 'CMSurf%')
  ORDER BY t.version, __color__ DESC, keywords DESC, summary

while this one runs just as expected:

SELECT DISTINCT p.value AS __color__,
   milestone AS __group__,
   id AS ticket, summary, keywords, t.type AS type, 
   (CASE status WHEN 'assigned' THEN concat(owner, ' *') ELSE owner END) AS owner,
   t.time AS created,
   changetime AS _changetime,
   reporter AS _reporter, 
   (CASE t.changetime
     WHEN t.time THEN CONCAT(FROM_UNIXTIME(t.changetime, '%d.%m.%Y %H:%i'), ' by ', t.reporter)
     ELSE CONCAT(FROM_UNIXTIME(t.changetime, '%d.%m.%Y %H:%i'), ' by ', c.author)
     END) AS 'last change'
  FROM ticket t, enum p, ticket_change c
  WHERE status IN ('new', 'assigned', 'reopened') AND p.name = t.priority 
    AND p.type = 'priority' AND (t.id = c.ticket OR t.changetime = t.time) 
    AND (c.time IN (SELECT MAX(time) FROM ticket_change GROUP BY ticket))
    AND milestone != 'CMSurf 1.x'
  ORDER BY __group__, __color__, keywords DESC, summary

This is strange since the query parts are 100% identical and the replacement is supposed to be done by MySQL (i.e. before the table is rendered, though after the variable replacement, maybe that could be the first thing to check)

[cboos]

Can't reproduce, both reports are working fine for me.

[cboos]

(I was testing with 0.11.6rc1, btw)