Extended ORDER BY in sqlite_orm!

[juandent]

The extended ORDER BY clause is working with latest Dev branch:

	auto statement = storage.prepare(select(columns(&Employee::m_ename, &Employee::m_salary, &Employee::m_commission), 
		order_by(case_<int>().when(is_null(&Employee::m_commission), then(0)).else_(1).end()).desc()));
	auto sql = statement.expanded_sql();
	auto rows = storage.execute(statement);

Schema would be:

struct Employee
{
	int m_empno;
	std::string m_ename;
	std::string m_job;
	std::optional<int> m_mgr;
	std::string m_hiredate;
	double m_salary;
	std::optional<double> m_commission;
	int m_deptno;
};

struct Department
{
	int m_deptno;
	std::string m_deptname;
	std::string m_loc;

};

auto storage = make_storage("SQLCookbook.sqlite",
	make_table("Emp",
		make_column("empno", &Employee::m_empno, primary_key(), autoincrement()),
		make_column("ename", &Employee::m_ename),
		make_column("job", &Employee::m_job),
		make_column("mgr", &Employee::m_mgr),
		make_column("hiredate", &Employee::m_hiredate),
		make_column("salary", &Employee::m_salary),
		make_column("comm", &Employee::m_commission),
		make_column("deptno", &Employee::m_deptno),
		foreign_key(&Employee::m_deptno).references(&Department::m_deptno)),
	make_table("Dept",
		make_column("deptno", &Department::m_deptno, primary_key(), autoincrement()),
		make_column("deptname", &Department::m_deptname),
		make_column("loc", &Department::m_loc)));

[trueqbit]

Is that a question or a statement? :)

[fnc12]

it is an example which has order_by with case_ which works with bindings as Juan says. We need to check it out cause Klaus and SQLite team told that dynamic ordering doesn't work

[trueqbit]

Sorry if I am only slowly following all the gory details...

I expect this specific query to work. Do you expect something different?
As a reference it would be good to know what does not work, and describe expectations.

[fnc12]

wait I expect this query not to work (I mean ordering not to work) because it has ORDER BY CASE ... with question marks which has integers bound at runtime. SQLite team told you that ORDER BY works only with SQLite literals it means that ordering with binding data must not work correctly. I am wrong?

[trueqbit]

No, the SQLite team didn't say anything of that. They just agreed that ORDER BY ? is not very useful, because the result will always be unsorted. But it is a valid expression otherwise, and so are parameters in more complex ORDER-BY expressions.

[fnc12]

yes this is what I am talking about: SQLite team told that ordering doesn't work in this expression (cause sqlite_orm uses ? inside order_by_t recursively) but Juan told that expression from the first comment works with expected order

[trueqbit]

I still don't get the problem: Just because ORDER BY ? doesn't sort, it doesn't mean that parameters shouldn't work, when they are present in nested expressions.

[fnc12]

What do you mean parameters to work? The only way how parameters must work inside ORDER BY is ordering. So when someone tells that parameters work it means that ordering works and inverted otherwise

[trueqbit]

We are probably intermixing words from different abstraction levels here...
A parameter, that I am referring to, is a SQLite parameter of a parameterized query. This parameter is a placeholder for a value that will be bound to the parameter. sqlite_orm produces such a parametrized query when preparing the statement.

So, in this case (simplified)

auto statement = storage.prepare(select(asterisk<Employee>, 
		order_by(case_<int>().when(is_null(&Employee::m_commission), then(0)).else_(1).end())));

the SQL query string that gets passed to sqlite3_prepare_v2() should be:

SELECT * FROM Emp ORDER BY CASE WHEN comm IS NULL THEN ? ELSE ? END

This is desired as it allows reusing the prepared statement.

[fnc12]

ok I see. I am talking about working ordering with parameters. Juan tells me that ordering works, SQLite team tells that it doesn't

[trueqbit]

The term "not working" is relative it seems.
It may help us to define the wording "doesn't work" in terms of

• expectations
• resulting SQL query string
• validity of SQL query
• whether something makes sense
• ... and combinations of it

With this expression

auto statement = storage.prepare(select(asterisk<Employee>, 
                                        order_by(1)));

the SQL query string that gets passed to sqlite3_prepare_v2() is (current dev branch as of 2022-03-23):

SELECT * from Emp ORDER BY ?

As we are discussing already a long time, this statement IS CORRECT and works in the sense that it is NOT AN ERROR, and the parameter used as a whole order-by expression IS A VALID EXPRESSION. However the result set is unordered. So a parameter used as the only order-by expression doesn't make sense.

Because of our lengthy and insightful discussion PR #966 will change this in a way such that 'bindables' will be serialized as literal values.
So with this expression

auto statement = storage.prepare(select(asterisk<Employee>, 
                                        order_by(1)));

the SQL query string that gets passed to sqlite3_prepare_v2() will be:

SELECT * from Emp ORDER BY 1

Other expressions are still treated the same way as before.
Hence with this expression

auto statement = storage.prepare(select(asterisk<Employee>, 
                                        order_by(c(&Employee::m_commission) == 1)));

the SQL query string that gets passed to sqlite3_prepare_v2() will be:

SELECT * from Emp ORDER BY (comm = ?)

The unit test for AST iteration makes it clear on what's happening.

@fnc12 Not sure whether this still isn't what you want.

@juandent Can you please let us know what exactly works or does not work?

[juandent]

This query works (by that I mean: it compiles, it runs without exception throwing and it orders the rows correctly):

auto statement = storage.prepare(select(columns(&Employee::m_ename, &Employee::m_salary, &Employee::m_commission), 
		order_by(case_<int>().when(is_null(&Employee::m_commission), then(0)).else_(1).end()).desc()));
	auto sql = statement.expanded_sql();
	auto rows = storage.execute(statement);

Is this your question?

[fnc12]

thanks @trueqbit for details. Thanks @juandent for answering. Why are we talking so much about ordering? Ordering has only one and only meaning in SQL. And words 'working ordering' mean that ordering works as @juandent told. @trueqbit described all the history of the issue one more time. We need to check who is not right: Juan or SQLite team. Please note that I am repeating the same every message here. IDK what happened.

[fnc12]

Interesting results. Looks like Juan and SQLite team both right. ORDER BY 1 and ORDER BY 2 with bindings doesn't work. But ORDER BY CASE WHEN comm ISNULL THEN 0 ELSE 1 works. How did I understand this? Tried
ORDER BY CASE WHEN comm ISNULL THEN 2 ELSE 1 and
ORDER BY CASE WHEN comm ISNULL THEN 1 ELSE 1 and the results were diffirent

    {
        auto statement = storage.prepare(select(columns(&Employee::m_ename, &Employee::m_salary, &Employee::m_commission),
            order_by(case_<int>().when(is_null(&Employee::m_commission), then(2)).else_(1).end()).desc()));
        auto rows = storage.execute(statement);
        for (auto &row: rows) {
            cout << get<0>(row) << '\t' << get<1>(row) << '\t' << get<2>(row) << endl;
        }
        cout << endl;
    }
    {
        auto statement = storage.prepare(select(columns(&Employee::m_ename, &Employee::m_salary, &Employee::m_commission),
            order_by(case_<int>().when(is_null(&Employee::m_commission), then(1)).else_(2).end()).desc()));
        auto rows = storage.execute(statement);
        for (auto &row: rows) {
            cout << get<0>(row) << '\t' << get<1>(row) << '\t' << get<2>(row) << endl;
        }
        cout << endl;
    }
    {
        auto statement = storage.prepare(select(columns(&Employee::m_ename, &Employee::m_salary, &Employee::m_commission),
            order_by(1)));
        auto rows = storage.execute(statement);
        for (auto &row: rows) {
            cout << get<0>(row) << '\t' << get<1>(row) << '\t' << get<2>(row) << endl;
        }
        cout << endl;
    }
    {
        auto statement = storage.prepare(select(columns(&Employee::m_ename, &Employee::m_salary, &Employee::m_commission),
            order_by(2)));
        auto rows = storage.execute(statement);
        for (auto &row: rows) {
            cout << get<0>(row) << '\t' << get<1>(row) << '\t' << get<2>(row) << endl;
        }
        cout << endl;
    }

Output of this code:

Smith	800	null
Jones	2975	null
Blake	2850	null
Clark	2450	null
Scott	3000	null
King	5000	null
Adams	1100	null
James	950	null
Ford	3000	null
Miller	1300	null
Allen	1600	300
Ward	1250	500
Martin	1250	1400
Turner	1500	0

Allen	1600	300
Ward	1250	500
Martin	1250	1400
Turner	1500	0
Smith	800	null
Jones	2975	null
Blake	2850	null
Clark	2450	null
Scott	3000	null
King	5000	null
Adams	1100	null
James	950	null
Ford	3000	null
Miller	1300	null

Smith	800	null
Allen	1600	300
Ward	1250	500
Jones	2975	null
Martin	1250	1400
Blake	2850	null
Clark	2450	null
Scott	3000	null
King	5000	null
Turner	1500	0
Adams	1100	null
James	950	null
Ford	3000	null
Miller	1300	null

Smith	800	null
Allen	1600	300
Ward	1250	500
Jones	2975	null
Martin	1250	1400
Blake	2850	null
Clark	2450	null
Scott	3000	null
King	5000	null
Turner	1500	0
Adams	1100	null
James	950	null
Ford	3000	null
Miller	1300	null

@trueqbit do you know why changing CASE expression inside ORDER BY affects ordering if SQLite team told you that non literal expression is no-op?

[fnc12]

Actually I cannot understand how ORDER BY can have expression dependent on row cause ORDER BY expression must be a constant expression like 1, 2 or column name. I mean I know that ORDER BY can have any expression theoretically but SQLite team told you that arbitrary expression is no-op but this example shows that CASE expression changes ordering