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 <mike...@zzzcomputing.com> 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 sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>. >> 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
