Opened 9 years ago

Closed 9 years ago

blocking "44" makes "#4" blocked

Reported by: Owned by: phpxcache coderanger normal MasterTicketsPlugin major chris@…, paresh.solanki@…, jhulten 0.10

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

-    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)

comment:1 Changed 9 years ago by shap

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.

comment:2 follow-up: ↓ 8 Changed 9 years ago by anonymous

• Cc chris@… added; anonymous removed

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


comment:3 Changed 9 years ago by 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?

comment:4 Changed 9 years ago by 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.

comment:5 follow-up: ↓ 6 Changed 9 years ago by Stefan

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

comment:6 in reply to: ↑ 5 Changed 9 years ago by coderanger

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.

comment:7 Changed 9 years ago by jhulten

• Description modified (diff)

comment:8 in reply to: ↑ 2 Changed 9 years ago by anonymous

• Severity changed from normal to major

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).

comment:9 Changed 9 years ago by anonymous

you tell the case, and i fix the issue

comment:10 Changed 9 years ago by coderanger

• Description modified (diff)
• Resolution set to fixed
• Status changed from new to closed

This is now fixed in trunk.