Unhandled Exception: Bad state: Expected exactly one element, but got 2

[javdhu]

I seem to get this bug on a table with a joint primary key. I use getSingleOrNull to get one element and it returns two. The two returned have different primary keys even though I explicitly query by one primary key. This is the exact error [ERROR:flutter/lib/ui/ui_dart_state.cc(209)] Unhandled Exception: Bad state: Expected exactly one element, but got 2

[simolus3]

@jdaev This will be hard to reproduce for me without seeing a working example? Can you post the basic tables and query that unexpectedly yields more than one row?

[javdhu]

Tables:

CREATE TABLE folder_entries (
    id TEXT NOT NULL,
    driveId TEXT NOT NULL,

    name TEXT NOT NULL,
    parentFolderId TEXT,
    path TEXT NOT NULL,

    dateCreated DATETIME NOT NULL DEFAULT (strftime('%s','now')),
    lastUpdated DATETIME NOT NULL DEFAULT (strftime('%s','now')),

    PRIMARY KEY (id, driveId)
) As FolderEntry;
import 'network_transactions.moor';

CREATE TABLE folder_revisions (
    folderId TEXT NOT NULL,
    driveId TEXT NOT NULL,

    name TEXT NOT NULL,
    parentFolderId TEXT,

    metadataTxId TEXT NOT NULL,

    dateCreated DATETIME NOT NULL DEFAULT (strftime('%s','now')),

    [action] TEXT NOT NULL,

    PRIMARY KEY (folderId, driveId, dateCreated),
    FOREIGN KEY (metadataTxId) REFERENCES network_transactions(id)
);

Queries

folderById:
    SELECT * FROM folder_entries
    WHERE driveId = :driveId AND id = :folderId;
oldestFolderRevisionByFolderId:
    SELECT * FROM folder_revisions
    WHERE driveId = :driveId AND folderId = :folderId
    ORDER BY dateCreated ASC 
    LIMIT 1;
latestFolderRevisionByFolderId:
    SELECT * FROM folder_revisions
    WHERE driveId = :driveId AND folderId = :folderId
    ORDER BY dateCreated DESC
    LIMIT 1;

Functions:

Selectable<FolderEntry> folderById(
      {required String driveId, required String folderId}) {
    return customSelect(
        'SELECT * FROM folder_entries\n    WHERE driveId = :driveId AND id = :folderId',
        variables: [
          Variable<String>(driveId),
          Variable<String>(folderId)
        ],
        readsFrom: {
          folderEntries,
        }).map(folderEntries.mapFromRow);
  }
  Selectable<FolderRevision> oldestFolderRevisionByFolderId(
      {required String driveId, required String folderId}) {
    return customSelect(
        'SELECT * FROM folder_revisions\n    WHERE driveId = :driveId AND folderId = :folderId\n    ORDER BY dateCreated ASC \n    LIMIT 1',
        variables: [
          Variable<String>(driveId),
          Variable<String>(folderId)
        ],
        readsFrom: {
          folderRevisions,
        }).map(folderRevisions.mapFromRow);
  }

  Selectable<FolderRevision> latestFolderRevisionByFolderId(
      {required String driveId, required String folderId}) {
    return customSelect(
        'SELECT * FROM folder_revisions\n    WHERE driveId = :driveId AND folderId = :folderId\n    ORDER BY dateCreated DESC\n    LIMIT 1',
        variables: [
          Variable<String>(driveId),
          Variable<String>(folderId)
        ],
        readsFrom: {
          folderRevisions,
        }).map(folderRevisions.mapFromRow);
  }

The place where it throws the exception:

parentPath = (await _driveDao
            .folderById(
                driveId: driveId, folderId: treeRoot.folder.parentFolderId!)
            .map((f) => f.path)
            .getSingleOrNull());

Here the folderById returns two folders

[simolus3]

Thanks for posting that. From a quick look, it indeed looks like the query should only return one row, the generated code looks correct to me as well.

Can this be reproduced by just writing whatever into folder_entries? Or do I need to write specific values for folderById to show this behavior?

[javdhu]

You can write whatever. The id's are generated with uuid.

[Rossdex]

I had a similar issue to this and resolved it by putting a limit on my queries where I know I only want 1.

  Stream<D?> getBy(String key, dynamic val) {
    CustomExpression<bool> findByExpression = CustomExpression<bool>("$key = '$val'");
    int? userId = db.sharedPreferences.getInt('current_user_id');

    return (select(_table)
          ..where((tbl) => findByExpression)
          ..where((tbl) => tbl.currentUserId.equals(userId))
          ..orderBy([(tbl) => OrderingTerm(expression: tbl.id, mode: OrderingMode.desc)])
          ..limit(1))
        .watchSingleOrNull();
  }

[srix55]

UPDATE: it's got nothing to do with isolates. Rather, it's got something to do with nullable columns in a compound primary key.

I'm running into this issue too. Seems like two insertions are happening even if they violate the primary key constraint. It seems to be happening when insertions are made to the same database over different isolates simultaneously. Violating insertions in the same isolate doesn't cause duplicates. I am using worker_manager for managing isolates & hooking up drift for isolates using the pre flutter 3.7 way.

Let me build a reproducible code.

[srix55]

I realize that none of the databases (mysql & postgresql) allow nullability in any of the columns of a compound primary key. That is primary key is always non-nullable - be it a single column or multiple - all columns have to be non-nullable.

• Drift allows columns of a compound primary key to be nullable
• Drift doesn't guarantee primary key uniqueness when same records (with null in nullable columns) are inserted

Here's the reproducible code: https://github.com/srix55/drift_primary_key_issue

[simolus3]

With sqlite3, it's possible to have nullable primary keys. As the docs mention, that is more of a defect in sqlite3 than anything else, but that's what it is. Two nulls being counted as distinct values in primary key is also the intended behavior.

I wonder what the best course of action here is - adding a warning probably?

[srix55]

True... it's an SQLite bug. Many others are facing the same - https://github.com/prisma/prisma/issues/3130 https://stackoverflow.com/questions/64753105/why-can-i-add-null-value-to-primary-key-in-sqlite

Drift should not be addressing this, imo. Let's make sure not to declare nullable primary keys.

[simolus3]

Yeah. I'll consider making STRICT the default in a future drift version, which would fix this.

The original issue description had all involved columns declared as non-null though, so it's possible that something else is/was going wrong here as well.