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.
