"AS OF" doesn't work with partial commit hashes, trying to improvise this crashes Dolt

[LaPingvino]

When trying to use a partial commit_hash after AS OF, this is not supported. I think it would be good to have a simple utility function for this that basically does select commit_hash from dolt_log where commit_hash like 'partialhash%' under the cover. (e.g. dolt_commit_hash('partialhash') or HASH('partialhash'));

Trying this right now inline crashes Dolt:

[timsehn]

We'll fix this today.

[jycor]

Hey @LaPingvino, thanks for reporting this issue!
I have a fix to prevent the crash here
Unfortunately, that does mean your clever workaround using partial matching through a subquery is not going work (at least for now).
If you're willing to break this up into two queries, you can use SELECT ... INTO and user variables to get the same result.

tmp2/main> select * from dolt_log;
+----------------------------------+-----------+-------------------+---------------------+----------------------------+
| commit_hash                      | committer | email             | date                | message                    |
+----------------------------------+-----------+-------------------+---------------------+----------------------------+
| 4a6d4t3osnk04v6o26he97h9tch2f2pd | jcor      | james@dolthub.com | 2024-12-04 19:03:55 | more inserts               |
| qo2avqkh4lhpjm0n8e6j4k7h2of2ml0o | jcor      | james@dolthub.com | 2024-12-04 19:03:27 | init table                 |
| m7kkaheh6qouv7fl7k6lf29uo6rh70fi | jcor      | james@dolthub.com | 2024-12-04 19:02:53 | Initialize data repository |
+----------------------------------+-----------+-------------------+---------------------+----------------------------+
3 rows in set (0.00 sec)

tmp2/main> select * from t;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---+
6 rows in set (0.00 sec)

tmp2/main> select commit_hash into @hash from dolt_log where commit_hash like "q%";
+
|
+
|
+
1 row in set (0.00 sec)

tmp2/main> select * from t as of @hash;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

We do support using HEAD~n syntax and a hashof() function

tmp2/main> select * from t as of hashof('HEAD');
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---+
6 rows in set (0.00 sec)

tmp2/main> select * from t as of hashof('HEAD~1');
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

tmp2/main> select * from t as of hashof('HEAD~2');
table not found: t

tmp2/main> select * from t as of hashof('HEAD~3');
invalid ancestor spec

Perhaps there is another way through JOINs and CTEs, but I haven't been able to come up with one...

I'm looking into whether or not we can implement partial commit hash matching for AS OF, and will keep you posted!