DISTINCT not working - varchar

[Ivan Smahin]

Hello Kay,

Monday, June 21, 2010, 8:50:19 AM, you wrote:

> Hi Ruslan,

> No it is not. I clicked the box to test it but it came up with a
> confirmation message, suggesting that I probably should really
> understand what I'm about to do - which I don't.

> I did a quick search of the Wiki but didn't learn much. If
> IndexedByWords has to be checked what are the other consequences -
> why would I, why wouldn't I? What constitutes a word - my varchar
> fields can contain things like AMS#deg°C, or maybe a URL with all sorts of funny characters.

> I could extract a distinct list by other means if I had to.

> DISTINCT just worked in mySQL and postgreSQL so curious to learn
> why Valentina my handle it differently.

> Thanks again

> On Mon, Jun 21, 2010 at 1:15 AM, Ruslan Zasukhin
> <ruslan_zasukhin at valentina-db.com> wrote:
> On 6/20/10 5:13 PM, "Kay C Lan" <lan.kc.macmail at gmail.com> wrote:

> Hi Kay,

> Is field marked as IndexedByWords?


>> simple SQL query:
>>
>> SELECT DISTINCT category FROM products
>>
>> returns every single row, all category returned multiple times.
>>
>> Tested in VStudio 4.5 and same result.
>>
>> Searching the List I see Christian reported getting Errors 1 year ago when
>> doing DISTINCT on varchar fields. My  field is varchar but I don't get an
>> error I just get exactly the same result as if I'd done:
>>
>> SELECT category FROM products
>>
>> Did a quick test on unsigned short and date field and got correct results.
>>
>> Is there some trick to doing DISTINCT on varchar fields?

Well,  your  db storage is set to UTF-8. It is not officially supported
yet. There are a lot of issues like you trap in. You should use UTF-16
only.


Steps to convert your db:

1. Make a SQL-dump of your db.
2. Edit result  - it should be

SET PROPERTY
        [Mode], 
        [IdentsCaseSensitive], 
        [SegmentSize], 
        [SchemaVersion], 
        [DateTimeFormat], 
        [DateSeparator], 
        [TimeSeparator], 
        [LocaleName], 
        [StorageEncoding], 
        [FrenchCollation], 
        [AlternateHandling], 
        [CaseFirst], 
        [CaseLevel], 
        [NormalizationMode], 
        [Strength], 
        [HiraganaQuaternaryMode], 
        [NumericCollation] 
of DATABASE  TO 
        'kDsc_Dat_Blb_Ind', 
        false, 
        32768, 
        1, 
        'kDMY', 
        '.', 
        ':', 
        'en_US_POSIX', 
        'UTF-16', 
        'kOff', 
        'kNonIgnorable', 
        'kOff', 
        'kOff', 
        'kOff', 
        'kTertiary', 
        'kOff', 
        'kOff';


instead of

SET PROPERTY
        [Mode], 
        [IdentsCaseSensitive], 
        [SegmentSize], 
        [SchemaVersion], 
        [DateTimeFormat], 
        [DateSeparator], 
        [TimeSeparator], 
        [LocaleName], 
        [StorageEncoding], 
        [FrenchCollation], 
        [AlternateHandling], 
        [CaseFirst], 
        [CaseLevel], 
        [NormalizationMode], 
        [Strength], 
        [HiraganaQuaternaryMode], 
        [NumericCollation] 
of DATABASE  TO 
        'kDsc_Dat_Blb_Ind', 
        false, 
        32768, 
        1, 
        'kDMY', 
        '.', 
        ':', 
        'en_US_POSIX', 
        'UTF-8',
        'kOff', 
        'kNonIgnorable', 
        'kOff', 
        'kOff', 
        'kOff', 
        'kTertiary', 
        'kOff', 
        'kOff';


  3. Load this dump


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com