I have a query that causes a crash in Ultralite, both in the iPad and Interactive SQL. The crash began to happen after I installed EBF 3798. Before that, the query was executed without error.

The original query is somewhat complex, but I succeeded in devising a simple query that causes the crash as well.

The query is:

select * from (
    select 
    A.id as i,
       case A.num 
            when 0 then 'a'
            when 1 then 'b'
            when 2 then 'c'
            else 'd'
       end as v,
       v + ' - ' + A.num as s
    from testTable A
) as X(i, v, s)
where i = 1

And the Ultralite database has only one table:

create table testTable
(
     id int primary key,
     num int,
     descr varchar(100)
)

The crash occurs when executing ULConnection's PrepareStatement method. And the table does not need to have any data in it.

If I remove the enclosing select, the problem does not happen again. Also, if remove either column v (the one that contains the case statement) or column s, that concatenates the preceding field with another field from the table, the problem does not occur any longer.

Those solutions are not acceptable, because the outer query is generated at runtime, in other to add extra filters to an existing query.

asked 29 Oct '12, 15:39

Antonio%20Martins's gravatar image

Antonio Martins
216229
accept rate: 0%


Hi Antonio,

Thank you for posting this concise repro. I confirm that the crash was fixed in 12.0.1.3803. There was a further bug that your query exposed, and that is now fixed with 12.0.1.3812. I have requested an EBF be released.

And thank you Breck for correcting the query. Indeed, UL does (now) report -157 (conversion error) for 'b -' -> numeric with the original query.

permanent link

answered 01 Nov '12, 13:33

Tim%20McClements's gravatar image

Tim McClements
2.0k1830
accept rate: 35%

edited 01 Nov '12, 17:42

Hi Tim,

Do you have any news about the EBF?

(13 Nov '12, 09:24) Antonio Martins
Replies hidden

Hello Antonio,

This issue has been reported and fixed underneath CR #723102. The Windows / Mac OS 12.0.1 EBFs have been requested for this issue and are currently being built and tested internally.

If you require an immediate update for when the EBF is released (or a "pre-release" copy for your own testing purposes), we invite you to open a technical support case so that we can discuss this with you directly and properly address your individual business needs.

(13 Nov '12, 15:13) Jeff Albion
1

This should be posted now (build 3819), and also includes proper iPhone 5/Xcode 4.5 support.

(12 Dec '12, 13:30) Tim McClements

I believe this is an instance of CR #717377 fixed in 12.0.1.3803.

permanent link

answered 29 Oct '12, 17:56

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175
accept rate: 25%

1

I haven't found this version in the EBF list. Is it available for download? If not, when will it be?

Thanks.

(30 Oct '12, 17:48) Antonio Martins

That query shouldn't ever work because of the ambiguous use of the "+" operator (is it numeric addition? is it string concatenation?), and it does fail in in SQL Anywhere 12.0.1.3298 Classic (non-UltraLite) as follows:

create table testTable
(
     id int primary key,
     num int,
     descr varchar(100)
);
INSERT testTable VALUES ( 1, 1, 'x' );
COMMIT;
select * from (
    select 
    A.id as i,
       case A.num 
            when 0 then 'a'
            when 1 then 'b'
            when 2 then 'c'
            else 'd'
       end as v,
       v + ' - ' + A.num as s
    from testTable A
) as X(i, v, s)
where i = 1;

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot convert 'b - ' to a numeric
SQLCODE=-157, ODBC 3 State="07006"

Try using STRING() instead of "+"; this query works:

select * from (
    select 
    A.id as i,
       case A.num 
            when 0 then 'a'
            when 1 then 'b'
            when 2 then 'c'
            else 'd'
       end as v,
       STRING ( v, ' - ', A.num ) as s
    from testTable A
) as X(i, v, s)
where i = 1;

i,v,s
1,'b','b - 1'
permanent link

answered 29 Oct '12, 16:48

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 29 Oct '12, 16:49

I changed the query to use STRING instead of "+", but it still crashes on iPad and in InteractiveSQL for Mac OS.

Also, I changed the query to

select *
from (
select 
   A.id as i,
   (case A.num 
        when 0 then 'a'
        when 1 then 'b'
        when 2 then 'c'
        else 'd'
   end) as v,
   v + ' - ' + A.descr as s
from testTable A
) as X(i, v, s)

where i = 1

The crash always happens, and the first time when there was an ambiguity, Ultralite did not return any error. It simply crashes.

permanent link

answered 29 Oct '12, 17:21

Antonio%20Martins's gravatar image

Antonio Martins
216229
accept rate: 0%

edited 29 Oct '12, 17:22

1

I can confirm the crash also happens to me using the above query. I'm also using the 3798 EBF, as far as I know it is the latest Ultralite EBF for iOS, or there is any more recent?

(30 Oct '12, 12:51) André Freitas
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×162
×51
×13

question asked: 29 Oct '12, 15:39

question was seen: 3,275 times

last updated: 12 Dec '12, 13:30