namedParameters are limited to 256 entries?

[cclp94]

Hello,

while load testing our app this week, we noticed that whenever querying with > 256 params, the driver will fail with a 400 status. Is this a known limitation? Anything that can be done to mitigate this?

Example query:

session.executeStatement( `
  SELECT *      FROM table     WHERE ((organization_id IN (:org_0,:org_1,:org_2,:org_3,:org_4,:org_5,:org_6,:org_7,:org_8,:org_9,:org_10,:org_11,:org_12,:org_13,:org_14,:org_15,:org_16,:org_17,:org_18,:org_19,:org_20,:org_21,:org_22,:org_23,:org_24,:org_25,:org_26,:org_27,:org_28,:org_29,:org_30,:org_31,:org_32,:org_33,:org_34,:org_35,:org_36,:org_37,:org_38,:org_39,:org_40,:org_41,:org_42,:org_43,:org_44,:org_45,:org_46,:org_47,:org_48,:org_49,:org_50,:org_51,:org_52,:org_53,:org_54,:org_55,:org_56,:org_57,:org_58,:org_59,:org_60,:org_61,:org_62,:org_63,:org_64,:org_65,:org_66,:org_67,:org_68,:org_69,:org_70,:org_71,:org_72,:org_73,:org_74,:org_75,:org_76,:org_77,:org_78,:org_79,:org_80,:org_81,:org_82,:org_83,:org_84,:org_85,:org_86,:org_87,:org_88,:org_89,:org_90,:org_91,:org_92,:org_93,:org_94,:org_95,:org_96,:org_97,:org_98,:org_99,:org_100,:org_101,:org_102,:org_103,:org_104,:org_105,:org_106,:org_107,:org_108,:org_109,:org_110,:org_111,:org_112,:org_113,:org_114,:org_115,:org_116,:org_117,:org_118,:org_119,:org_120,:org_121,:org_122,:org_123,:org_124,:org_125,:org_126,:org_127,:org_128,:org_129,:org_130,:org_131,:org_132,:org_133,:org_134,:org_135,:org_136,:org_137,:org_138,:org_139,:org_140,:org_141,:org_142,:org_143,:org_144,:org_145,:org_146,:org_147,:org_148,:org_149,:org_150,:org_151,:org_152,:org_153,:org_154,:org_155,:org_156,:org_157,:org_158,:org_159,:org_160,:org_161,:org_162,:org_163,:org_164,:org_165,:org_166,:org_167,:org_168,:org_169,:org_170,:org_171,:org_172,:org_173,:org_174,:org_175,:org_176,:org_177,:org_178,:org_179,:org_180,:org_181,:org_182,:org_183,:org_184,:org_185,:org_186,:org_187,:org_188,:org_189,:org_190,:org_191,:org_192,:org_193,:org_194,:org_195,:org_196,:org_197,:org_198,:org_199,:org_200,:org_201,:org_202,:org_203,:org_204,:org_205,:org_206,:org_207,:org_208,:org_209,:org_210,:org_211,:org_212,:org_213,:org_214,:org_215,:org_216,:org_217,:org_218,:org_219,:org_220,:org_221,:org_222,:org_223,:org_224,:org_225,:org_226,:org_227,:org_228,:org_229,:org_230,:org_231,:org_232,:org_233,:org_234,:org_235,:org_236,:org_237,:org_238,:org_239,:org_240,:org_241,:org_242,:org_243,:org_244,:org_245,:org_246,:org_247,:org_248,:org_249,:org_250,:org_251,:org_252,:org_253)) AND (deleted_at IS NULL) AND (hard_deleted_at IS NULL)`, 
{ namedParameters: {...}});

[kravets-levko]

Hi @cclp94! Yes, this is a known limit, and it comes from server. I don't know if it can be increased (probably yes), but better reach out Databricks support with this request.

[samikshya-db]

Hi @kravets-levko : I hope you are well. It is always great to hear from you.

@cclp94 - Levko is right here. The limit comes from the server. Can you elaborate your use-case better and provide the maximum number of parameters you would want?

[cclp94]

Hi @kravets-levko : I hope you are well. It is always great to hear from you.

@cclp94 - Levko is right here. The limit comes from the server. Can you elaborate your use-case better and provide the maximum number of parameters you would want?

Hi! Thanks for the quick reply. We have a reporting platform inside of our application that leverages databricks for the data. In this platform, the user can add filters dynamically in our UI. We also validate and scope the data according to the organizations they have access to (hence the organization filter).

In the particular example I was testing, the user has access to ~270 organizations that would need to be included in the clause + whatever filters they choose for the data. This exceeded the limit quite easily. We haven't encountered the issue in production just yet, but with our clients expanding and using more complex filters, I can see the limit being exceeded in the future.

[cclp94]

Hi @cclp94! Yes, this is a known limit, and it comes from server. I don't know if it can be increased (probably yes), but better reach out Databricks support with this request.

Thanks @kravets-levko ! I'm already in contact with the team through an internal support channel our companies have.

[samikshya-db]

Got it, we have also received your parameter increase request from the support team. I will check internally and keep you posted. Thanks for reaching out!

[cclp94]

As a general question, is there a technical reason why I can't pass an array as a single parameter? For example in my example query having something like;

{ 
  query: "SELECT * from q where organization_id in (:orgs)",
  namedParameters: {
    orgs: [1,2,3,4,5...]
  }
}

[cclp94]

As a general question, is there a technical reason why I can't pass an array as a single parameter? For example in my example query having something like;

{ 
  query: "SELECT * from q where organization_id in (:orgs)",
  namedParameters: {
    orgs: [1,2,3,4,5...]
  }
}

Poking on this again. Is there a design choice why the above method is not supported for multiple values?