Odd SQL Problem

[Ivan Smahin]

Hello Beatrix,

Wednesday, February 18, 2009, 8:45:46 PM, you wrote:

> Working with VStudio (Mac, 4RC1) I have an odd SQL problem.

> The table called "Mailbox" has a field called "MailboxPath". I want  
> to sort it with a custom sort function. Values starting with Inbox  
> come first etc. The result should like the following:

> MailboxPath                     Value for sorting
> Mailbox:Something       4:Mailbox:Something
> Trash                           3:Trash
> Inbox                           1:Inbox
> Inbox:Account           1:Inbox:Account

> The following SQL works okay:

> select MailboxPath,
> case
>                 when list_nthitem(MailboxPath, 1, ':') = 'Inbox' then
>                         concat('1:', MailboxPath)
>                 when list_nthitem(MailboxPath, 1, ':') = 'Sent' then
>                         concat('2:', MailboxPath)
>                 when mailboxPath = 'Trash' then
>                         concat('3:', MailboxPath)
>                 else
>                         concat('4:', MailboxPath)
>         end
> from mailbox

> Now if I create a function

> create or replace function PathSort(thePath string)
> returns string
> begin
>         case
>                 when list_nthitem(thePath, 1, ':') = 'Inbox' then
>                         return concat('1:', thePath);
>                 when list_nthitem(thePath, 1, ':') = 'Sent' then
>                         return concat('2:', thePath);
>                 when thePath = 'Trash' then
>                         return concat('3:', thePath);
>                 else
>                         return concat('4:', thePath);
>         end case
> end

> and test it with

> select mailboxpath, PathSort(MailboxPath) from Mailbox order by 2

> then I get the following result:

> MailboxPath                     Value for sorting
> Mailbox:Something       4:Mailbox:Something
> Trash                           3:Trash
> Inbox                           4:Inbox
> Inbox:Account           4:Inbox:Account

> Am I missing something?
>                 

There is known bug with UDF (user defined function) returned string.
Probably it is related to it.
Could you report it to the Mantis and attach (or send me) simple db
for testing?


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