IDS.2000
A user moving from IDS 7.31 to 9.21 found that passing an NVARCHAR column to a stored procedure declared with a CHAR(30) parameter failed with SQL -674 (routine cannot be resolved), while NCHAR and all other built-in types worked via implicit casts. Replies explained there is no built-in cast from NVARCHAR to CHAR, and you can't create casts between built-in types; suggested workarounds were declaring the routine with LVARCHAR parameters (or overloaded versions), which the poster rejected as too invasive for 200,000 lines of code. Paul Brown argued the real issue is that CHAR/VARCHAR use code-set collation while NCHAR/NVARCHAR use localized collation, so such implicit conversion is unsafe and arguably shouldn't have worked in 7.x. No satisfactory fix was recorded beyond rewriting the code or contacting tech support.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Server: IDS.2000 9.21.TC1-1
Script:
CREATE TABLE my_table (
a CHAR,
b SMALLINT,
c INTEGER,
d FLOAT,
e SMALLFLOAT,
f DECIMAL,
g SERIAL,
h DATE,
i MONEY,
j DATETIME YEAR TO FRACTION,
k VARCHAR,
l NCHAR,
m NVARCHAR
);
INSERT INTO my_table VALUES ( 'A', 0, 0, 0, 0, 0, 0, TODAY, 0, TODAY,'A', 'A', 'A' );
CREATE PROCEDURE my_proc( p CHAR( 30 ) ) RETURNING CHAR( 30 ); RETURN p;
END PROCEDURE;
SELECT my_proc( a ) FROM my_table;
SELECT my_proc( b ) FROM my_table;
SELECT my_proc( c ) FROM my_table;
SELECT my_proc( d ) FROM my_table;
SELECT my_proc( e ) FROM my_table;
SELECT my_proc( f ) FROM my_table;
SELECT my_proc( g ) FROM my_table;
SELECT my_proc( h ) FROM my_table;
SELECT my_proc( i ) FROM my_table;
SELECT my_proc( j ) FROM my_table;
SELECT my_proc( k ) FROM my_table;
SELECT my_proc( l ) FROM my_table;
-- All this statements works fine in both versions ( 7.31 and 9.21 )
-- Selecting last field
SELECT my_proc( m ) FROM my_table;
-- OOPS! SQL Error (-674) : Routine (my_proc) can not be resolved.
Any comments?
Leonid.
Leonids.Voroncovs@dati.lv wrote: : Server: IDS.2000 9.21.TC1-1 : SELECT my_proc( l ) FROM my_table; : -- All this statements works fine in both versions ( 7.31 and 9.21 ) An implicit cast must have been performed. : -- Selecting last field : SELECT my_proc( m ) FROM my_table; : -- OOPS! SQL Error (-674) : Routine (my_proc) can not be resolved. I am surprised l would work, an implicit cast from any native type to a char does not really make sense (two byte chars being the main problem) but may be forcible. : Any comments? If you don't like this, you could play with dropping the casts in the 9.2 engine (for that database). : Leonid. -- Rob Wilson rwilson@ntsource.com
Leonids.Voroncovs@dati.lv wrote:
> Server: IDS.2000 9.21.TC1-1
> Script:
> CREATE TABLE my_table (>
[ snip ]
> m NVARCHAR
> );
> INSERT INTO my_table VALUES ( 'A', 0, 0, 0, 0, 0, 0, TODAY, 0, TODAY,> 'A', 'A', 'A' );
> CREATE PROCEDURE my_proc( p CHAR( 30 ) ) RETURNING CHAR( 30 );> RETURN p;
> END PROCEDURE;
>
> SELECT my_proc( m ) FROM my_table;
> -- OOPS! SQL Error (-674) : Routine (my_proc) can not be resolved.
No CAST between NVARCHAR and CHAR. All of the other built-in types in
your example can be cast to CHAR(). This works in 9.X (although it won't
work in 7.X, as the FUNCTION syntax is new.)
CREATE TABLE my_table (
a CHAR,
b SMALLINT,
c INTEGER,
d FLOAT,
e SMALLFLOAT,
f DECIMAL,
g SERIAL,
h DATE,
i MONEY,
j DATETIME YEAR TO FRACTION,
k VARCHAR,
l NCHAR,
m NVARCHAR
);
INSERT INTO my_table VALUES ( 'A', 0, 0, 0, 0, 0, 0, TODAY, 0, TODAY,'A', 'A', 'A' );
CREATE FUNCTION my_proc( p LVARCHAR)
RETURNING LVARCHAR
RETURN p;END FUNCTION;
SELECT my_proc( a ) FROM my_table;
SELECT my_proc( b ) FROM my_table;
SELECT my_proc( c ) FROM my_table;
SELECT my_proc( d ) FROM my_table;
SELECT my_proc( e ) FROM my_table;
SELECT my_proc( f ) FROM my_table;
SELECT my_proc( g ) FROM my_table;
SELECT my_proc( h ) FROM my_table;
SELECT my_proc( i ) FROM my_table;
SELECT my_proc( j ) FROM my_table;
SELECT my_proc( k ) FROM my_table;
SELECT my_proc( l ) FROM my_table;
SELECT my_proc( m ) FROM my_table;
Rob Wilson wrote: > If you don't like this, you could play with dropping the casts in the > 9.2 engine (for that database). Can You be more detailed? What do You mean by "dropping casts"? > Rob Wilson Leonid Vorontsov
Paul Brown wrote:
> No CAST between NVARCHAR and CHAR. All of the other built-in types in
> your example can be cast to CHAR().
Yes, I see. But why? This is a bug I think.
> CREATE FUNCTION my_proc( p LVARCHAR)
> RETURNING LVARCHAR
Yes, good idea. But there is a problem.
We are running production system with 200,000 lines of source code.
Who can check all of them and do this changes?
Leonids.Voroncovs@dati.lv wrote:
: Paul Brown wrote:
: > No CAST between NVARCHAR and CHAR. All of the other built-in types in
: > your example can be cast to CHAR().
: Yes, I see. But why? This is a bug I think.
A bug is something that causes unexpected behavior. This is expected, if
there is no cast defined from one type of data to another I would not
expect a cast to be performed. You can either create a cast for
nvarchar to char (RTFM for syntax) or
CREATE FUNCTION my_proc (p LVARCHAR)
RETURNING CHAR; return p::CHAR(30);
END FUNCTION;
CREATE FUNCTION my_proc (p CHAR(30))
RETURNING CHAR; return p;
END FUNCTION;
Since the signatures are different the stored function that can be cast
to will run and both return a CHAR(30). I would still rather use the
user defined cast myself since you may not know which of the above
functions will be called when something can be cast to either type.
: > CREATE FUNCTION my_proc( p LVARCHAR)
: > RETURNING LVARCHAR
: Yes, good idea. But there is a problem.
: We are running production system with 200,000 lines of source code.
: Who can check all of them and do this changes?
--
Rob Wilson
rwilson@ntsource.com
Rob Wilson wrote:
> A bug is something that causes unexpected behavior. This is expected,
1. There is implicit cast from NCHAR to CHAR,
but there isn't cast from NVARCHAR to CHAR.
This behavior You call expected?
Where may be a problem to cast NCHAR but do not cast NVARCHAR?
2. The casting mentioned works fine with 7.3x,
but doesn't works with 9.2x.
This behavior You call expected?
Where is promised 100% compatibility?
> You can either create a cast for
> nvarchar to char (RTFM for syntax) or
You can't create casting from built-in type to build-in type.
You should RTFM first.
> CREATE FUNCTION my_proc (p LVARCHAR)
This means big redeveloping as mentioned in previous message.
> Rob Wilson
Leonid Vorontsov
Leonids.Voroncovs@dati.lv wrote: : Rob Wilson wrote: : > A bug is something that causes unexpected behavior. This is expected, : 1. There is implicit cast from NCHAR to CHAR, : but there isn't cast from NVARCHAR to CHAR. : This behavior You call expected? Only that without a cast defined none is performed. : Where may be a problem to cast NCHAR but do not cast NVARCHAR? No idea. : 2. The casting mentioned works fine with 7.3x, : but doesn't works with 9.2x. : This behavior You call expected? Expected may be a bit harsh, I find it unsurprising. I think 7.3x just tried to cast as it could and I would not be surprised if this only worked based on a translation file existing for the database locale to C. : Where is promised 100% compatibility? Agreed. Treat it like a bug. (Everyone loves marketing promises) : > You can either create a cast for : > nvarchar to char (RTFM for syntax) or : You can't create casting from built-in type to build-in type. : You should RTFM first. I should have, yes. : > CREATE FUNCTION my_proc (p LVARCHAR) : This means big redeveloping as mentioned in previous message. It may be the only work around, call tech support and see if they have better ideas. (Or maybe someone around here does.) : > Rob Wilson : Leonid Vorontsov -- Rob Wilson rwilson@ntsource.com (foot removed, have a nice day.)
Leonids.Voroncovs@dati.lv wrote:
> Any comments?
OK. This intrigued me. I had a poke around. The resolution is quite
complex:
1. I don't think you should *ever* be able to cast between VARCHAR/CHAR
and NVARCHAR/NCHAR. Strictly speaking, NVARCHAR/NCHAR support "localized
collation", while VARCHAR/CHAR support "code-set collation"[1]. Code-set
collation uses the "bit-pattern order of characters within a code-set",
while localized order "refers to the order of the characters that relate
to a real language."[2]
2. What this means is that when you take an NCHAR instance, and try to
find, for example, all rows in another table where a CHAR column contains
a value before the corresponding value in the first table, you may get
different results depending on the join-order . It won't affect =, but it
will certainly affect BETWEEN, >, <, <=, and >=.
(Apologies for my Russian.)
CREATE TABLE Customers (
Id SERIAL PRIMARY KEY,
Name VARCHAR(32) NOT NULL
);
CREATE TABLE ÚÁËÁÚÞÉË (
Id SERIAL PRIMARY KEY,
ÆÁÍÉÌÉÑ NVARCHAR(32) NOT NULL
);
SELECT COUNT(*)
FROM Customers C1, Customers C2, ÚÁËÁÚÞÉË Ú
WHERE Ú.ÆÁÍÉÌÉÑ BETWEEN C1.Name AND C2.Name
AND C1.Id = 101 AND C2.Id = 102;
Now, depending on how this query is processed (join order) you would
expect that the engine will either turn the NVARCHAR() into VARCHAR(), or
the VARCHAR() into NVARCHAR().But if it does this, and the sort order is
different, then this query may return a different answer.
So there would appear to be at least two "problems" here. One was that
it worked at all in the 7.X case. The other is that it partiall works in
the 9.X case. So long as you happen to be working in a language where the
code-set collation and the localized collation are the same, then I think
you're not going to see a difference. But as soon as you go to other
languages -- and this is the general case that NCHAR and NVARCHAR must
support -- the query may begin to return inconsistent answers.
And that's not good, in anyone's language.
3. As an aside, things are pretty wierd with plain old VARCHAR() and
CHAR(), too. Running the following script is left as an exercise for the
(by now not numbed to sleep) reader: (Needs 9.X)
--
-- Just in case.
--
DROP TABLE Foo;--
CREATE TABLE Foo (
Id INTEGER NOT NULL,
Val_One CHAR(10) NOT NULL,
Val_Two VARCHAR(10) NOT NULL
);--
INSERT INTO Foo
SELECT Id, Val_One, Val_Two
FROM TABLE(SET{ROW(1, 'Paul','Paul'),
ROW(2, 'Paul ', 'Paul '),
ROW(3, 'Paul ','Paul ')
}::SET(ROW(Id INTEGER,
Val_One CHAR(10),
Val_Two VARCHAR(10) ) NOT NULL)
);--
SELECT Id, ']' || Val_One || '[', Val_One AS UP FROM Foo ORDER BY 3 ASC;
SELECT Id, ']' || Val_One || '[', Val_One AS DOWN FROM Foo ORDER BY 3DESC;
--
SELECT Id, ']' || Val_Two || '[', Val_Two AS UP FROM Foo ORDER BY 3 ASC;
SELECT Id, ']' || Val_Two || '[', Val_Two AS DOWN FROM Foo ORDER BY 3DESC;
--
INSERT INTO Foo
SELECT Id, Val_One, Val_Two
FROM TABLE(SET{
ROW(4, ' Paul',' Paul'),
ROW(5, ' Paul ',' Paul')
}::SET(ROW(Id INTEGER,
Val_One CHAR(10),
Val_Two VARCHAR(10) ) NOT NULL)
);--
SELECT Id, ']' || Val_One || '[', Val_One AS UP FROM Foo ORDER BY 3 ASC;
SELECT Id, ']' || Val_One || '[', Val_One AS DOWN FROM Foo ORDER BY 3DESC;
--
SELECT Id, ']' || Val_Two || '[', Val_Two AS UP FROM Foo ORDER BY 3 ASC;
SELECT Id, ']' || Val_Two || '[', Val_Two AS DOWN FROM Foo ORDER BY 3DESC;
--
No. Your eyes do not deceive you. Nor is this a bug. Rules for
collating CHAR and VARCHAR with spaces produce a sequence where things
ascend inconsistently with how they descend! TRIM() is your friend.
4. So, what to do?
i. I do not know enough about GLS and about the language in which
you are working to have a definitive answer. But my feeling is that you
should be mighty careful with queries that automatically convert
CHAR/VARCHAR <-> NCHAR/NVARCHAR.
ii. On the code management question, I haven't a clue. You are spot on
that you can't CAST between built-in types, however, so I don't think
that there's an easy hack to be had. I think -- and you're not going to
like this -- that what you've done is to code a bug into your
application. :-(
- I'd really like to hear what other people have to say about all of
this. I kind of hope -- for Leonids' sake -- that I'm wrong
[1] INFORMIX Inc. Informix Guide to SQL: Syntax, Volume 2. Sept. 1999.
pp. 4-55, 4-56.
[2] INFORMIX Inc. Informix Guide to GLS Functionality. Sept, 1999. pp.
1-14, 1-15.
Paul Brown <paul.NOSPAM.brown@informix.com> writes: > Leonids.Voroncovs@dati.lv wrote: > > > Any comments? > > OK. This intrigued me. I had a poke around. The resolution is quite > complex: This is (as usual) very informative, thanks Paul! Thomas
Leonids.Voroncovs@dati.lv wrote: : Rob Wilson wrote: : > If you don't like this, you could play with dropping the casts in the : > 9.2 engine (for that database). : Can You be more detailed? What do You mean by "dropping casts"? DROP CAST (NCHAR AS LVARCHAR); Informix says the cast is dropped when I do it but I did not test that it actually was dropped. : > Rob Wilson : Leonid Vorontsov -- Rob Wilson rwilson@ntsource.com