Repository: kestra-io/kestra
Issue: Execution sort error
Labels: bug

### Expected Behavior

![image](https://user-images.githubusercontent.com/44800439/225796256-234a2625-bd77-414d-9cb5-271bd4360cd0.png)
Internal server error: SQL [select *, count(*) over () as "total_count" from (select "value" from executions where "deleted" = ? order by "id" desc) as "page" where true offset ? rows fetch next ? rows only]; ERROR: column "id" does not exist Position: 112

### Actual Behaviour

Create a pause Executions and check whether the executions are restarted after being deleted. After the executions are deleted, an error is reported when the executions list is sorted by ID.

### Steps To Reproduce

1, create pause flow
2, delete pause Executions
3, create pause Executions
4, Executions page id sort

### Environment Information

- Kestra Version: 0.8.0-SNAPSHOT
- Operating System (OS / Docker / Kubernetes): Kubernetes
- Java Version (If not docker): 1.8


### Example flow

_No response_

Issue comments:
Hi @cuiweining thanks for reporting.
I tested it and in fact it is not linked to the pause task or an execution deletion but sorting on the `id` field didn't work at all.
I'll look at it.
@cuiweining the bug is fixed and is available on the new 0.8.0-SNAPSHOT that was published minutes ago and should be available if you use our `develop-full` Docker image.
You can try it and provides feedback if any.

Pull request review comments:

Patch that resolved the issue:
diff --git a/jdbc-h2/src/main/resources/migrations/h2/V6__missing_execution_id.sql b/jdbc-h2/src/main/resources/migrations/h2/V6__missing_execution_id.sql
new file mode 100644
index 0000000000..e1a547265c
--- /dev/null
+++ b/jdbc-h2/src/main/resources/migrations/h2/V6__missing_execution_id.sql
@@ -0,0 +1,1 @@
+ALTER TABLE executions ADD COLUMN "id" VARCHAR(150) NOT NULL GENERATED ALWAYS AS (JQ_STRING("value", '.id'))
\ No newline at end of file
diff --git a/jdbc-postgres/src/main/resources/migrations/postgres/V7__missing_execution_id.sql b/jdbc-postgres/src/main/resources/migrations/postgres/V7__missing_execution_id.sql
new file mode 100644
index 0000000000..cbdb82b8ce
--- /dev/null
+++ b/jdbc-postgres/src/main/resources/migrations/postgres/V7__missing_execution_id.sql
@@ -0,0 +1,1 @@
+ALTER TABLE executions ADD COLUMN id VARCHAR(150) NOT NULL GENERATED ALWAYS AS (value ->> 'id') STORED
\ No newline at end of file


Test patch:
diff --git a/core/src/test/java/io/kestra/core/repositories/AbstractExecutionRepositoryTest.java b/core/src/test/java/io/kestra/core/repositories/AbstractExecutionRepositoryTest.java
index b48c1773b7..38ad4e453a 100644
--- a/core/src/test/java/io/kestra/core/repositories/AbstractExecutionRepositoryTest.java
+++ b/core/src/test/java/io/kestra/core/repositories/AbstractExecutionRepositoryTest.java
@@ -10,6 +10,7 @@
 import io.kestra.core.models.tasks.ResolvedTask;
 import io.kestra.core.tasks.debugs.Return;
 import io.micronaut.data.model.Pageable;
+import io.micronaut.data.model.Sort;
 import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
 import jakarta.inject.Inject;
 import org.junit.jupiter.api.Test;
@@ -113,6 +114,18 @@ protected void find() {
         assertThat(executions.getTotal(), is(8L));
     }
 
+    @Test
+    protected void findWithSort() {
+        inject();
+
+        ArrayListTotal<Execution> executions = executionRepository.find(Pageable.from(1, 10, Sort.of(Sort.Order.desc("id"))),  null, null, null, null, null, null);
+        assertThat(executions.getTotal(), is(28L));
+        assertThat(executions.size(), is(10));
+
+        executions = executionRepository.find(Pageable.from(1, 10),  null, null, null, null, null, List.of(State.Type.RUNNING, State.Type.FAILED));
+        assertThat(executions.getTotal(), is(8L));
+    }
+
     @Test
     protected void findTaskRun() {
         inject();