Invalid cast executing query with "LIKE"

Hello,

I'm experiencing a unexpected result when executing a query statement in ultralite.

The problem is that the interactive sql is throwing the error "[UltraLite Database] Cannot convert 16409700 to a varchar SQLCODE=-157, ODBC 3 State="07006" when I execute the following command with a specific dataset in my database:

SELECT p.u
from tb1 p 
where p.u like '%16409700%'

To reproduce the error use the following script:

insert into tb1
values(1,'a','b','c',4,1,17,2,4,17,'','','','','d','e',12,'f','g',5,16409700,'','h')

SELECT p.u
from tb1 p 
where p.u like '%16409700%'

//ERROR - [UltraLite Database] Cannot convert 16409700 to a varchar
//SQLCODE=-157, ODBC 3 State="07006"

// clear all
truncate table tb1

insert into tb1
values(2,'a','b','c',4,1,17,2,4,17,'','','','','d','e',12,'f','g',5,1,'','h')

SELECT p.u
from tb1 p 
where p.u like '%1%'

// OK - no error

Database schema:

<?xml version="1.0" encoding="utf-8" standalone="no"?>
<ul:ulschema xmlns:ul="urn:ultralite">
    <collation name="1252LATIN1" case_sensitive="no" utf8="yes"/>
    <tables>
        <table name="tb1" sync="changes">
            <columns>
                <column name="a" type="integer" null="no"/>
                <column name="b" type="varchar(40)" null="yes"/>
                <column name="c" type="varchar(80)" null="yes"/>
                <column name="d" type="varchar(240)" null="yes"/>
                <column name="e" type="smallint" null="yes"/>
                <column name="f" type="smallint" null="yes"/>
                <column name="g" type="integer" null="yes"/>
                <column name="h" type="integer" null="yes"/>
                <column name="i" type="integer" null="yes"/>
                <column name="j" type="integer" null="yes"/>
                <column name="k" type="integer" null="yes"/>
                <column name="l" type="integer" null="yes"/>
                <column name="m" type="integer" null="yes"/>
                <column name="n" type="integer" null="yes"/>
                <column name="o" type="varchar(100)" null="yes"/>
                <column name="p" type="varchar(100)" null="yes"/>
                <column name="q" type="smallint" null="yes"/>
                <column name="r" type="varchar(10)" null="yes"/>
                <column name="s" type="varchar(10)" null="yes"/>
                <column name="t" type="smallint" null="yes"/>
                <column name="u" type="integer" null="yes"/>
                <column name="v" type="integer" null="yes"/>
                <column name="x" type="long varchar" null="yes"/>
            </columns>
            <primarykey>
                <primarycolumn name="a" direction="asc"/>
            </primarykey>
            <indexes/>
        </table>
  </tables>
</ul:ulschema>

asked 18 Feb '13, 07:59

Gabriel Fran...
31●2●2●3
accept rate: 0%

edited 19 Feb '13, 11:02

Graham Hurst
2.7k●1●18●43

Are you sure it isn't the INSERT that is causing the problem?

Is tb1.u the third-last column in tb1, and if so, is it declared as VARCHAR with a maximum length less than 8? If so, then 16409700 won't fit after conversion to '16409700'.

(18 Feb '13, 09:03) Breck Carter

Replies hidden

Hmm, according to the table declaration, column "u" is of type integer, which should surely accept 16409700 as value.

Therefore I guess the problem may be due to a conversion error of the LIKE predicate's operands.

@Gabriel: What build number are you using?

(18 Feb '13, 09:44) Volker Barth

Try CAST...

SELECT p.u
from tb1 p 
where CAST ( p.u AS VARCHAR ( 20 ) ) like '%16409700%'

(18 Feb '13, 10:17) Breck Carter

Replies hidden

Well, for SQL Anywhere queries (not Ultralite), a cast should not be necessary, as the docs state tat both operands are converted to CHAR/NCHAR automatically for LIKE search conditions:

expression [ NOT ] LIKE pattern [ ESCAPE escape-character ]

expression is interpreted as a CHAR or NCHAR string. [...] Similarly, pattern is interpreted as a CHAR or NCHAR string [...]

(18 Feb '13, 10:26) Volker Barth

Well, something is necessary :)

(18 Feb '13, 11:13) Breck Carter

There is an issue with UL in handling the implicit cast. As a workaround, cast the value as suggested by Breck will workaround this issue.

(22 Feb '13, 13:24) Chris Keating

Comment Text Removed

im in ur base killin ur d00dz!

alt text

(22 Feb '13, 15:50) Breck Carter

showing 3 of 7 show all flat view

One Answer:

active answers oldest answers newest answers popular answers

This is fixed as engineering case CR 734471, SQL Anywhere version 12.0.1.3870 or later.

Notwithstanding the scary King Kong picture :-)

permanent link

answered 11 Mar '13, 16:16

Andy Quick
2.2k●27●37
accept rate: 45%

Comment Text Removed

Boba

(11 Mar '13, 17:06) Breck Carter

Thanks for the solution.

(12 Mar '13, 07:28) Gabriel Fran...

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

ultralite ×160
select ×66
bug ×43
error-message ×43
like ×6

question asked: 18 Feb '13, 07:59

question was seen: 5,846 times

last updated: 12 Mar '13, 07:47

SAP SQL Anywhere

Invalid cast executing query with "LIKE"

Follow this question

Related questions