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();
  }