Uploaded image for project: 'Qt'
  1. Qt
  2. QTBUG-95610

SQLite SQL driver plugin: Named parameters and "Parameter count mismatch" Error

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Reported
    • Priority: P4: Low
    • Resolution: Unresolved
    • Affects Version/s: 5.15.2, 6.1.2
    • Fix Version/s: None
    • Component/s: SQL Support
    • Labels:
      None

      Description

      SQLite driver handles named parameters wrong. It can't properly count them if they are something other than question marks of `:NNN` double-color prefixed. Namely, "@" and "$" don't work at all, and "?XXX" is not consistent with ":XXX" when not really bound to anything.

      What the heck is this mess? It is doing too many checks no one asked for.

      qtbase/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp
      #if (SQLITE_VERSION_NUMBER >= 3003011)
          // In the case of the reuse of a named placeholder
          // We need to check explicitly that paramCount is greater than or equal to 1, as sqlite
          // can end up in a case where for virtual tables it returns 0 even though it
          // has parameters
          if (paramCount >= 1 && paramCount < values.count()) {
              const auto countIndexes = [](int counter, const QList<int> &indexList) {
                                            return counter + indexList.length();
                                        };
      
              const int bindParamCount = std::accumulate(d->indexes.cbegin(),
                                                         d->indexes.cend(),
                                                         0,
                                                         countIndexes);
      
              paramCountIsValid = bindParamCount == values.count();
              // When using named placeholders, it will reuse the index for duplicated
              // placeholders. So we need to ensure the QList has only one instance of
              // each value as SQLite will do the rest for us.
              QList<QVariant> prunedValues;
              QList<int> handledIndexes;
              for (int i = 0, currentIndex = 0; i < values.size(); ++i) {
                  if (handledIndexes.contains(i))
                      continue;
                  const char *parameterName = sqlite3_bind_parameter_name(d->stmt, currentIndex + 1);
                  if (!parameterName) {
                      paramCountIsValid = false;
                      continue;
                  }
                  const auto placeHolder = QString::fromUtf8(parameterName);
                  const auto &indexes = d->indexes.value(placeHolder);
                  handledIndexes << indexes;
                  prunedValues << values.at(indexes.first());
                  ++currentIndex;
              }
              values = prunedValues;
          }
      #endif
      

      Observed behavior

      Execute query VALUES ( ? ) with no parameters: Error: Parameter count mismatch
      Execute query VALUES ( ?1 ) with no parameters: Error: Parameter count mismatch
      Execute query VALUES ( :one ) with no parameters:

       {"column1":null} 

      Execute query VALUES ( @one ) with no parameters: Error: Parameter count mismatch
      Execute query VALUES ( $one ) with no parameters: Error: Parameter count mismatch

      Execute query VALUES ( :one, :two ) with parameters

      {":one": 42, ":two": "Yahaha!"}

      results in:

      {"column1":42,"column2":"Yahaha!"}

      Execute query VALUES ( :one, $two ) with parameters

      {":one": 42, "$two": "Yahaha!"}

      results in Error: Parameter count mismatch

      But most importantly,

      Executing query VALUES ( :one, $two ) with array of parameters [42, "Yahaha!"] results in:

      {"column1":42,"column2":"Yahaha!"}

      Expected behavior

      At this point I don't know what to expect, and I feel like I might be loosing it.

        Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            People

            Assignee:
            mabrand Mark Brand
            Reporter:
            ratijas ivan tkachenko
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Gerrit Reviews

                There are no open Gerrit changes