System.Data.SQLite

I'm writing a Media Management application, and thought everything was working smoothly until I had to update my media player to make use of alternative data views, and it started breaking when the correct number of tracks for a particular playlist appeared to be incorrect.  I've attached the database in question for replication purposes.  I'm using an SQLCommand with a dataset, using the following VB.NET code:

Public Function GetData(ByVal CommandText As String, ByRef ReturnResults As System.Data.DataSet) As Boolean

Try

Dim SQLData As New SQLite.SQLiteDataAdapter(CommandText, m_Connection)

Dim DataSet As New System.Data.DataSet

Call SQLData.Fill(DataSet)

ReturnResults = DataSet

Return True

Catch ex As Exception RaiseEvent ErrorRaised(ex)

End Try

End Function

Anyway, here is the query at the heart of my problem:

SELECT playlists.name as Title, count(playlist_media.mediaid) AS Tracks, playlists.playlistid as TagID, (SELECT count(*) FROM selected_media,playlist_media WHERE selected_media.mediaid = playlist_media.mediaid AND playlists.playlistid = playlist_media.playlistid) AS Selected, case (SELECT count(*) FROM selected_media,playlist_media WHERE selected_media.mediaid = playlist_media.mediaid AND playlists.playlistid = playlist_media.playlistid) WHEN 0 THEN 0 ELSE 1 END AS IsSelected FROM playlists, playlist_media WHERE playlist_media.playlistid=playlists.playlistid GROUP BY playlists.name ORDER BY playlists.playlist_position;

This is used to retrieve a list of 'playlists' to screen showing the associated track count for each playlist. If you run this query in something like SQLite Administrator (http://sqliteadmin.orbmu2k.de/) the data is returned in the correct order, with the correct values.

Absolute Oldskool Classics Volume 4 (Disc 1)

Absolute Oldskool Classics Volume 4 (Disc 2)

Absolute Oldskool Classics Volume 7 (Disc 1)

Absolute Oldskool Classics Volume 7 (Disc 2)

If you run this query via ADO.NET 2.0 Data Provider, the 'Title' column appears in an incorrect order but all other columns contain the correct data.  The resulting incorrect column has the following order:

Drum & Bass Arena (Disc 1)

Drum & Bass Arena (Disc 2)

Drum & Bass Arena (Disc 1)

Prodigy Demo Tape

 If you run a simpler query:

SELECT playlists.name as Title FROM playlists ORDER BY playlist_position;

You get the following (correct) results:

Absolute Oldskool Classics Volume 4 (Disc 1)

Absolute Oldskool Classics Volume 4 (Disc 2)

Absolute Oldskool Classics Volume 7 (Disc 1)

Absolute Oldskool Classics Volume 7 (Disc 2)

This issue is present in version 1.0.48, and also in the latest 1.0.60.  My guess is it could be something to do with the sub-selection.  Any idea on a way round this... or do I need a fix?

**UPDATE**

 Modifying the query to use the outer tables, has no impact, so changing the query to something like this:

SELECT playlists.name as Title, count(playlist_media.mediaid) AS Tracks, playlists.playlistid as TagID, (SELECT count(*) FROM selected_media WHERE selected_media.mediaid = playlist_media.mediaid AND playlists.playlistid = playlist_media.playlistid) AS Selected FROM playlists, playlist_media WHERE playlist_media.playlistid=playlists.playlistid GROUP BY playlists.name ORDER BY playlists.playlist_position;

...still returns the one column in the wrong order.

I cannot replicate this.

Using 1.0.50 running the following c# code while connected to your database, the output will still be the same as when firing against SQLite directly (using SqLiteSpy in my case):

Absolute Oldskool Classics Volume 4 (Disc 1)

Absolute Oldskool Classics Volume 4 (Disc 2)

Absolute Oldskool Classics Volume 7 (Disc 1)

Absolute Oldskool Classics Volume 7 (Disc 2)

.....

cn = new SQLiteConnection( string.Format( "Data Source={0};Synchronous=Normal;Version=3",
    Path.Combine(
        Environment.GetFolderPath( Environment.SpecialFolder.Desktop ),
        "PhatMM.db" ) ) );

cn.Open();

DbDataAdapter da = new SQLiteDataAdapter(
    "SELECT playlists.name as Title, count(playlist_media.mediaid) AS Tracks, playlists.playlistid as TagID, (SELECT count(*) FROM selected_media,playlist_media WHERE selected_media.mediaid = playlist_media.mediaid AND playlists.playlistid = playlist_media.playlistid) AS Selected, case (SELECT count(*) FROM selected_media,playlist_media WHERE selected_media.mediaid = playlist_media.mediaid AND playlists.playlistid = playlist_media.playlistid) WHEN 0 THEN 0 ELSE 1 END AS IsSelected FROM playlists, playlist_media WHERE playlist_media.playlistid=playlists.playlistid GROUP BY playlists.name ORDER BY playlists.playlist_position;",
    cn );

DataSet ds = new DataSet();
da.Fill( ds );

foreach ( DataRow dr in ds.Tables[0].Rows )
{
    Console.WriteLine( dr[0] );
}

May be you are sorting the results inadvertly at the client side? Perhaps you use a dataview or a bindig source?

Nope I checked there was no client sorting, the ListView being populated has no custom sorter or any sorting methods applied to the control either at design time or run-time.

 I also added the console dump after getting the dataset back, and got the same incorrectly ordered results:

 Drum & Bass Arena (Disc 1)

Drum & Bass Arena (Disc 2)

Drum & Bass Arena (Disc 3)

Prodigy Demo Tape

Absolute Oldskool Classics Volume 4 (Disc 1)

Absolute Oldskool Classics Volume 4 (Disc 2)

Absolute Oldskool Classics Volume 7 (Disc 1)

Absolute Oldskool Classics Volume 7 (Disc 2)

DJ's Unite - Volume 1

Essential Old Skool Hardcore (Disc 1)

Essential Old Skool Hardcore (Disc 2)

Hardcore U Know The Score (Disc 1)

Hit The Decks Volume 2

The Max Power Old Skool Collection

Rave Anthems (Disc 1)

Rave Anthems (Disc 2)

Rave Anthems (Disc 3)

Rave Anthems Volume 2 (Disc 1)

Rave Anthems Volume 2 (Disc 2)

Rave Anthems Volume 2 (Disc 3)

The Oldskool Masters: Round 2 (Disc 1)

The Oldskool Masters: Round 2 (Disc 2)

Oldskool Classics - Phantasy

The Prodigy Experience

The Ultimate Rave Classics Volume 1 (Disc 1)

The Ultimate Rave Classics Volume 1 (Disc 2)

The Ultimate Rave Classics Volume 1 (Disc 3)

Raveology (Disc 1)

Raveology (Disc 2)

The Ultimate Hardcore

I Love 19 92

The Best of LeetRadio.coA first chance exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll

m

Regression: Back To 89 to 92 (Disc 1)

Regression: Back To 89 to 92 (Disc 2)

Ravin - 40 Rave Anthems (Disc 1)

Ravin - 40 Rave Anthems (Disc 2)

Extreme Euphoria 4 (Disc 1)

Extreme Euphoria 4 (Disc 2)

Extreme Euphoria 4 (Disc 3)

Hardhouse Anthems Volume 5 (Disc 1)

Hardhouse Anthems Volume 5 (Disc 2)

The Very Best Of Tried & Tested Euphoria (Disc 1)

The Very Best Of Tried & Tested Euphoria (Disc 2)

The Very Best Of Tried & Tested Euphoria (Disc 3)

Hard House Nation [Disc 1]

Hard House Nation [Disc 2]

Keep It Tidy 4 - (Disc 1)

Keep It Tidy 4 (Disc 2)

Keep It Tidy 4 (Disc 3)

Keep It Tidy 4 (Disc 4)

Extreme Euphoria 3 (Disc 1)

Extreme Euphoria 3 (Disc 2)

BK - Extreme Euphoria 5 (Disc 1)

BK - Extreme Euphoria 5 (Disc 2)

Extreme Euphoria 2 (Disc 1)

Extreme Euphoria 2 (Disc 2)

Ministry Of Sound - Hardhouse Classics (Disc 1)

Ministry Of Sound - Hardhouse Classics (Disc 2)

Ministry Of Sound - Hardhouse Classics (Disc 2) 2

Trance Nation America 3 (Disc 1)

Trance Nation America 3 (Disc 2)

Trance Nation Harder (Disc 1)

Trance Nation Harder (Disc 2)

Slinky - World Class (Disc 1)

Slinky - World Class (Disc 2)

Ministry of Sound - The Annual 2002 (Disc 1)

Ministry of Sound - The Annual 2002 (Disc 2)

Trance Nation 4

Ibiza Mix

Lashed Euphoria (Disc 1)

Lashed Euphoria (Disc 2)

Lashed Euphoria (Disc 3)

Trance Nation Electric (Disc 1)

Trance Nation Electric (Disc 2)

Eminem - The Eminem Show

Maximum Bass (Disc 1)

Maximum Bass (Disc 2)

Bass In Yer Face (Disc 1)

Bass In Yer Face (Disc 2)

Various Artists - Max Power Booty Bass (Disc 1)

Various Artists - Max Power Booty Bass (Disc 2)

I also noticed, that if I change the ORDER BY to be use the same column as the GROUP BY, the Title column is in the correct order, but the other columns are in the wrong order!

I've also tried simplifying the query to:

select distinct playlists.name as Title, (select count(pm1.mediaid) from playlist_media pm1 where pm1.playlistid = playlists.playlistid) as Tracks, (select count(*) from selected_media, playlist_media pm2 where selected_media.mediaid = pm2.mediaid and pm2.playlistid=playlists.playlistid) as Selected from playlists;

...and the results are the same :(

 I am having a similar issue. I have created the following table:

CREATE TABLE EXPENSE_CATEGORIES ( 

CATEGORY_ID INTEGER PRIMARY KEY AUTOINCREMENT,

DESCRIPTION VARCHAR(255) NOT NULL, UNIQUE (DESCRIPTION))

and entered the three records: 'atest1', 'btest2', and 'ctest3'.

Then, in C# 2.0 I ran the query:

SELECT CATEGORY_ID, DESCRIPTION FROM EXPENSE_CATEGORIES ORDER BY DESCRIPTION ASC

I obtained the result:

btest2

atest1

ctest3

I then changed the query ORDER BY TO  ORDER BY DESCRIPTION DESC and got the result:

ctest3

atest1

btest2

 

I then changed the order by clause to  ORDER BY DESCRIPTION COLLATE NOCASE and obtained the result:

btest2

ctest3

atest1

I then changed the order by clause to  ORDER BY ORDER BY lower(DESCRIPTION) ASC and obtained the result:

btest2

ctest3

atest1

 ORDER BY upper(DESCRIPTION) DESC

atest1

ctest3

btest2

 ORDER BY lower(DESCRIPTION) DESC

atest1

ctest3

btest2

ORDER BY lower(DESCRIPTION) ASC

btest2

ctest3

atest1

ORDER BY lower(DESCRIPTION) COLLATE NOCASE

btest2

ctest3

atest1

So, I ask, what is the deal? It is a simple select with an order by. However, it does not work at all!!

 

 

 

 

I decided to continue trying other things and what I did was to re-define the table as:

CREATE TABLE EXPENSE_CATEGORIES (
CATEGORY_ID INTEGER PRIMARY KEY AUTOINCREMENT,
DESCRIPTION VARCHAR(255) NOT NULL COLLATE NOCASE,
UNIQUE (DESCRIPTION))

The results were the same. The weird behavior I am reporting is very disturbing to me My confidence  in the product has been shaken and I can no longer rely on SQLITE at this point. I think it is time for me to stop developing with SQLITE and go back to MSDBE.

Try to create like vs says: -- Original table schema CREATE TABLE [Frases2] ( [AutoID] integer PRIMARY KEY AUTOINCREMENT NOT NULL, [Frase] nvarchar(400) NOT NULL COLLATE NOCASE ); CREATE UNIQUE INDEX [IX_Frase] ON [Frases2] ([Frase]); I haven't any problem ordering your 3 words -.- using (SQLiteCommand comm = conn.CreateCommand()) { comm.CommandText = "select frase,autoid from frases2 order by frase asc"; comm.CommandType = CommandType.Text; using (SQLiteDataReader data = comm.ExecuteReader()) { while (data.Read()) { for (int i = 0; i " + data[i]); } } } } Output: Frase -> atest1 AutoID -> 1003 Frase -> btest2 AutoID -> 1001 Frase -> ctest3 AutoID -> 1002

 I replaced the database with MS SQL Express and realized, after the work was done, that I am encrypting the data before inserting into the table. Surely enough, sorting works. I just don't get what I would expect, since the data being sorted is encrypted.

I decided to change the code to use SQLite again. I think I need to have my brain replaced instead of the DB.

 Thanks a bunch for the reply, anyway. In fact, your reply triggered the remaining good neurons in my brain and made me remember about the encryption.

What I will do next is to make the encryption/decryption C# functions part of SQLite and then apply the decryption in the order by clause of the select statement. Hopefully it will work.

 Thanks again,

 

Fernando