Batch insert query generation bug

[ilyachase]

What steps will reproduce the problem?

$sphinx->createCommand()->batchInsert(
'some_index',
[
  'id' => 'id',
  'URL' => 'URL',
  'MozRank' => 'MozRank',
  'Alexa_Rank' => 'Alexa_Rank',
  'SR_Rank' => 'SR_Rank',
  'checkedByUclassify' => 'checkedByUclassify',
  'uclassify_category' => 'uclassify_category',
  'is_alive' => 'is_alive',
],
[
  [
    'id' => 3671044397895311365,
    'URL' => '0-booksinprint2.com.henrietta.slwa.wa.gov.au',
    'MozRank' => 0.0,
    'Alexa_Rank' => 497024,
    'SR_Rank' => 697840,
    'checkedByUclassify' => true,
    'uclassify_category' => 'television_factual tv_27_5',
    'is_alive' => true,
  ],
  [
    'id' => 8798971899820158926,
    'URL' => '0-eds.a.ebscohost.com.library.vu.edu.au',
    'MozRank' => 0.0,
    'Alexa_Rank' => null,
    'SR_Rank' => 50381,
    'checkedByUclassify' => true,
    'uclassify_category' => 'technology and computing_browsers_26_3_3_3',
    'is_alive' => true,
  ],
  [
    'id' => 2017977390763813861,
    'URL' => '0-infoweb.newsbank.com.henrietta.slwa.wa.gov.au',
    'MozRank' => 0.0,
    'Alexa_Rank' => 463318,
    'SR_Rank' => 0,
    'checkedByUclassify' => true,
    'uclassify_category' => 'television_factual tv_27_5',
    'is_alive' => true,
  ],
]

Take note that in batchData there is field with null ('Alexa_Rank' => null). Then, because of continue at /src/QueryBuilder.php:304, it will generate wrong query:

INSERT INTO `domains` (`id`, `URL`, `MozRank`, `Alexa_Rank`, `SR_Rank`, `checkedByUclassify`, `uclassify_category`, `is_alive`) VALUES (:qp0, :qp1, :qp2, :qp3, :qp4, :qp5, :qp6, :qp7), (:qp8, :qp9, :qp10, :qp11, :qp12, :qp13, :qp14), (:qp15, :qp16, :qp17, :qp18, :qp19, :qp20, :qp21, :qp22)

It happens because $notNullColumns was filled at first correct batch item, but in case of second batch item, code just skips one value and does not bind anything.

And it results in SQLSTATE[42000]: Syntax error or access violation: 1064 sphinxql: wrong number of values here near '), (-8263165427741034520, '0-infoweb.newsbank.com.henrietta.slwa.wa.gov.au', 0, 463318, 0, 1, 'television_factual tv_27_5', 1). There is indeed wrong binding placeholders (7) in second values group of the query.

What's expected?

I'm not sure. May be an exception? Not sure if sphinx or manticore allows NULL value. At least query

INSERT INTO `domains` (`id`, `URL`, `MozRank`, `Alexa_Rank`, `SR_Rank`, `checkedByUclassify`, `uclassify_category`, `is_alive`) VALUES  (7450185433363622911, '0-infoweb.newsbank.com.henrietta.slwa.wa.gov.au', 0, null, 0, TRUE, 'television_factual tv_27_5', TRUE);

results in ERROR 1064 (42000): sphinxql: syntax error, unexpected NULL near 'null, 0, TRUE, 'television_factual tv_27_5', TRUE)'. My index scheme:

index domains
{
    type = rt
    path = /var/lib/manticore/data/domains
    rt_mem_limit = 4096M

    rt_field = URL
    rt_field = uclassify_category
    rt_attr_string = URL
    rt_attr_bool = is_alive
    rt_attr_float = MozRank
    rt_attr_uint = Alexa_Rank
    rt_attr_uint = SR_Rank
    rt_attr_bool = checkedByUclassify
}

But I think it is not corresponding with the query building problem, because it anyways looks bad to generate query with wrong number of values. For example, in Mysql Query Builder there is no continue in values binding loop: vendor/yiisoft/yii2/db/QueryBuilder.php:462.

[ilyachase]

I did some more research and found out that both sphinx and manticore can't store NULL values. So in this case we may just throw an exception.
In case of ActiveRecord maybe we can use rules() to cast to "null" value according to scheme.