MyTickets report fails on non authenticated anonymous users who have their email set in the preferences

[Carsten Klein <carsten.klein@…>]

*please* make this work.

I will provide a patch if possible.

[Carsten Klein <carsten.klein@…>]

I see now that the MyTickets report will only return those tickets for which one is the registered owner.

Perhaps extending this to also allowing it to search for reporter = $USER would do the trick?

[Remy Blank]

Could you please be more precise? The "My Tickets" report seems to work on this site, even if I log out and set an e-mail address in the preferences. What are you seeing, and what did you expect?

[Carsten Klein <carsten.klein@…>]

on t.e.o. i see, with configured email address and name in the preferences (see attachment).

[Carsten Klein <carsten.klein@…>]

What I would expect are at least those for which I am the reporter and not the responsible person (or assigned person) for.

Ideally it would also return those tickets on which I commented upon, but this is optional.

[Carsten Klein <carsten.klein@…>]

Replying to Carsten Klein <carsten.klein@…>:

[…] and not _only_ the responsible person […]

[Remy Blank]

Ok, understood. I guess the "My tickets" report was originally designed for listing the tickets of which someone is the owner. Adding the tickets reported by the current user would make sense. However, adding those on which the current user has commented would probably add too much noise. We could add another report for that, though.

So yes, if you can come up with the SQL queries for these two, we could fix that.

[Carsten Klein <carsten.klein@…>]

You asked for it:

SELECT p.value AS __color__,
   (CASE status WHEN 'accepted' THEN 'Accepted' ELSE 'Owned' END) AS __group__,
   id AS ticket, summary, component, version, milestone,
   t.type AS type, priority, time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE t.status <> 'closed' AND ( owner = $USER or reporter = $USER )
  ORDER BY (status = 'accepted') DESC, CAST(p.value AS int), milestone, t.type, time

[Carsten Klein <carsten.klein@…>]

And here is the second query which also includes tickets where the user commented upon:

SELECT DISTINCT p.value AS __color__,
   (CASE status WHEN 'accepted' THEN 'Accepted' ELSE 'Owned' END) AS __group__,
   id AS ticket, summary, component, version, milestone,
   t.type AS type, priority, t.time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON tc.ticket = t.id
  WHERE t.status <> 'closed' AND ( owner = $USER or reporter = $USER or author = $USER )
  ORDER BY (status = 'accepted') DESC, CAST(p.value AS int), milestone, t.type, t.time

[Thijs Triemstra]

Queries need a review.

[Remy Blank]

Will do.

[Remy Blank]

Good start. How about adding groups for "reported" and "commented"?

SELECT DISTINCT p.value AS __color__,
       (CASE
         WHEN owner = $USER AND status = 'accepted' THEN 'Accepted'
         WHEN owner = $USER THEN 'Owned'
         WHEN reporter = $USER THEN 'Reported'
         ELSE 'Commented' END) AS __group__,
       t.id AS ticket, summary, component, version, milestone,
       t.type AS type, priority, t.time AS created,
       t.changetime AS _changetime, description AS _description,
       reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON tc.ticket = t.id AND tc.author = $USER
                                AND tc.field = 'comment'
  WHERE t.status <> 'closed'
        AND (owner = $USER OR reporter = $USER OR author = $USER)
  ORDER BY (owner = $USER AND status = 'accepted') DESC,
           owner = $USER DESC, reporter = $USER DESC,
           CAST(p.value AS int), milestone, t.type, t.time

Let me know if this does what you wanted.

[Remy Blank]

Improved "My Tickets" report committed in [10385].

[Christian Boos]

I wanted to test r10385 here, but I got:

ProgrammingError: for SELECT DISTINCT, ORDER BY expressions must appear in select list

The following "fix" worked:

SELECT DISTINCT
        CAST(p.value AS int) AS __color__,
       (CASE
         WHEN owner = $USER AND status = 'accepted' THEN 'Accepted'
         WHEN owner = $USER THEN 'Owned'
         WHEN reporter = $USER THEN 'Reported'
         ELSE 'Commented' END) AS __group__,
       t.id AS ticket, summary, component, version, milestone,
       t.type AS type, priority, t.time AS created,
       t.changetime AS _changetime, description AS _description,
       reporter AS _reporter,
       (owner = $USER AND status = 'accepted') AS __exp1,
       (owner = $USER) as __exp2,
       (reporter = $USER) as __exp3
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON tc.ticket = t.id AND tc.author = $USER
                                AND tc.field = 'comment'
  WHERE t.status <> 'closed'
        AND (owner = $USER OR reporter = $USER OR author = $USER)
  ORDER BY (owner = $USER AND status = 'accepted') DESC,
           owner = $USER DESC, reporter = $USER DESC,
           CAST(p.value AS int),
            milestone, t.type, t.time

But then the groups are not contiguous. What about this instead:

SELECT DISTINCT
        CAST(p.value AS int) AS __color__,
       (CASE
         WHEN owner = $USER AND status = 'accepted' THEN 'Accepted'
         WHEN owner = $USER THEN 'Owned'
         WHEN reporter = $USER THEN 'Reported'
         ELSE 'Commented' END) AS __group__,
       t.id AS ticket, summary, component, version, milestone,
       t.type AS type, priority, t.time AS created,
       t.changetime AS _changetime, description AS _description,
       reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON tc.ticket = t.id AND tc.author = $USER
                                AND tc.field = 'comment'
  WHERE t.status <> 'closed'
        AND (owner = $USER OR reporter = $USER OR author = $USER)
  ORDER BY __group__, __color__, milestone, t.type, t.time

Or even better, to have the group order Accepted/Owned/Reported/Commented:

SELECT  __color__, __group,
       (CASE
         WHEN __group = 1 THEN 'Accepted'
         WHEN __group = 2 THEN 'Owned'
         WHEN __group = 3 THEN 'Reported'
         ELSE 'Commented' END) AS __group__,
       ticket, summary, component, version, milestone,
       type, priority, created, _changetime, _description,
       _reporter
FROM (
 SELECT DISTINCT CAST(p.value AS int) AS __color__,
      (CASE
         WHEN owner = $USER AND status = 'accepted' THEN 1
         WHEN owner = $USER THEN 2
         WHEN reporter = $USER THEN 3
         ELSE 4 END) AS __group,
       t.id AS ticket, summary, component, version, milestone,
       t.type AS type, priority, t.time AS created,
       t.changetime AS _changetime, description AS _description,
       reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON tc.ticket = t.id AND tc.author = $USER
                                AND tc.field = 'comment'
  WHERE t.status <> 'closed'
        AND (owner = $USER OR reporter = $USER OR author = $USER)
) AS sub
ORDER BY __group, __color__, milestone, type, created

VERIFY However, it remains to be seen if one of the above would work with MySQL ;-) (they both do with SQLite).

[Christian Boos]

TODO On a "different" topic, actually back to the original request, it seems that $USER is still replaced by anonymous when not logged in, not with the name/e-mail set in the preferences. It should be possible to fix also that…

[Remy Blank]

Replying to cboos:

I wanted to test r10385 here, but I got:

ProgrammingError: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Which database backend is this? SQLite worked fine here, maybe it's a question of version. But I didn't test on the other backends. Feel free to commit your fixes.

The discussion did drift a bit starting from comment:4, so the initial issue could indeed be fixed as well.

[Christian Boos]

Replying to rblank:

Replying to cboos:

I wanted to test r10385 here, but I got:

ProgrammingError: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Which database backend is this?

Sorry, I should have been more precise: here == t.e.o ;-) See {12}. So this was with PostgreSQL 8.3.

Ok, I'll commit the changes once I've tested them with MySQL.

[Remy Blank]

Reassigning as per comment:16.

[anonymous]

It looks good now, but returns too many results for the non authenticated user with email address set in the preferences as it will also return results where there is simply the anonymous user, i.e. a user with no email having been set…

[Christian Boos]

#10677 was closed as duplicate for the problem discussed in comment:13.

[Christian Boos]

Replying to anonymous:

It looks good now, but returns too many results for the non authenticated user with email address set in the preferences as it will also return results where there is simply the anonymous user, i.e. a user with no email having been set…

See comment:14.

[OT] Experimenting with WikiPhrases to highlight the remaining open points in long tickets (as proposed in ticket:525#comment:113)

[Mark Potter <mpotter@…>]

Coming late to the discussion of this ticket, so I may be off base; apologies in advance if I am.

It appears to me that we are blurring the purpose of the "My Tickets" report. I view the original purpose of "My Tickets" as:

As a developer, what are the tickets that I need to work on?
i.e. Tickets where owner is the current user.
i.e. My tickets.

Most of the discussion appears to be adding:

As a user, what are the ticket that are of interest to me?
i.e Tickets that the current user reported, is watching (in the CC), or commented on). (see also #8559).

I find these two purposes to be distinctly different and thus should be different reports.

[Christian Boos]

Aren't the proposed categories enough to address both use cases?

• as a user, you'd get the "Reported" and "Commented" tickets,
• as a developer, you'll get (in addition) the "Owned" and "Accepted" tickets, and they'll appear first

I'm not sure whether you'd have in "Commented" the tickets where you simply added yourself to the CC:, but if not we could have a fifth category "On CC:".

Of course, the alternative to have more specialized reports for different roles is also a valid approach, but I think the idea of this ticket was just to make the default "My Tickets" report more generally useful.

[Mark Potter <mpotter@…>]

Replying to cboos:

Aren't the proposed categories enough to address both use cases?

Yes, but it starts becoming "a jack of all trades but master of none". Of the top of my head:

• As a developer:
  • I do not want to see Closed; I'm done with those.
  • I would want to see Accepted, then Owned, then CC'ed, then Reported, then Commented. If I cared enough to CC a ticket it's more important than those I only reported or commented on.
• As a user:
  • I would want to see Closed tickets, as yet another category. (What's the status of my tickets of interest? Where did my issue go?)
  • I would want to see Reported, then CC'ed, then Commented, then Deleted, then Accepted, then Owned.
    • A normal user would not have any Accepted or Owned, so them coming first is a not an issue.
    • But what about a dual user looking at the tickets as a user. ("What's the status of my tickets of interest?" vs. as a developer, "What should I be working on?") Obviously the order would be non-ideal, but also a ticket could end up in the wrong grouping (e.g. Developer vs User: Owned vs. Reported, Owned vs. Commented, Owned vs. CC, or CC vs. Reported).

I'm not sure whether you'd have in "Commented" the tickets where you simply added yourself to the CC:, but if not we could have a fifth category "On CC:".

No, there is no comment when you only add yourself to the CC. But not, was added to the CC, but is in the CC; thus directly test the ticket cc field. See #8559.

Of course, the alternative to have more specialized reports for different roles is also a valid approach,

I would think that best.

but I think the idea of this ticket was just to make the default "My Tickets" report more generally useful.

To the original request of making $USER work for non authenticated users, I would also like to see a $USER dynamic variable UI, for manager type functions. (I don't want to see my tickets, I want to see arthur.dent's tickets.) But that should be another ticket.

[Christian Boos]

Replying to Mark Potter <mpotter@…>:

To the original request of making $USER work for non authenticated users, I would also like to see a $USER dynamic variable UI, for manager type functions. (I don't want to see my tickets, I want to see arthur.dent's tickets.) But that should be another ticket.

I'll answer to the rest later, but for this one, you know that you can already specify USER in the URL? We could perhaps also make it part of the UI, but for now it's excluded on purpose (don't remember exactly why though, see #7293 for details).

[Mark Potter <mpotter@…>]

Replying to cboos:

I'll answer to the rest later, but for this one, you know that you can already specify USER in the URL?

Yes, that is what I do, thanks.

[Remy Blank]

Preparing for 1.0.

[Christian Boos]

Moving enhancements to dev.

[Remy Blank]

I assume you meant to set the milestone.

[Ethan Jucovy <ethan.jucovy@…>]

Replying to cboos:

Or even better, to have the group order Accepted/Owned/Reported/Commented:

SELECT  __color__, __group,
       (CASE
         WHEN __group = 1 THEN 'Accepted'
         WHEN __group = 2 THEN 'Owned'
         WHEN __group = 3 THEN 'Reported'
         ELSE 'Commented' END) AS __group__,
       ticket, summary, component, version, milestone,
       type, priority, created, _changetime, _description,
       _reporter
FROM (
 SELECT DISTINCT CAST(p.value AS int) AS __color__,
      (CASE
         WHEN owner = $USER AND status = 'accepted' THEN 1
         WHEN owner = $USER THEN 2
         WHEN reporter = $USER THEN 3
         ELSE 4 END) AS __group,
       t.id AS ticket, summary, component, version, milestone,
       t.type AS type, priority, t.time AS created,
       t.changetime AS _changetime, description AS _description,
       reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON tc.ticket = t.id AND tc.author = $USER
                                AND tc.field = 'comment'
  WHERE t.status <> 'closed'
        AND (owner = $USER OR reporter = $USER OR author = $USER)
) AS sub
ORDER BY __group, __color__, milestone, type, created

VERIFY However, it remains to be seen if one of the above would work with MySQL ;-) (they both do with SQLite).

I just tested the above version of Report 7 with a MySQL backend. It fails:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT) AS __color__,\r\n (CASE\r\n WHEN (owner = 'ethan' AND status = 'ac' at line 12

The problem is DISTINCT CAST(p.value AS int) AS __color__. For MySQL, it needs to be DISTINCT CAST(p.value AS decimal) AS __color__ — casting to "decimal" instead of "int". I just tested on postgres and sqlite, and using DISTINCT CAST(p.value AS decimal) __color__ seems to work properly on all of the database backends.

So, this version of the report works well on postgres 8.4, mysql 5.1.63-0ubuntu0.11.04.1, and sqlite 3.6.22:

SELECT  __color__, __group,
       (CASE
         WHEN __group = 1 THEN 'Accepted'
         WHEN __group = 2 THEN 'Owned'
         WHEN __group = 3 THEN 'Reported'
         ELSE 'Commented' END) AS __group__,
       ticket, summary, component, version, milestone,
       type, priority, created, _changetime, _description,
       _reporter
FROM (
 SELECT DISTINCT CAST(p.value AS decimal) AS __color__,
      (CASE
         WHEN owner = $USER AND status = 'accepted' THEN 1
         WHEN owner = $USER THEN 2
         WHEN reporter = $USER THEN 3
         ELSE 4 END) AS __group,
       t.id AS ticket, summary, component, version, milestone,
       t.type AS type, priority, t.time AS created,
       t.changetime AS _changetime, description AS _description,
       reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON tc.ticket = t.id AND tc.author = $USER
                                AND tc.field = 'comment'
  WHERE t.status <> 'closed'
        AND (owner = $USER OR reporter = $USER OR author = $USER)
) AS sub
ORDER BY __group, __color__, milestone, type, created

[anonymous]

Replying to Ethan Jucovy <ethan.jucovy@…>:

Backing this up with a link to the offical documentation:

​http://dev.mysql.com/doc/refman/5.1/de/cast-functions.html

There is no type INTeger to which you can cast to, except for UNSIGNED (INTEGER).

+1 by me.

[Jun Omae]

#11480 was closed as duplicated.

It would be simple to use EXISTS (SELECT * FROM ticket_change ...) rather than DISTINCT ... LEFT JOIN ticket_change .... I verified with SQLite 3.3.6, PostgreSQL 8.1.23 and MySQL 5.0.95. If using MySQL, replace with CAST(p.value AS signed).

SELECT p.value AS __color__,
       (CASE
         WHEN owner = $USER AND status = 'accepted' THEN 'Accepted'
         WHEN owner = $USER THEN 'Owned'
         WHEN reporter = $USER THEN 'Reported'
         ELSE 'Commented' END) AS __group__,
       t.id AS ticket, summary, component, version, milestone,
       t.type AS type, priority, t.time AS created,
       t.changetime AS _changetime, description AS _description,
       reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE t.status <> 'closed' AND
        (owner = $USER OR reporter = $USER OR
         EXISTS (SELECT * FROM ticket_change tc
                 WHERE tc.ticket = t.id AND tc.author = $USER AND
                       tc.field = 'comment'))
  ORDER BY (COALESCE(owner, '') = $USER AND status = 'accepted') DESC,
           COALESCE(owner, '') = $USER DESC,
           COALESCE(reporter, '') = $USER DESC,
           CAST(p.value AS integer), milestone, t.type, t.time

[Jun Omae]

Proposed fix for ProgrammingError on PostgreSQL with unit tests for reports can be found in jomae.git@t9311. I've verified with SQLite 3.3.6, PostgreSQL 8.1.23 and MySQL 5.0.95.

[Jun Omae]

Committed in [12533] and merged to trunk in [12534].

Also, the DISTINCT and LEFT JOIN ticket_change in report:12 has been changed to EXISTS (SELECT * FROM ticket_change ...).

[Jun Omae]

Oops. The following failure cause of milliseconds resolution time on Windows.

======================================================================
ERROR: test_report_7_my_tickets (__main__.ExecuteReportTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "trac\ticket\tests\report.py", line 452, in test_report_7_my_tickets
    comment='from anonymous')
  File "trac\ticket\tests\report.py", line 145, in _save_ticket
    return ticket.save_changes(author=author, comment=comment, when=when)
  File "C:\usr\src\trac\trac.git\trac\ticket\model.py", line 358, in save_changes
    """, (self.id, when_ts, author, cnum, comment))
  File "C:\usr\src\trac\trac.git\trac\db\util.py", line 121, in execute
    cursor.execute(query, params)
  File "C:\usr\src\trac\trac.git\trac\db\util.py", line 54, in execute
    r = self.cursor.execute(sql_escape_percent(sql), args)
  File "C:\usr\src\trac\trac.git\trac\db\sqlite_backend.py", line 78, in execute
    result = PyFormatCursor.execute(self, *args)
  File "C:\usr\src\trac\trac.git\trac\db\sqlite_backend.py", line 56, in execute
    args or [])
  File "C:\usr\src\trac\trac.git\trac\db\sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
IntegrityError: columns ticket, time, field are not unique

trac/ticket/tests/report.py

@@ -140 +140 @@
 
     def _save_ticket(self, ticket, author=None, comment=None, when=None,
                      **kwargs):
+        if when is None:
+            when = ticket['changetime'] + timedelta(microseconds=1)
         for name, value in kwargs.iteritems():
             ticket[name] = value
         return ticket.save_changes(author=author, comment=comment, when=when)

[Jun Omae]

The patch has been committed in [12535] and merged to trunk in [12536].