blocking "44" makes "#4" blocked

[phpxcache]

when i fill "blocking" with "44" in #123, ticket "#4" is blocked by "#123", i'm sure this is wrong.

Index: mastertickets/util.py
===================================================================
--- mastertickets/util.py   (revision 2071)
+++ mastertickets/util.py   (working copy)
@@ -10,10 +10,15 @@
     db = env.get_db_cnx()
     cursor = db.cursor()

-    cursor.execute('SELECT ticket FROM ticket_custom WHERE name=%s AND (value LIKE %s OR value LIKE %s)',
+    cursor.execute('SELECT ticket,value FROM ticket_custom WHERE name=%s AND (value LIKE %s OR value LIKE %s)',
                    ('blocking', '%%%s,%%'%tkt, '%%%s'%tkt))
-    blocking_ids = [row[0] for row in cursor]
+    blocking_ids = []
+    for row in cursor:
+        (ticket, value) = row
+        blocks = value.split(',')
+        if tkt in blocks:
+            blocking_ids.append(ticket)
     return blocking_ids

 def linkify_ids(env, req, ids):
-    return Markup(', '.join([unicode(html.A('#%s'%i, href=req.href.ticket(i), class_='%s ticket'%Ticket(env, i)['status'])) for i in ids]))
\ No newline at end of file
+    return Markup(', '.join([unicode(html.A('#%s'%i, href=req.href.ticket(i), class_='%s ticket'%Ticket(env, i)['status'])) for i in ids]))
Index: setup.py

there is nice sql function FIND_IN_SET() but is mysql specified (not sure)

[Jonathan S. Shapiro]

I confirm this bug. I confirm that the patch appears to resolve it, though it does seem strange to need to do a filtering pass this way. I'm not a SQL user much, but surely there is a way to filter for an exact match on the ticket in the initial SELECT?

Anyway, as a workaround it seems to work.

[anonymous]

I'm seeing this one too.

A regexp in the SQL query to ensure the matching ticket ID was word-boundary delimited would pass, but this would require regexp in SQLITE, which appears not to be a builtin. So the filtering pass is a good workable fix.

When filterin pass is added, the SQL doesn't need the second ( LIKE OR LIKE ). I am using,

def blocked_by(env, tkt):
    if isinstance(tkt, Ticket):
        tkt = tkt.id # Allow passing a Ticket object

    db = env.get_db_cnx()
    cursor = db.cursor()

    cursor.execute('SELECT ticket, value FROM ticket_custom WHERE name=%s AND value LIKE %s',
                   ('blocking', '%%%s'%tkt))

    blocking_ids = []
    for row in cursor:
      (ticket,value) = row
      blocks = value.split(',')
      if tkt in blocks:
        blocking_ids.append(ticket)
    return blocking_ids

[paresh.solanki@…]

I also have this problem:

Tickets 50, 51 and 52 are blocking 49, but when ticket 9 is viewed, it also says it is being blocked by 50, 51 and 52.

However, this only seems to affect single digit tickets as tickets 19, 29 and 39 are not affected by this.

It's not really a fix, but a quick work around would be to create the first 10 tickets as dummies and close them off before using the system for real?

[anonymous]

That's because '9' is a substring of '49', but '19' is not. (In SQL, %9% matches 49.) You'd also have to create dummy tickets each time your ticket count went up by a factor of ten, and 10% of your tickets would always be dummy tickets. (My maths may be wrong, it's early.)

Either of the patches above should fix the problem for you.

[Stefan]

Isn't the real problem in storing multiple values (x,y,z) in one column (blocking)? That breaks the 1NF in table design.

[Noah Kantrowitz]

Replying to Stefan:

Isn't the real problem in storing multiple values (x,y,z) in one column (blocking)? That breaks the 1NF in table design.

Yes, however I was hoping to avoid maintaining additional invariant data about ticket links. The next iteration of this plugin (which probably won't be until after workflow is merged to trunk) will use it's own table for data storage.

[anonymous]

Replying to anonymous:

Sorry I'm not an SQL expert so I can't help further, but neither of the above patches work for me. They make the original problem go away, but break the "Blocked By:" functionality (at least in certain cases).

[anonymous]

you tell the case, and i fix the issue

[Noah Kantrowitz]

This is now fixed in trunk.