sqlite order not work in intermediate table

[papandadj]

I have a table like this:

I want get the last data of a group by name and reverse order output, like a table above, it shout be output 6, 5, 3.
I write a sql in navicat

select * from (
			select * from 'test'
			order by id desc
		)test1 group by name order by id desc;

It works well, and output:

BUT the same statement executes differently in go-sqlite3, it output 6, 4, 1.

This is my code ， which is convenient for you to debug.

package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"

	_ "github.com/mattn/go-sqlite3"
)

var db *sql.DB

const dbname = "./test.db"

func main() {
	var err error
	db, err = sql.Open("sqlite3", dbname)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	os.Remove(dbname)
	stmtCreate := `
		create table if not exists test (id integer not null primary key autoincrement, name text);
	`
	_, err = db.Exec(stmtCreate)
	if err != nil {
		log.Printf("%q: %s\n", err, stmtCreate)
		return
	}

	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}
	stmt, err := tx.Prepare("insert into test(name) values(?)")
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()
	stmt.Exec("a")
	stmt.Exec("a")
	stmt.Exec("a")
	stmt.Exec("b")
	stmt.Exec("b")
	stmt.Exec("c")
	tx.Commit()

	statSelect := `
		select * from (
			select * from 'test'
			order by id desc
		)test1 group by name order by id desc`
	rows, err := db.Query(statSelect)
	if err != nil {
		log.Printf("%q: %s\n", err, statSelect)
		return
	}
	defer rows.Close()
	for rows.Next() {
		fmt.Printf("rows: %v\n", rows)
	}
}

it work well in under the follow statement:

select * from (select * from (
			select * from 'test'
			order by id desc
		)test1 group by name) order by id desc

[rittneje]

I get the same output (6, 4, 1) from the latest SQLite CLI (3.38.5). I believe the source of the issue is that your query relies on undefined behavior. In particular:

If the SELECT statement is an aggregate query with a GROUP BY clause, [...] Each expression in the result-set is then evaluated once for each group of rows. If the expression is an aggregate expression, it is evaluated across all rows in the group. Otherwise, it is evaluated against a single arbitrarily chosen row from within the group.

https://www.sqlite.org/lang_select.html#generation_of_the_set_of_result_rows

Also note that as per the SQL spec, the order of records in a nested SELECT is not guaranteed to be preserved. If your intention is to select the largest id for each name, then you need to write your query like this:

SELECT MAX(id), name
FROM test
GROUP BY name

[papandadj]

I get the same output (6, 4, 1) from the latest SQLite CLI (3.38.5). I believe the source of the issue is that your query relies on undefined behavior. In particular:

If the SELECT statement is an aggregate query with a GROUP BY clause, [...] Each expression in the result-set is then evaluated once for each group of rows. If the expression is an aggregate expression, it is evaluated across all rows in the group. Otherwise, it is evaluated against a single arbitrarily chosen row from within the group.

https://www.sqlite.org/lang_select.html#generation_of_the_set_of_result_rows

Also note that as per the SQL spec, the order of records in a nested SELECT is not guaranteed to be preserved. If your intention is to select the largest id for each name, then you need to write your query like this:

SELECT MAX(id), name
FROM test
GROUP BY name

Thanks for you answer, I understand