It seems that "ORDER BY" clause ignored by Butterfly.Core.Database.Memory.SelectRowsAsync()

[leapingbytes]

DB Created as:

_database = new Butterfly.Core.Database.Memory.MemoryDatabase();
...
await _database.CreateFromSqlAsync(
                @"CREATE TABLE event_store(
                  Id BIGINT AUTO_INCREMENT,
                  Timestamp DATETIME,
                  TableId CHAR(36),
                  Players TEXT,
                  Memo TEXT,
                  GuessPosition INT,
                  
                  PRIMARY KEY (Id),
                  INDEX byTableId(TableId),
                  INDEX byTableIdAndTimestamp(TableId, Timestamp)
                );"
            );

Simple Dump() method

private void Dump()
        {
            var selectTask = _database.SelectRowsAsync("select Id, Timestamp, TableId, Players, Memo, GuessPosition from event_store order by Id desc");

            foreach (var record in selectTask.Result)
            {
                Log.Info(JsonUtil.Serialize(record));
            }
        }

Example Output:

2019-05-08 09:03:36,383  INFO [9] - {"Id":0,"Timestamp":"2019-05-08 16:03:14","TableId":"fc3897e4-9f40-4300-b9c3-a05f73ae85e1","Players":"[]","Memo":"[]","GuessPosition":0}
2019-05-08 09:03:36,385  INFO [9] - {"Id":1,"Timestamp":"2019-05-08 16:03:29","TableId":"fc3897e4-9f40-4300-b9c3-a05f73ae85e1","Players":"[{\"ScreenName\":\"someblock\",\"SessionId\":\"session-1\"}]","Memo":"[]","GuessPosition":0}
2

Expected to see record with Id 1 first.

[firesharkstudios]

That's really odd. I'll try it later today and see what his happening. I suspect the problem is unique to MemoryDatabase but I'll see.

[leapingbytes]

It's really nice to hear from you :)

I think you most likely right, this is specific for MemoryDatabase... still, I feel that it either should throw NotImplementedException() exception when it sees ORDER BY or actually do sort the records.

I have another question, do you accept PRs for this project? I run into some limitations of RedHttpServerWebApi. Was able to fix it "outside" of butterfly codebase... but it would be much nicer to have all these throw new NotImplementedException(); taken care of. I would be more than happy to create few PRs to deal with these issues.

Cheers,
Andrei

[firesharkstudios]

Yes! I'm actively using Butterfly Server on multiple internal projects but I do not use the RedHttpServer implementation. It doesn't get as much love.

BTW, this line in MemoryDatabase is likely the problem...

DataRow[] dataRows = memoryTable.DataTable.Select(evaluatedWhereClause, null, DataViewRowState.OriginalRows);

The "null" is the parameter for the sort fields...oops. Have to run but this might be an easy fix later today.

[firesharkstudios]

Just committed fix