Hi all!

I´m getting the same error, using the SQL instruction below, which has 19519 bytes. Simplified versions of this query are creating plans with less than 1k, so i presume the plan of this query will not be greater than 48k.

So the first question is:

Why is this error happening with this query? Something to do with multibyte character sets? I´m connected to the database with Win1252.

The second question is:

Is there any plan to remove these limits? I can see a big problem here to use Firebird with tools like Hibernate which generate automatic SQL statements to map complex objects. Do someone here had these problems also? Is there some workaround? (maybe the solution is abuse of Hibernate lazy load feature)?

Regards,

Fabiano

select
pedidovend0_.nummv$pdv1 as nummv2_2_31_,
pedidovend0_.numcrt1 as numcrt9_2_31_,
pedidovend0_.comissao as comissao2_31_,
pedidovend0_.numcad1 as numcad10_2_31_,
pedidovend0_.departamento as departa11_2_31_,
pedidovend0_.emissao as emissao2_31_,
pedidovend0_.numemp1 as numemp12_2_31_,
pedidovend0_.nummv$lst1 as nummv13_2_31_,
pedidovend0_.referencia as referencia2_31_,
pedidovend0_.observacao as observacao2_31_,
pedidovend0_.numcad1fun as numcad14_2_31_,
pedidovend0_.prazo_pagto as prazo7_2_31_,
pedidovend0_.previsao as previsao2_31_,
pedidovend0_.numcad1red as numcad15_2_31_,
pedidovend0_.status_codigo as status16_2_31_,
pedidovend0_.numcad1tra as numcad17_2_31_,
pedidovend0_.numcad1rep as numcad18_2_31_,
carteira1_.numcrt1 as numcrt1_18_0_,
conta2_.numcad1 as numcad1_19_1_,
departamen3_.numv2$dpt1 as numv1_8_2_,
empresa4_.numemp1 as numemp1_12_3_,
empresa4_.aliquota_cofins as aliquota2_12_3_,
empresa4_.aliquota_csll as aliquota3_12_3_,
empresa4_.aliquota_icms as aliquota4_12_3_,
empresa4_.aliquota_inss as aliquota5_12_3_,
empresa4_.aliquota_ipi as aliquota6_12_3_,
empresa4_.aliquota_irpj as aliquota7_12_3_,
empresa4_.aliquotaiss as aliquota8_12_3_,
empresa4_.aliquota_pis as aliquota9_12_3_,
empresa4_.nfe_ambiente as nfe10_12_3_,
empresa4_.associar_cadastros as associar11_12_3_,
empresa4_.associar_financas as associar12_12_3_,
empresa4_.associar_materiais as associar13_12_3_,
empresa4_.bairro as bairro12_3_,
empresa4_.cep as cep12_3_,
empresa4_.nfe_certificado_digital_id as nfe40_12_3_,
empresa4_.chave as chave12_3_,
empresa4_.numcid1 as numcid41_12_3_,
empresa4_.cnae_id as cnae42_12_3_,
empresa4_.cgc as cgc12_3_,
empresa4_.reparticaofiscal as reparti18_12_3_,
empresa4_.comercio as comercio12_3_,
empresa4_.complemento as complem20_12_3_,
empresa4_.contato as contato12_3_,
empresa4_.email as email12_3_,
empresa4_.endereco as endereco12_3_,
empresa4_.fone2 as fone24_12_3_,
empresa4_.fone1 as fone25_12_3_,
empresa4_.icms_simples_aliquota_credito as icms26_12_3_,
empresa4_.icms_simples_tributacao as icms27_12_3_,
empresa4_.industria as industria12_3_,
empresa4_.inscricao as inscricao12_3_,
empresa4_.integracao_cc_clientes as integracao30_12_3_,
empresa4_.integracao_cc_fornecedores as integracao31_12_3_,
empresa4_.lista_precos_id_custo as lista43_12_3_,
empresa4_.lista_precos_id_venda as lista44_12_3_,
empresa4_.apelido as apelido12_3_,
empresa4_.numero as numero12_3_,
empresa4_.observacoes as observa34_12_3_,
empresa4_.numv2$org1 as numv45_12_3_,
empresa4_.numace1 as numace46_12_3_,
empresa4_.propriedades as proprie35_12_3_,
empresa4_.nome as nome12_3_,
empresa4_.regime_tributario as regime37_12_3_,
empresa4_.segmento_id as segmento47_12_3_,
empresa4_.servico as servico12_3_,
empresa4_.empresa_tipo as empresa39_12_3_,
certificad5_.certificado_digital_id as certific1_17_4_,
cidade6_.numcid1 as numcid1_0_5_,
cnae7_.cnae_id as cnae1_14_6_,
listapreco8_.nummv$lst1 as nummv1_13_7_,
listapreco8_.aceita_item_nao_existente as aceita2_13_7_,
listapreco8_.alteracao_prazo as alteracao3_13_7_,
listapreco8_.codigo as codigo13_7_,
listapreco8_.comissao as comissao13_7_,
listapreco8_.descricao as descricao13_7_,
listapreco8_.numemp1 as numemp14_13_7_,
listapreco8_.numv2$uni1_moeda as numv15_13_7_,
listapreco8_.observacao as observacao13_7_,
listapreco8_.numv2$org1 as numv16_13_7_,
listapreco8_.numace1 as numace17_13_7_,
listapreco8_.prazo_pagto as prazo8_13_7_,
listapreco8_.tipo as tipo13_7_,
listapreco8_.validade_final as validade10_13_7_,
listapreco8_.validade_inicial as validade11_13_7_,
listapreco8_.variacao_maior as variacao12_13_7_,
listapreco8_.variacao_menor as variacao13_13_7_,
empresa9_.numemp1 as numemp1_12_8_,
empresa9_.aliquota_cofins as aliquota2_12_8_,
empresa9_.aliquota_csll as aliquota3_12_8_,
empresa9_.aliquota_icms as aliquota4_12_8_,
empresa9_.aliquota_inss as aliquota5_12_8_,
empresa9_.aliquota_ipi as aliquota6_12_8_,
empresa9_.aliquota_irpj as aliquota7_12_8_,
empresa9_.aliquotaiss as aliquota8_12_8_,
empresa9_.aliquota_pis as aliquota9_12_8_,
empresa9_.nfe_ambiente as nfe10_12_8_,
empresa9_.associar_cadastros as associar11_12_8_,
empresa9_.associar_financas as associar12_12_8_,
empresa9_.associar_materiais as associar13_12_8_,
empresa9_.bairro as bairro12_8_,
empresa9_.cep as cep12_8_,
empresa9_.nfe_certificado_digital_id as nfe40_12_8_,
empresa9_.chave as chave12_8_,
empresa9_.numcid1 as numcid41_12_8_,
empresa9_.cnae_id as cnae42_12_8_,
empresa9_.cgc as cgc12_8_,
empresa9_.reparticaofiscal as reparti18_12_8_,
empresa9_.comercio as comercio12_8_,
empresa9_.complemento as complem20_12_8_,
empresa9_.contato as contato12_8_,
empresa9_.email as email12_8_,
empresa9_.endereco as endereco12_8_,
empresa9_.fone2 as fone24_12_8_,
empresa9_.fone1 as fone25_12_8_,
empresa9_.icms_simples_aliquota_credito as icms26_12_8_,
empresa9_.icms_simples_tributacao as icms27_12_8_,
empresa9_.industria as industria12_8_,
empresa9_.inscricao as inscricao12_8_,
empresa9_.integracao_cc_clientes as integracao30_12_8_,
empresa9_.integracao_cc_fornecedores as integracao31_12_8_,
empresa9_.lista_precos_id_custo as lista43_12_8_,
empresa9_.lista_precos_id_venda as lista44_12_8_,
empresa9_.apelido as apelido12_8_,
empresa9_.numero as numero12_8_,
empresa9_.observacoes as observa34_12_8_,
empresa9_.numv2$org1 as numv45_12_8_,
empresa9_.numace1 as numace46_12_8_,
empresa9_.propriedades as proprie35_12_8_,
empresa9_.nome as nome12_8_,
empresa9_.regime_tributario as regime37_12_8_,
empresa9_.segmento_id as segmento47_12_8_,
empresa9_.servico as servico12_8_,
empresa9_.empresa_tipo as empresa39_12_8_,
listapreco10_.nummv$lst1 as nummv1_13_9_,
listapreco10_.aceita_item_nao_existente as aceita2_13_9_,
listapreco10_.alteracao_prazo as alteracao3_13_9_,
listapreco10_.codigo as codigo13_9_,
listapreco10_.comissao as comissao13_9_,
listapreco10_.descricao as descricao13_9_,
listapreco10_.numemp1 as numemp14_13_9_,
listapreco10_.numv2$uni1_moeda as numv15_13_9_,
listapreco10_.observacao as observacao13_9_,
listapreco10_.numv2$org1 as numv16_13_9_,
listapreco10_.numace1 as numace17_13_9_,
listapreco10_.prazo_pagto as prazo8_13_9_,
listapreco10_.tipo as tipo13_9_,
listapreco10_.validade_final as validade10_13_9_,
listapreco10_.validade_inicial as validade11_13_9_,
listapreco10_.variacao_maior as variacao12_13_9_,
listapreco10_.variacao_menor as variacao13_13_9_,
unidade11_.numv2$uni1 as numv1_6_10_,
organizaca12_.numv2$org1 as numv1_20_11_,
pontoacess13_.numace1 as numace1_15_12_,
pontoacess13_.codigo as codigo15_12_,
pontoacess13_.codigo_bloqueado as codigo3_15_12_,
pontoacess13_.descricao as descricao15_12_,
organizaca14_.numv2$org1 as numv1_20_13_,
pontoacess15_.numace1 as numace1_15_14_,
pontoacess15_.codigo as codigo15_14_,
pontoacess15_.codigo_bloqueado as codigo3_15_14_,
pontoacess15_.descricao as descricao15_14_,
segmento16_.segmento_id as segmento1_5_15_,
segmento16_.ativo as ativo5_15_,
segmento16_.descricao as descricao5_15_,
segmento16_.discriminador as discrimi4_5_15_,
segmento16_.sistema as sistema5_15_,
itensbd17_.nummv$pdv1 as nummv43_2_33_,
itensbd17_.nummv$pdv2 as nummv1_33_,
itensbd17_.nummv$pdv2 as nummv1_16_16_,
itensbd17_.codigo_auxiliar as codigo2_16_16_,
itensbd17_.comissao as comissao16_16_,
itensbd17_.custo_unitario as custo4_16_16_,
itensbd17_.custo_total as custo5_16_16_,
itensbd17_.data as data16_16_,
itensbd17_.desconto_tipo as desconto7_16_16_,
itensbd17_.valor_total_desconto as valor8_16_16_,
itensbd17_.desconto_valor as desconto9_16_16_,
itensbd17_.despesa_variavel_total as despesa10_16_16_,
itensbd17_.numemp1 as numemp39_16_16_,
itensbd17_.gerar_movimento as gerar11_16_16_,
itensbd17_.icms_aliquota as icms12_16_16_,
itensbd17_.icms_aliquota_interna as icms13_16_16_,
itensbd17_.icms_base_calculo as icms14_16_16_,
itensbd17_.icms_base_calculo_total as icms15_16_16_,
itensbd17_.icms_subst_iva as icms16_16_16_,
itensbd17_.icms_subst_tipo as icms17_16_16_,
itensbd17_.icms_subst_tributacao as icms18_16_16_,
itensbd17_.icms_tributacao as icms19_16_16_,
itensbd17_.icms_valor_total as icms20_16_16_,
itensbd17_.ipi_aliquota as ipi21_16_16_,
itensbd17_.ipi_tributacao as ipi22_16_16_,
itensbd17_.ipi_valor_total as ipi23_16_16_,
itensbd17_.iss_aliquota as iss24_16_16_,
itensbd17_.iss_tributacao as iss25_16_16_,
itensbd17_.iss_valor_total as iss26_16_16_,
itensbd17_.numpro1 as numpro40_16_16_,
itensbd17_.base_calculo_produto as base27_16_16_,
itensbd17_.item_destino as item28_16_16_,
itensbd17_.item_origem as item29_16_16_,
itensbd17_.valor_total_produto as valor30_16_16_,
itensbd17_.nummv$lst1 as nummv41_16_16_,
itensbd17_.margem_contribuicao as margem31_16_16_,
itensbd17_.observacao as observacao16_16_,
itensbd17_.numopr2 as numopr42_16_16_,
itensbd17_.operacao_tipo as operacao33_16_16_,
itensbd17_.nummv$pdv1 as nummv43_16_16_,
itensbd17_.peso_total_liquido as peso34_16_16_,
itensbd17_.nummv$qld1 as nummv44_16_16_,
itensbd17_.quantidade as quantidade16_16_,
itensbd17_.quantidade_aux as quantidade36_16_16_,
itensbd17_.unidade_id as unidade45_16_16_,
itensbd17_.numv2$uni1_aux as numv46_16_16_,
itensbd17_.valor as valor16_16_,
itensbd17_.valor_lista as valor38_16_16_,
itensbd17_.variante_id as variante47_16_16_,
empresa18_.numemp1 as numemp1_12_17_,
empresa18_.aliquota_cofins as aliquota2_12_17_,
empresa18_.aliquota_csll as aliquota3_12_17_,
empresa18_.aliquota_icms as aliquota4_12_17_,
empresa18_.aliquota_inss as aliquota5_12_17_,
empresa18_.aliquota_ipi as aliquota6_12_17_,
empresa18_.aliquota_irpj as aliquota7_12_17_,
empresa18_.aliquotaiss as aliquota8_12_17_,
empresa18_.aliquota_pis as aliquota9_12_17_,
empresa18_.nfe_ambiente as nfe10_12_17_,
empresa18_.associar_cadastros as associar11_12_17_,
empresa18_.associar_financas as associar12_12_17_,
empresa18_.associar_materiais as associar13_12_17_,
empresa18_.bairro as bairro12_17_,
empresa18_.cep as cep12_17_,
empresa18_.nfe_certificado_digital_id as nfe40_12_17_,
empresa18_.chave as chave12_17_,
empresa18_.numcid1 as numcid41_12_17_,
empresa18_.cnae_id as cnae42_12_17_,
empresa18_.cgc as cgc12_17_,
empresa18_.reparticaofiscal as reparti18_12_17_,
empresa18_.comercio as comercio12_17_,
empresa18_.complemento as complem20_12_17_,
empresa18_.contato as contato12_17_,
empresa18_.email as email12_17_,
empresa18_.endereco as endereco12_17_,
empresa18_.fone2 as fone24_12_17_,
empresa18_.fone1 as fone25_12_17_,
empresa18_.icms_simples_aliquota_credito as icms26_12_17_,
empresa18_.icms_simples_tributacao as icms27_12_17_,
empresa18_.industria as industria12_17_,
empresa18_.inscricao as inscricao12_17_,
empresa18_.integracao_cc_clientes as integracao30_12_17_,
empresa18_.integracao_cc_fornecedores as integracao31_12_17_,
empresa18_.lista_precos_id_custo as lista43_12_17_,
empresa18_.lista_precos_id_venda as lista44_12_17_,
empresa18_.apelido as apelido12_17_,
empresa18_.numero as numero12_17_,
empresa18_.observacoes as observa34_12_17_,
empresa18_.numv2$org1 as numv45_12_17_,
empresa18_.numace1 as numace46_12_17_,
empresa18_.propriedades as proprie35_12_17_,
empresa18_.nome as nome12_17_,
empresa18_.regime_tributario as regime37_12_17_,
empresa18_.segmento_id as segmento47_12_17_,
empresa18_.servico as servico12_17_,
empresa18_.empresa_tipo as empresa39_12_17_,
item19_.numpro1 as numpro1_11_18_,
listapreco20_.nummv$lst1 as nummv1_13_19_,
listapreco20_.aceita_item_nao_existente as aceita2_13_19_,
listapreco20_.alteracao_prazo as alteracao3_13_19_,
listapreco20_.codigo as codigo13_19_,
listapreco20_.comissao as comissao13_19_,
listapreco20_.descricao as descricao13_19_,
listapreco20_.numemp1 as numemp14_13_19_,
listapreco20_.numv2$uni1_moeda as numv15_13_19_,
listapreco20_.observacao as observacao13_19_,
listapreco20_.numv2$org1 as numv16_13_19_,
listapreco20_.numace1 as numace17_13_19_,
listapreco20_.prazo_pagto as prazo8_13_19_,
listapreco20_.tipo as tipo13_19_,
listapreco20_.validade_final as validade10_13_19_,
listapreco20_.validade_inicial as validade11_13_19_,
listapreco20_.variacao_maior as variacao12_13_19_,
listapreco20_.variacao_menor as variacao13_13_19_,
operacaofi21_.numopr2 as numopr1_9_20_,
qualidade22_.nummv$qld1 as nummv1_4_21_,
unidade23_.numv2$uni1 as numv1_6_22_,
unidade24_.numv2$uni1 as numv1_6_23_,
variante25_.variante_id as variante1_10_24_,
listapreco26_.nummv$lst1 as nummv1_13_25_,
listapreco26_.aceita_item_nao_existente as aceita2_13_25_,
listapreco26_.alteracao_prazo as alteracao3_13_25_,
listapreco26_.codigo as codigo13_25_,
listapreco26_.comissao as comissao13_25_,
listapreco26_.descricao as descricao13_25_,
listapreco26_.numemp1 as numemp14_13_25_,
listapreco26_.numv2$uni1_moeda as numv15_13_25_,
listapreco26_.observacao as observacao13_25_,
listapreco26_.numv2$org1 as numv16_13_25_,
listapreco26_.numace1 as numace17_13_25_,
listapreco26_.prazo_pagto as prazo8_13_25_,
listapreco26_.tipo as tipo13_25_,
listapreco26_.validade_final as validade10_13_25_,
listapreco26_.validade_inicial as validade11_13_25_,
listapreco26_.variacao_maior as variacao12_13_25_,
listapreco26_.variacao_menor as variacao13_13_25_,
conta27_.numcad1 as numcad1_19_26_,
conta28_.numcad1 as numcad1_19_27_,
pedidovend29_.codigo as codigo3_28_,
conta30_.numcad1 as numcad1_19_29_,
conta31_.numcad1 as numcad1_19_30_
from
mv$pdv1 pedidovend0_
left outer join
crt1 carteira1_
on pedidovend0_.numcrt1=carteira1_.numcrt1
inner join
cad1 conta2_
on pedidovend0_.numcad1=conta2_.numcad1
inner join
v2$dpt1 departamen3_
on pedidovend0_.departamento=departamen3_.numv2$dpt1
inner join
emp1 empresa4_
on pedidovend0_.numemp1=empresa4_.numemp1
left outer join
v3$certificados_digitais certificad5_
on empresa4_.nfe_certificado_digital_id=certificad5_.certificado_digital_id
left outer join
cid1 cidade6_
on empresa4_.numcid1=cidade6_.numcid1
left outer join
v3$cnae cnae7_
on empresa4_.cnae_id=cnae7_.cnae_id
left outer join
mv$lst1 listapreco8_
on empresa4_.lista_precos_id_custo=listapreco8_.nummv$lst1
left outer join
emp1 empresa9_
on listapreco8_.numemp1=empresa9_.numemp1
left outer join
mv$lst1 listapreco10_
on empresa9_.lista_precos_id_venda=listapreco10_.nummv$lst1
left outer join
v2$uni1 unidade11_
on listapreco10_.numv2$uni1_moeda=unidade11_.numv2$uni1
left outer join
v2$org1 organizaca12_
on listapreco10_.numv2$org1=organizaca12_.numv2$org1
left outer join
ace1 pontoacess13_
on listapreco10_.numace1=pontoacess13_.numace1
left outer join
v2$org1 organizaca14_
on empresa9_.numv2$org1=organizaca14_.numv2$org1
left outer join
ace1 pontoacess15_
on empresa9_.numace1=pontoacess15_.numace1
left outer join
v3$segmentos segmento16_
on empresa9_.segmento_id=segmento16_.segmento_id
left outer join
mv$pdv2 itensbd17_
on pedidovend0_.nummv$pdv1=itensbd17_.nummv$pdv1
left outer join
emp1 empresa18_
on itensbd17_.numemp1=empresa18_.numemp1
left outer join
pro1 item19_
on itensbd17_.numpro1=item19_.numpro1
left outer join
mv$lst1 listapreco20_
on itensbd17_.nummv$lst1=listapreco20_.nummv$lst1
left outer join
opr2 operacaofi21_
on itensbd17_.numopr2=operacaofi21_.numopr2
left outer join
mv$qld1 qualidade22_
on itensbd17_.nummv$qld1=qualidade22_.nummv$qld1
left outer join
v2$uni1 unidade23_
on itensbd17_.unidade_id=unidade23_.numv2$uni1
left outer join
v2$uni1 unidade24_
on itensbd17_.numv2$uni1_aux=unidade24_.numv2$uni1
left outer join
v3$variantes variante25_
on itensbd17_.variante_id=variante25_.variante_id
left outer join
mv$lst1 listapreco26_
on pedidovend0_.nummv$lst1=listapreco26_.nummv$lst1
left outer join
cad1 conta27_
on pedidovend0_.numcad1fun=conta27_.numcad1
left outer join
cad1 conta28_
on pedidovend0_.numcad1red=conta28_.numcad1
inner join
v3$pedidos_vendas_status pedidovend29_
on pedidovend0_.status_codigo=pedidovend29_.codigo
left outer join
cad1 conta30_
on pedidovend0_.numcad1tra=conta30_.numcad1
left outer join
cad1 conta31_
on pedidovend0_.numcad1rep=conta31_.numcad1
where
pedidovend0_.nummv$pdv1=?

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 03:34 AM 25/04/2006, you wrote:
> >Hello,
> >
> >I am using Firebird 1.5.2 for my project and I encountered one
> >problem. When executing a (rather complex) query I get the following
> >error messages:
> >
> >Implementation limit exceeded
> >Block size exceeds implementation restriction
> >
> >Besides Firebird I am using Hibernate for the OR mapping. I have
> >searched the web for the description and found only the following page
> >http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_fb1_faq
> >describing the problem (at the bottom of the page). However, I am not
> >sure where exactly the problem is and what does "..expressions do not
> >exceed the maximum size of a column..." mean.
>
> It means (either or both) that your SQL statement is larger than 64
> Kb or the query plan is larger than 48 Kb.
>
> Some thoughts:
>
> --Don't overlook the size of data in your parameterised expressions
> --Consider the proliferation of bytes in the resolved SQL and the
> plan from using IN() (which resolves under the hood to a sometimes
> huge number of OR clauselets).
> --In fact, if you are using IN() at all, you would do well to
> rephrase your query to use EXISTS() instead, wherever possible
> --If you are joining to views, remember that the plan could be
> exceedingly complex
> --Consider places where you might be using joins unnecessarily, where
> a correlated subquery might do the trick better and distribute the
> load into separate plans
> --If the query is such that it is jostling at the outer limits, it's
> probably a sign that this op needs to be simplified into a selectable
> SP. A 64Kb select statement or a 48 Kb plan is not particularly practicable...
>
> ./heLen
>