So maybe I want "with_labels" but I am not sure how to set that in the visit_join... or am I looking in the wrong place?
On Fri, May 20, 2016 at 7:53 AM, John Omernik <[email protected]> wrote: > Awesome, I am making some progress, I realized that the visit_join I had > was specific to Microsoft access, so I commented it out to see what > happened... that function was somehow not using the ?. When I commented it > out I got "better" sql: > > Access visit_join: > def visit_join(self, join, asfrom=False, **kwargs): > > return ( self.process(join.left, asfrom=True) + \ > (join.isouter and " LEFT OUTER JOIN " or " INNER JOIN ") + > \ > self.process(join.right, asfrom=True) + " ON " + \ > self.process(join.onclause) ) > SELECT field1 AS field1, COUNT(*) AS cnt > FROM table INNER JOIN (SELECT field1 AS __field1 > FROM table GROUP BY field1 ORDER BY COUNT(*) DESC > LIMIT ?) AS anon_1 ON field1 = __field1 GROUP BY field1 ORDER BY cnt DESC > LIMIT 5000 > > > No visit_join > > SELECT field1 AS field1, COUNT(*) AS cnt > FROM table JOIN (SELECT field1 AS __field1 > FROM table GROUP BY field1 ORDER BY COUNT(*) DESC > LIMIT 50) AS anon_1 ON field1 = __field1 GROUP BY field1 ORDER BY cnt DESC > LIMIT 5000 > > > > > This is better! Now, the only question I have now is how I can create a > visit_join (or perhaps this goes elsewhere) where the "ON field1 = > __field1" can actually be ON field1 = anon_1.__field1 Drill doesn't like > field1 = __field1, says its ambiguous. Is there an easy way to do this > rather than trying to manually rewrite the statement. (For example, anon_1 > is being put in by something, and it's non-obvious to me, but I don't want > hard code anon_1 in a dialect... > > Thanks for your help thus far in pointing me in the right direction. > > John > > > > > > > > On Thu, May 19, 2016 at 7:50 PM, John Omernik <[email protected]> wrote: > >> Hey Mike, thanks for the reply. I feel really bad in that I am struggling >> on this, a little background, I am trying to get Caravel, which uses SQL >> Alchemy to play nice with Apache Drill, this means I am learning about >> dialects through a crash course of feeling really dumb, I've had some >> success (with help) and have some aspects working great. One of the things >> I am struggling with is the "How" to trouble shoot when, for example >> Caravel is issuing a Query that should have a limit, but instead comes >> through like this: >> >> SELECT myfield AS myfield, COUNT(*) AS cnt >> FROM mytable INNER JOIN (SELECT myfield AS __myfield >> FROM mytable GROUP BY myfield ORDER BY COUNT(*) DESC >> LIMIT ?) AS anon_1 ON myfield = __myfield GROUP BY myfield ORDER BY cnt DESC >> LIMIT 5000 >> >> >> So this hos Drill receives the query and it fails. There are two things >> wrong here, first is the limits. Now, why the query is build this way is >> beyond me (I can't tell if its Caravel building it that way or SQL Alchemy, >> but the the self join makes no sense to me... it seems redudant, but I >> digress.) There are two LIMITS, the first is the ? which I am assuming >> should be the parameter from Caravel, and then LIMIT 5000. I am guessing >> that the limit 5000 is put on by CARAVEL as that is the max row count... but >> why this is failing is beyond me, it doesn't work on my drill/pyodbc setup, >> but when it's the sqllite dialect on a sqllite test database, that works >> fine... >> >> >> The second issue is the join. Drill finds the myfield = __myfield ambiguous. >> I just need to find how I could tell SQLAlchemy to qualify the __myfield >> with the anon_1. I can't tell if that is SQL Alchemy thing or a caravel >> thing. I am still digging, and will continue to, but if you or anyone here >> could help me debug and/or point me in the right direction, I figured it >> would be good to articulate my thoughts. >> >> >> >> >> >> On Thu, May 19, 2016 at 6:31 PM, Mike Bayer <[email protected]> >> wrote: >> >>> >>> a SQL query that uses parameters will use ? if the DBAPI uses "qmark" >>> paramstyle which is very common. The actual value that lines up with the ? >>> is part of the "parameters" sequence. The specification for this is at >>> https://www.python.org/dev/peps/pep-0249/#id15 which also links out to >>> paramstyle. >>> >>> >>> >>> On 05/19/2016 05:27 PM, John Omernik wrote: >>> >>>> I am writing my own dialect, and for some reason, when my application >>>> sets a limit via parameter, the query submitted to the back end just has >>>> ? in it. Is this an issue with the dialect, the pyodbc (doubt it's >>>> that), or the ODBC driver itself? >>>> >>>> Thanks, John >>>> >>>> >>>> >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "sqlalchemy" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected] >>>> <mailto:[email protected]>. >>>> To post to this group, send email to [email protected] >>>> <mailto:[email protected]>. >>>> Visit this group at https://groups.google.com/group/sqlalchemy. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "sqlalchemy" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To post to this group, send email to [email protected]. >>> Visit this group at https://groups.google.com/group/sqlalchemy. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
