SQL dinâmico em rotinas armazenadas do MySQL

Só de ouvir a pergunta me faz pensar em dois aspectos:

ASPECTO #1: As funções devem ser DETERMINÍSTICAS

Nesse caso, isso implica que uma função deve apresentar os mesmos dados de retorno consistentemente para um determinado conjunto de parâmetros, NÃO IMPORTA QUANDO VOCÊ CHAMA A FUNÇÃO.

Agora, imagine uma função que produz uma resposta diferente devido à coleta de dados em diferentes horários do dia com base no SQL estático da função. Em certo sentido, isso ainda pode ser considerado DETERMINÍSTICO se você consultar o mesmo conjunto de tabelas e colunas todas as vezes, dado o mesmo conjunto de parâmetros.

E se você pudesse alterar as tabelas subjacentes de uma função via SQL dinâmico? Você está violando a definição de uma função DETERMINÍSTICA.

Observe que o MySQL adicionou esta opção em /etc/my.cnf

log-bin-trust-function-creators

Embora isso possa ser uma simplificação exagerada, isso permite que as funções tenham permissão para gravar dados em logs binários sem aplicar estritamente a propriedade DETERMINISTIC.

ASPECTO #2: Os gatilhos devem poder ser revertidos

• Você poderia imaginar um gatilho com todos os mesmos comportamentos de uma função e, em seguida, introduzir o SQL dinâmico na mistura?
• Você poderia imaginar tentar aplicar MVCC (Multiversion Concurrecy Control) contra SQL dinâmico depois de aplicar MVCC à tabela base para a qual o gatilho foi criado?

Você teria essencialmente dados que crescem quadraticamente (mesmo exponencialmente) apenas no MVCC sozinho. O processo de gerenciar o rollback do SQL com gatilhos que podem ser não DETERMINÍSTICOS seria extremamente complexo, para dizer o mínimo.

À luz desses dois aspectos, tenho certeza de que os desenvolvedores do MySQL pensaram nessas coisas e rapidamente as descartaram impondo restrições.

Então, por que suspender a restrição para Procedimentos? Simplificando, não há preocupação com propriedades DETERMINÍSTICAS ou reversão.

Essa é uma ótima pergunta, mas não sei a resposta. Imagino que isso terá que ir para a equipe interna, mas não sei se eles vão gostar muito deste site. Enquanto isso, posso ajudá-lo a deduzir algumas respostas.

Para começar, vejo isso:

O cache do acionador não detecta quando os metadados dos objetos subjacentes foram alterados. Se um acionador usa uma tabela e a tabela mudou desde que o acionador foi carregado no cache, o acionador opera usando os metadados desatualizados.

O que me faz pensar que isso tem algo a ver com isso. Não vai recompilar o SQL se não estiver monitorando os metadados. O que significa que é uma preocupação do motor.

Da mesma forma, quando leio este bloco, penso a mesma coisa (motor):

Para evitar problemas de interação entre as threads do servidor, quando um cliente emite uma instrução, o servidor utiliza um instantâneo de rotinas e gatilhos disponíveis para execução da instrução. Ou seja, o servidor calcula uma lista de procedimentos, funções e gatilhos que podem ser usados ​​durante a execução da instrução, carrega-os e, em seguida, executa a instrução. Isso significa que, enquanto a instrução é executada, ela não verá alterações nas rotinas executadas por outras threads.

Portanto, apesar de tudo, não tenho certeza de por que eles não permitem isso, mas posso adivinhar. Desculpe não poder te ajudar mais, estou aberto a investigar isso um pouco mais. O melhor é esperar por alguns desenvolvedores MySQL ativos assim que deixarmos o beta privado;)

Em grande parte, é devido à segurança. A exceção para procedimentos é porque o SQL dinâmico dentro do procedimento pode ser atribuído ao contexto de segurança do usuário em execução. Isso significa que, mesmo que o mecanismo não saiba o que será executado, ele pode garantir que o usuário tenha permissão para acessar o(s) objeto(s) referenciado(s).

Além disso, você pode levantar questões feias sobre o que poderia acontecer, se isso fosse permitido.