SQLite Forum

create unique index and order by not really unique or ordered properly
Login

create unique index and order by not really unique or ordered properly

(1) By rbucker on 2021-01-09 17:50:45 [link] [source]

My testing was in version 3.23...

Here are my tables:

CREATE TABLE Answers (AnswerID, Answer);
CREATE UNIQUE INDEX idx_answers on answers (AnswerID);

My application insert or replace rows in the DB. And then I started to notice some weird behavior only to determine that it was the side effects of the data. Looking at the data:

The order by failed here. The '-' is here to identify if there are embedded whitespace.

sqlite> select '-'||answerid||'-' from answers order by answerid;
-1030-
-1040-
-1060-
-1250-
-1270-
-1350-
-1030-
-1070-
-1120-

In the following SQL distinct did clean the duplicates but the order failed.

sqlite> select distinct '-'||answerid||'-' from answers order by answerid;
-1030-
-1040-
-1060-
-1250-
-1270-
-1350-
-1070-
-1120-

I'm drawing a blank on how to fix this or if it's encoding how to identify and repair or prevent.

(2) By Richard Hipp (drh) on 2021-01-09 17:54:20 in reply to 1 [link] [source]

What does this query show:

SELECT quote(answerid) FROM answers;

My guess: One or more of those entries that look like numbers are really strings.

(3) By Ryan Smith (cuz) on 2021-01-09 20:15:38 in reply to 1 [link] [source]

That's almost impossible unless the data are differently typed per row or you are using some custom collation that almost randomizes results (though your posted schema seems to exclude the latter possibility - if that is an accurate reproduction of the real schema).

Please run:

select answerid, typeof(answerid) from answers order by answerid;

PRAGMA table_info(Answers);

and post the results here.

(4) By rbucker on 2021-01-29 16:44:45 in reply to 2 [link] [source]

it has taken some time for this to show again...

sqlite> SELECT quote(answerid) FROM answers;
'10070'
'10090'
'10120'
'10140'
'10160'
'10440'
'10470'
'10540'
'11500'
'11630'
'11650'

and then there was this...

sqlite> select answerid, typeof(answerid) from answers;
10070|text
10090|text
10120|text
10140|text
10160|text
10440|text
10470|text
10540|text
11500|text
11630|text
11650|text

and yet this query still fails to return data

select * from answers where answerid in (10120,10160);

but then I tried this... and I got data.

select * from answers where cast(answerid as integer) in (10120,10160);
10120|http://bobville.com|2021-01-28 15:40:53|bob|Waiting for Review
10160|123-123-1234|2021-01-28 16:28:18|bob|Waiting for Review

(5) By rbucker on 2021-01-29 16:46:23 in reply to 3 [link] [source]

Here is the info you requested

sqlite> PRAGMA table_info(Answers);
0|AnswerID||0||0
1|Answer||0||0
2|lasteditdate||0||0
3|lastedituser||0||0
4|status||0||0

typeof...

sqlite> select answerid, typeof(answerid) from answers;
10070|text
10090|text
10120|text
10140|text
10160|text
10440|text
10470|text
10540|text
11500|text
11630|text
11650|text

(6) By Keith Medcalf (kmedcalf) on 2021-01-29 17:08:39 in reply to 4 [link] [source]

Correct --- because a field containing text (that has no affinity) can never be equal to an integer constant that has no affinity. They are different types.

Even though they may "look the same" when viewed after certain processing (such as printing in ASCII text on paper or a CRT) they are not the same.

Your "cast" converted the text items with no affinity to integers with integer affinity, so when the result was compared (using affinity) to the constants (integers having no affinity) the comparison was integer to integer, which could result TRUE result if the values were equal.

(7) By Keith Medcalf (kmedcalf) on 2021-01-29 17:11:46 in reply to 4 [source]

(8) By rbucker on 2021-01-29 20:41:51 in reply to 7 [link] [source]

THANKS! I did not see that coming. But makes perfect sense.