Does this driver have trouble with `ORDER BY CASE ... END` clauses?

[mholt]

Hi, sorry if this is a weird question. But I noticed that I have a (rather long, complicated) query that is perfectly successful until I add this clause:

ORDER BY CASE
	WHEN items.data_text LIKE '?%' THEN 1
	WHEN items.data_text LIKE '%?' THEN 3
	ELSE 2
END

Then, the driver returns datatype mismatch when I call rows.Next(). (That's all; that's the whole error.) Searching the Web for that exact error string brings me to sqlite source code itself, where it seems to come from deep within the sqlite library and I'm pretty sure there be dragons. (Anyway, I'm scared to go down there alone.)

Oddly, TablePlus (a database GUI) runs the exact same query successfully on the same database without any errors. My Go program works with other ORDER BY clauses, just not this one with the CASE.

So I suspect something might be wrong with the driver, but I have no idea, so I'm asking here... is it a bug? Thanks for any insights.

PS. I'm using v1.14.12 with sqlite 3.38.

[rittneje]

Please provider a complete minimal reproducer, including the schema, the data, the full SELECT query, and the relevant Go code.

[mholt]

I'm having trouble reproducing it with a minimal test case. (It works with a simple example.) Doing so might be problematic; the table has hundreds of thousands of rows of personal information, and the code is also quite complex -- but I've boiled it down to printing out the query it's running, then running the same query in TablePlus, to see that the query itself works. And the error in the Go program comes from rows.Next(), which is not my code. 🤷

[mholt]

Closing, since I can't simply reproduce it.

[rittneje]

@mholt My suggestion would be to double-check your parameter bindings in Go. One of the few ways I'm able to get SQLITE_MISMATCH to happen is by trying to bind, say, a string value to LIMIT ?.

[mholt]

Hmm, thanks for the tip! I will look into that.