Invalid cast executing query with "LIKE"

[Former Member]

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>

[Breck_Carter]

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'.

[VolkerBarth]

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?

[Breck_Carter]

Try CAST...

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

[VolkerBarth]

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 [...]

[Breck_Carter]

Well, something is necessary 🙂

[chris_keating]

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.

[Breck_Carter]

im in ur base killin ur d00dz!

[Former Member]

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

Notwithstanding the scary King Kong picture 🙂

[Breck_Carter]

[Former Member]

Thanks for the solution.