On Sunday 17 November 2002 04:37 pm, Jeff Zucker wrote: > James Blomo wrote: > > support the IN clause. > > Thanks James, what would help most is some sample queries that didn't > work before and do work after. > SELECT * FROM device t1, device_managed, device_interface t2 WHERE t1.id = device_managed.id AND t1.id = t2.id AND t1.type IN (602, 651) AND (t2.admin_security = 10)
In 1.005 the IN clause assumed that it would be the last conditional, meaning it would grab "(602, 651) AND (t2.admin_security = 10)" as the IN clause. My patch only looks at the items inside the parens immediatly following IN, and saves any extra conditions. In addition, when parsing the column name to be used, it found "t1.id = t2.id AND t1.type" as the clause, causing the IN to be translated into (t1.id = t2.id AND t1.type = 602 OR t1.id = t2.id AND t1.type = 651). I fixed it so that it would only grab colB or tableA.colB immediatly preceding IN. In summary, the above statement would be translated into "WHERE t1.id = device_managed.id AND (t1.id = t2.id AND t1.type = 602 OR t1.id = t2.id AND t1.type = 651)", and it now translates (correctly, I believe) into "WHERE t1.id = device_managed.id AND t1.id = t2.id AND (type = 602 OR t1.type = 651) AND (t2.admin_security = 10)" > Unfortunately, the testing bit is the most labor intensive part and the > part I need most help on. I can come up with lots of examples that > work, but finding the ones that don't isn't always simple. Also making > a fix is not as hard as making sure the fix doesn't break other things > (e.g., did you test with NOT IN? or where the IN is between other > parenthetical clauses?). I understand. Well, I have a code base the is working with DBD::Pg, and I'm trying to translate it into DBD::AnyData so it can be used with cached data. Hopefully there will be enough complex queries to catch most bugs :) I tested NOT IN and IN placed in various positions relative to other conditionals. I'll try IN statments inside various levels of parens right now, and email any problems I find. > > Next I'm going to try to get all 3 way joins working. > Not sure exactly what you mean here. For the example above, I get the error SQL ERROR: Bad predicate: ''! SQL ERROR: No equijoin condition in WHERE or ON clause non_parens_search seems to be getting an empty string from whatever calls it instead of the string with "t1.id = device_managed.id AND t1.id = t2.id" in it. > It seems to me that the most > important > thing to tackle is self joins. Also, are you looking at fixes for For > SQL::Parser only, or for SQL::Statement as well? Right now, I am basicly trying to get the example SELECT statment working, since it is the first step in getting the rest of my program to work. If I have to look in Statment for a solution, I will certainly try to fix that as well. My hope is that the problems I'm encountering are just parsing errors, like IN had, and not fundamental problems. > What version is this a patch against? Oops, sorry. This is against the 1.005 found on CPAN. -- James Blomo UC Berkeley
