SQLite Forum

SELECT skips empty fields ?
Login

SELECT skips empty fields ?

(1) By Rudy Wieser (RWieser) on 2021-02-17 07:36:38 [link] [source]

Hello all,

A novice (in regard to sqlite3) here.

I've been trying to write a generic sqlite3 browser where I do a SELECT providing the current data to retrieve the next or previous screen-full :

SELECT * FROM moz_places WHERE (title, id) >= (?1, ?9) ORDER BY title, id LIMIT 34;

This seems to work well as long as the selected and sorted on field (title) contains something. When it contains nothing (returning empty strings) all the empty fields are skipped.

I thought I had bolted that down by providing the primary key as the last one, but I seem to have been wrong there.

I've tried to evoke the same behaviour on a colum that contains rows upon rows of the same string or value, but there everything works OK.

Question: What is goinging on there, and what do I need to do to fix it ?

Also, I noticed that in the WHERE clause I can group fields, but cannot(?) do that in the ORDER BY clause - "ORDER BY (title, id) DESC" does not seem to work. Yet, I do not get an error. What is the above ORDER BY supposed to be doing ?

(2) By Keith Medcalf (kmedcalf) on 2021-02-17 08:15:28 in reply to 1 [link] [source]

"ORDER BY (title, id) DESC" does not seem to work. Yet, I do not get an error.

this throws the error "row value misused", which is correct.

What is the above ORDER BY supposed to be doing ?

You mean the ORDER BY which contains a misused row value? It should be generating an error message because it is invalid.

This seems to work well as long as the selected and sorted on field (title) contains something. When it contains nothing (returning empty strings) all the empty fields are skipped.

Are you sure that you don't mean NULL rather than an empty string?

The obverse of:

SELECT * FROM moz_places WHERE (title, id) >= (?1, ?9) ORDER BY title, id LIMIT 34;

would be:

SELECT * FROM moz_places WHERE (title, id) <= (?1, ?9) ORDER BY title desc, id desc LIMIT 34

(5) By Rudy Wieser (RWieser) on 2021-02-17 13:23:29 in reply to 2 [link] [source]

this throws the error "row value misused", which is correct.
My bad : It turns out that I forgot to check directly after the 'prepare' statement. After I did I got the error you mentioned.

Still, I would have liked to be able to set the sorting direction for a / the total group of columns ...

Are you sure that you don't mean NULL rather than an empty string?
Blimy. Second mistake. I thought that I had updated the routine to print '<null>' on a NULL ptr. Checking just now shows I had - just not here. Yes, I get some NULL ptrs back from the sqlite3_column_text function.
SELECT * FROM moz_places WHERE (title, id) <= (?1, ?9) ORDER BY title desc, id desc LIMIT 34
That was what I, after some trying, ended up with. After that I thought that the grouping as used in the SELECT might also work with ORDER BY. The ebsense of an error threw me off.
Of course, if the value of title or id can be null, then you have to account for that in your select since you can only IS or IS NOT against NULL -- < > = <= >= will always be false.
Did I already mention I'm a novice in regard to sqlite3 ? :-)

I did some more googeling, and found COALESCE. Which works fine on a string column ("COALESCE(title,'')"). But when I try the same on a numeric column ("COALESCE(visit_count,0)") it returns nothing when I try to go down the list, and gets stuck when I try to go up the list. For a reason thats as thick as mud to me. Mind you, without that COALESCE on a numeric column everything seems to work.

I feel like I'm going one step forward, only to be put one step back again. :-| :-)

Also, if you have a better way to go at it than using COALESCE I would like to know.

 

P.s. Remarkable that I could post even before receiving and responding to a "did you really want to sign up" confirmation email first. I would think it would be heavily abused by spammers and other miscreants ...

(6.1) By Wout Mertens (wmertens) on 2021-02-17 15:08:18 edited from 6.0 in reply to 5 [link] [source]

I tried using fancy row values for paging, but in the end the only way I could make things work with mixed sort directions was by composing individual compares. See https://github.com/StratoKit/strato-db/blob/master/src/JsonModel/JsonModel.js line 375

Anyway, perhaps you wanted to use ifnull(val, replacement)?

(7) By Rudy Wieser (RWieser) on 2021-02-18 09:20:21 in reply to 6.1 [link] [source]

in the end the only way I could make things work with mixed sort directions was by composing individual compares
I'm sorry, but I'm not versed enough in JS to decypher it to see what it actually creates (and more importantly: why) ...
Anyway, perhaps you wanted to use ifnull(val, replacement)?
During my googleing I got the impression that COALESCE was its successor (if not, how do they differ ?). I just tried it though, and I get the same "skip everything" (inc) / "getting stuck" (desc) result as with COALESCE. I also tried other replacement values ( like 1 and -1), but that didn't change anything.

Also, as soon as I use COALESCE or IFNULL on a numeric column that doesn't have any NULLs I get the same as the above. Almost as if the commands themselves introduce a different comparing behaviour ...

(9) By Keith Medcalf (kmedcalf) on 2021-02-18 11:16:59 in reply to 7 [link] [source]

coalesce is the "standard" SQL function.

ifnull is an implementation-ism from MySQL (I believe). coalesce takes any number of arguments and evaluates to the first one from left to right which is not null. ifnull does exactly the same thing but only permits two arguments.

What do you mean by "skip everything" and "get stuck". Do you mean like a car getting stuck in the mud (or snow) and not going anywhere anymore?

(10) By Rudy Wieser (RWieser) on 2021-02-19 12:35:04 in reply to 9 [source]

What do you mean by "skip everything" and "get stuck". Do you mean like a car getting stuck in the mud (or snow) and not going anywhere anymore?.
Pretty-much that, yes.

When I grab the first screen-full of rows (order by ascending - no "where" clause) and than try to get the next batch from there I get a blank screen (zero results).

When I grab the last screen-full of rows (order by descending - again no "where" clause) and try to get the previous batch from there I get the same batch again. The wheels are spinning but I'm not going anywhere. :-\

But you are responding as if the above is new to you. Am I really the first one here who tries to browse thru an sqlite3 DB and is running into these problems ? Blimy...

(3) By Keith Medcalf (kmedcalf) on 2021-02-17 08:28:47 in reply to 1 [link] [source]

Of course, if the value of title or id can be null, then you have to account for that in your select since you can only IS or IS NOT against NULL -- < > = <= >= will always be false.

(4) By tom (younique) on 2021-02-17 12:16:23 in reply to 3 [link] [source]

But you can choose where NULL values should be placed by adding NULLS FIRST or NULLS LAST

(8) By Rudy Wieser (RWieser) on 2021-02-18 09:31:07 in reply to 4 [link] [source]

I have no problem seeing them SORT BY before everything. Its even logical to me. But yes, when I was googeling I saw that mentiononed to. Thanks though.