Hello All,
We've discovered that collation is no longer evaluated in nested queries.
Collation worked fine with at least SQLite3.DLL 3.6.16.x. After updating to
3.7.7.1 the sorting of some queries does no longer reflect the columns
Collation definition.
The table was created with SQLite 3.6.16:
CREATE TABLE File (
PKFile integer PRIMARY KEY AUTOINCREMENT NOT NULL,
FKFolder integer NOT NULL REFERENCES Folder(PKFolder) ON DELETE CASCADE,
FileName text NOT NULL COLLATE NOCASE,
Extension text NOT NULL COLLATE NOCASE,
FileSize integer NOT NULL,
FileModificationTime timestamp NOT NULL,
FileCreationTime timestamp NOT NULL,
MediaKind text NOT NULL COLLATE NOCASE,
EventName text NOT NULL COLLATE NOCASE,
ItemCreationTime timestamp,
Artist text COLLATE NOCASE,
AlbumArtist text COLLATE NOCASE,
Album text COLLATE NOCASE,
Title text COLLATE NOCASE,
Genre text COLLATE NOCASE,
Duration integer DEFAULT 0,
TrackNumber integer DEFAULT 0,
ProductionYear integer DEFAULT 0,
Width integer DEFAULT 0,
Height integer DEFAULT 0,
IsFavorite bit DEFAULT 0,
Guid text NOT NULL,
RecordAddedTime timestamp NOT NULL,
RecordUpdatedTime timestamp,
IsHidden bit DEFAULT 0,
ScanSource integer DEFAULT 0,
MetaDataExtraction integer DEFAULT 0,
ThumbnailState integer NOT NULL DEFAULT 0,
EditSteps integer NOT NULL DEFAULT 0,
ErrorState integer NOT NULL DEFAULT 0,
AspectRatio real NOT NULL DEFAULT 0.0,
DiskNumber integer DEFAULT 0,
HashValueAtImport text COLLATE NOCASE);
A simple query like this works fine with both versions, the result is sorted
NOCASE by the Album column:
SELECT File.Album, File.Artist, Drive.DriveName, Folder.FolderName,
File.FileName, File.Extension
FROM File
INNER JOIN Folder ON Folder.PKFolder = File.FKFolder
INNER JOIN Drive ON Drive.PKDrive = Folder.FKDrive
GROUP BY File.Album
However the same thing as a more complex, nested query (generated by SQLite.Net
from a LINQ query) is evaluated different by both versions. With the older
SQLite version NOCASE is evaluated correctly, but with the newer version
sorting is case sensitive:
SELECT
[Project1].[C6] AS [C1],
[Project1].[Album] AS [Album],
[Project1].[Artist] AS [Artist],
[Project1].[AlbumArtist] AS [AlbumArtist],
[Project1].[FileCreationTime] AS [FileCreationTime],
[Project1].[FileModificationTime] AS [FileModificationTime],
[Project1].[C7] AS [C2],
[Project1].[RecordAddedTime] AS [RecordAddedTime],
[Project1].[DriveName] AS [DriveName],
[Project1].[C8] AS [C3],
[Project1].[C9] AS [C4],
[Project1].[C10] AS [C5],
[Project1].[C11] AS [C6],
[Project1].[C12] AS [C7],
[Project1].[C13] AS [C8],
[Project1].[EventName] AS [EventName],
[Project1].[Extension] AS [Extension],
[Project1].[PKFile] AS [PKFile],
[Project1].[FileName] AS [FileName],
[Project1].[FileSize] AS [FileSize],
[Project1].[FolderName] AS [FolderName],
[Project1].[Genre] AS [Genre],
[Project1].[Guid] AS [Guid],
[Project1].[C14] AS [C9],
[Project1].[C15] AS [C10],
[Project1].[MediaKind] AS [MediaKind],
[Project1].[C2] AS [C11],
[Project1].[Title] AS [Title],
[Project1].[C16] AS [C12],
[Project1].[C17] AS [C13],
[Project1].[C3] AS [C14],
[Project1].[AspectRatio] AS [AspectRatio],
[Project1].[IsSlowDevice] AS [IsSlowDevice],
[Project1].[C4] AS [C15],
[Project1].[MediaObjectFolderId] AS [MediaObjectFolderId],
[Project1].[C5] AS [C16]
FROM ( SELECT
CASE WHEN (([Filter1].[Title] IS NULL) OR ((LENGTH([Filter1].[Title])) =
0)) THEN [Filter1].[FileName] ELSE [Filter1].[Title] END AS [C1],
[Filter1].[PKFile] AS [PKFile],
[Filter1].[FileName] AS [FileName],
[Filter1].[Extension] AS [Extension],
[Filter1].[FileSize] AS [FileSize],
[Filter1].[FileModificationTime] AS [FileModificationTime],
[Filter1].[FileCreationTime] AS [FileCreationTime],
[Filter1].[MediaKind] AS [MediaKind],
[Filter1].[EventName] AS [EventName],
[Filter1].[Artist] AS [Artist],
[Filter1].[AlbumArtist] AS [AlbumArtist],
[Filter1].[Album] AS [Album],
[Filter1].[Title] AS [Title],
[Filter1].[Genre] AS [Genre],
[Filter1].[Guid] AS [Guid],
[Filter1].[RecordAddedTime] AS [RecordAddedTime],
[Filter1].[AspectRatio] AS [AspectRatio],
[Filter1].[ThumbnailState] AS [C2],
[Filter1].[ErrorState] AS [C3],
[Filter1].[PostProcessingRotation] AS [C4],
[Filter1].[Rating] AS [C5],
[Filter1].[FolderName] AS [FolderName],
[Filter1].[MediaObjectFolderId] AS [MediaObjectFolderId],
[Extent3].[DriveName] AS [DriveName],
[Extent3].[IsSlowDevice] AS [IsSlowDevice],
1 AS [C6],
CASE WHEN ([Filter1].[ItemCreationTime] IS NULL) THEN
[Filter1].[FileCreationTime] ELSE [Filter1].[ItemCreationTime] END AS [C7],
CASE WHEN ([Filter1].[Duration] IS NULL) THEN 0 ELSE
[Filter1].[Duration] END AS [C8],
CASE WHEN ([Filter1].[TrackNumber] IS NULL) THEN 0 ELSE
[Filter1].[TrackNumber] END AS [C9],
CASE WHEN ([Filter1].[DiskNumber] IS NULL) THEN 0 ELSE
[Filter1].[DiskNumber] END AS [C10],
CASE WHEN ([Filter1].[ProductionYear] IS NULL) THEN 0 ELSE
[Filter1].[ProductionYear] END AS [C11],
CASE WHEN ([Filter1].[Width] IS NULL) THEN 0 ELSE [Filter1].[Width] END
AS [C12],
CASE WHEN ([Filter1].[Height] IS NULL) THEN 0 ELSE [Filter1].[Height]
END AS [C13],
CASE WHEN ([Filter1].[IsFavorite] IS NULL) THEN 0 ELSE
[Filter1].[IsFavorite] END AS [C14],
CASE WHEN ([Filter1].[IsFavorite] IS NULL) THEN 0 ELSE
[Filter1].[IsFavorite] END AS [C15],
CASE WHEN ([Extent3].[IsConnected] <> 1) THEN 1 WHEN
([Extent3].[IsConnected] = 1) THEN 0 END AS [C16],
CASE WHEN ([Filter1].[IsHidden] IS NULL) THEN 0 ELSE
[Filter1].[IsHidden] END AS [C17]
FROM (SELECT [Extent1].[PKFile] AS [PKFile], [Extent1].[FKFolder] AS
[FKFolder], [Extent1].[FileName] AS [FileName], [Extent1].[Extension] AS
[Extension], [Extent1].[FileSize] AS [FileSize],
[Extent1].[FileModificationTime] AS [FileModificationTime],
[Extent1].[FileCreationTime] AS [FileCreationTime], [Extent1].[MediaKind] AS
[MediaKind], [Extent1].[EventName] AS [EventName], [Extent1].[ItemCreationTime]
AS [ItemCreationTime], [Extent1].[Artist] AS [Artist], [Extent1].[AlbumArtist]
AS [AlbumArtist], [Extent1].[Album] AS [Album], [Extent1].[Title] AS [Title],
[Extent1].[Genre] AS [Genre], [Extent1].[Duration] AS [Duration],
[Extent1].[TrackNumber] AS [TrackNumber], [Extent1].[ProductionYear] AS
[ProductionYear], [Extent1].[Width] AS [Width], [Extent1].[Height] AS [Height],
[Extent1].[IsFavorite] AS [IsFavorite], [Extent1].[Guid] AS [Guid],
[Extent1].[RecordAddedTime] AS [RecordAddedTime], [Extent1].[RecordUpdatedTime]
AS [RecordUpdatedTime], [Extent1].[IsHidden] AS [IsHidden],
[Extent1].[ScanSource] AS [ScanSource], [Extent1].[MetaDataExtraction] AS
[MetaDataExtraction], [Extent1].[ThumbnailState] AS [ThumbnailState],
[Extent1].[EditSteps] AS [EditSteps], [Extent1].[ErrorState] AS [ErrorState],
[Extent1].[AspectRatio] AS [AspectRatio], [Extent1].[DiskNumber] AS
[DiskNumber], [Extent1].[HashValueAtImport] AS [HashValueAtImport],
[Extent1].[PostProcessingRotation] AS [PostProcessingRotation],
[Extent1].[Rating] AS [Rating], [Extent2].[PKFolder] AS [PKFolder],
[Extent2].[FKDrive] AS [FKDrive], [Extent2].[FolderName] AS [FolderName],
[Extent2].[MediaObjectFolderId] AS [MediaObjectFolderId]
FROM [File] AS [Extent1]
INNER JOIN [Folder] AS [Extent2] ON [Extent1].[FKFolder] =
[Extent2].[PKFolder]
WHERE ('Music' = [Extent1].[MediaKind]) AND (0 =
[Extent1].[IsHidden]) ) AS [Filter1]
INNER JOIN [Drive] AS [Extent3] ON [Filter1].[FKDrive] =
[Extent3].[PKDrive]
) AS [Project1]
GROUP BY [Project1].[Album]
The database to reproduce this is attached.
Mit freundlichen Grüßen / Best Regards,
Andreas J. Wicker
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
