MySQL optimizer hints not working with Query API

[Jacob Jensen]

I'm trying to use the query API to create a MySQL query with the the MAX_EXECUTION_TIME(30000) hint.

My code is roughly:

from flask_sqlalchemy import SQLAlchemy

class MyTable(SQLAlchemy().Model):
  ...

base_query = Sqlalchemy.session.query(MyTable) # This works when I execute it.
base_query_with_hint = base_query.with_hint(MyTable, "MAX_EXECUTION_TIME(30000)")
base_query_with_hint.execute() # THIS DOES NOT WORK.

str(base_query_with_hint) 
# Output
# Select a, b, c from MyTable MAX_EXECUTION_TIME(30000)

The hint is just rendered at the end of the query, apparently!

It should render as

Select /*+ MAX_EXECUTION_TIME(30000) */ a, b, c from MyTable

I want it to behave exactly like https://docs.sqlalchemy.org/en/13/core/selectable.html#sqlalchemy.sql.expression.Select.with_hint but I'm using the Query API instead of the Select API.

Another possibility: A commenter in stackoverflow suggested this is an issue with the MySQL dialect not the query API.

See comments here (they suggested I come to this mailing list): https://stackoverflow.com/questions/56030825/sqlalchemy-query-api-not-working-correctly-with-hints

[Mike Bayer]

Hi there-

I see you are using str() to get the string representation, which will
not give you a MySQL representation. DId you read
https://docs.sqlalchemy.org/en/13/faq/sqlexpressions.html#how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined
?

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/789cd456-ce14-4624-a363-446845d94a2d%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

Additionally, those hints seem to be a new feature
(http://mysqlserverteam.com/whats-new-in-mysql-5-7-first-release-candidate/)
for a hint format that was previously not present in MySQL and don't
seem to be present in MariaDB either, and surprisingly enough they
look just like Oracle optimizer hints. Unfortunately the MySQL
dialect in SQLAlchemy does not support this format of MySQL optimizer
hint at this time, and this would need to be new feature add. It
also might be fairly complicated to pull off since these hints don't
apply to all MySQL versions, there might need to be new API features
added, not sure.

I've added https://github.com/sqlalchemy/sqlalchemy/issues/4667 to
track this feature.

On Wed, May 8, 2019 at 9:31 AM Jacob Jensen <2tim...@gmail.com> wrote:
>

[Mike Bayer]

Update, please use prefix_with(), sorry I forgot this goes into the right place:


query.prefix_with("/*+ MAX_EXECUTION_TIME(30000) */")


will add a note to the docs

[Jacob Jensen]

My attempted solution in the meantime: Render the query, edit, execute. I have run into several different errors.

     

        query = ... # my complex query, includes params that do not render with literal_bind.
        # Directly edit query text and rebuild to add MAX_EXECUTION_TIME hint
        query_statement_compiled = query.statement.compile()
        query_binds = [bindparam(key=name, value=bp.value, type_=bp.type) for bp, name in query_statement_compiled.bind_names.iteritems()]
        query_str = str(query_statement_compiled).replace('"', '`')
        sel_ind = query_str.index('SELECT')
        # Insert the hint directly after '...SELECT'
        hint_str = ' /*+ MAX_EXECUTION_TIME(1000) */'
        # hint_str = ''
        query_str_with_hint = query_str[:sel_ind + 6] + hint_str + query_str[sel_ind+6:]
        query_stmt_with_hint = text(query_str_with_hint, bindparams=query_binds)
        query = db.session.query(ProvenancePrototype).from_statement(query_stmt_with_hint)


        query.all() # Fails.

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Jacob Jensen]

I just saw prefix_with. It works correctly! Thanks. Still curious if there's a "direct text edit" approach that works with the query API.

[Mike Bayer]

you can alter SQL text at execution time using the
before_cursor_execute() event, see the example at
https://github.com/sqlalchemy/sqlalchemy/wiki/SessionModifiedSQL . I
just updated some out of date things a bit so if there's still
problems let me know.

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/37d7c086-7b0d-4f94-8137-49916827b2df%40googlegroups.com.