|
Home > Archive > SQL Anywhere database > October 2005 > Proxy tables trimming - probably a bug
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Proxy tables trimming - probably a bug
|
|
|
| Greetings,
during my endless sessions with Sybase ASA 9.0.2.2551 I've
come accross this:
When creating a proxy table, it automatically trims
values!!!
This is too hard for me to swallow. For example: in the
remote database, the column value is "Joseph " but when
you create an so called existing table (proxy table) in
local database, the value of that column will be "Joseph".
That's fatal when comparing the values, because they differ.
Am I missing something? Could somebody please explain it?
Thanks, I wish you all the best,
Pavel
| |
| Breck Carter [TeamSybase] 2005-10-27, 7:41 am |
| I don't see that behavior with two ASA 9.0.2.3193 databases:
SELECT pkey, STRING ( '[', name, ']' ) FROM proxy_t;
pkey,STRING('[',proxy_t.name,']')
1,'[Joseph ]'
Here is the test I used...
-- Database ddd2:
CREATE TABLE t (
pkey INTEGER NOT NULL PRIMARY KEY,
name VARCHAR ( 100 ) NOT NULL );
INSERT t VALUES ( 1, 'Joseph ' );
COMMIT;
-- Database ddd:
CREATE SERVER target CLASS 'ASAODBC'
USING 'DRIVER=Adaptive Server Anywhere 9. 0;ENG=ddd2;DBN=ddd2'
;
CREATE EXTERNLOGIN DBA TO target REMOTE LOGIN DBA IDENTIFIED BY 'sql';
CREATE EXISTING TABLE proxy_t AT 'target..DBA.t';
SELECT pkey, STRING ( '[', name, ']' ) FROM proxy_t;
=====
Is there something different about your setup? Suddenly trimming
spaces would be a *huge* problem, and there have been no other reports
I am aware of.
Breck
On 27 Oct 2005 03:08:18 -0700, Pavel wrote:
>Greetings,
>
>during my endless sessions with Sybase ASA 9.0.2.2551 I've
>come accross this:
>
>When creating a proxy table, it automatically trims
>values!!!
>
>This is too hard for me to swallow. For example: in the
>remote database, the column value is "Joseph " but when
>you create an so called existing table (proxy table) in
>local database, the value of that column will be "Joseph".
>
>That's fatal when comparing the values, because they differ.
>Am I missing something? Could somebody please explain it?
>
>Thanks, I wish you all the best,
>Pavel
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Rudy Sujanto 2005-10-27, 7:41 am |
| Pavel,
Probably one of the databases was created with the "ignore trailing
blanks in comparison" option while the other was not. You can check it
from the database properties from SybaseCentral.
Regards,
Rudy Sujanto
| |
|
| Dear people,
I've checked the "ignore trailing blanks" from Sybase
Central for both databases, they are both set to "No". But I
didn't know about this feature, so for me from now on, this
is a good thing to remember. Thanks, Rudy!
Mr. Breck, you were right, it's not in proxy tables.
Regarding what you've written, I've realized that the
columns in proxy tables contain whitespaces (= columns are
not trimmed) perfectly like in original tables.
But the problem still persists. I've expanded your example
of the following:
- Database ddd:
run the same CREATE TABLE as was run at ddd2 database
and then, with server and proxy table created, run the
following:
SELECT * FROM t AS loc
JOIN proxy_t AS rem ON loc.pkey=rem.pkey
WHERE loc.name<>rem.name
....this produces a result!! Also, running
SELECT * FROM t AS loc
JOIN proxy_t AS rem ON loc.pkey=rem.pkey
WHERE loc.name=rem.name
....produces no result!
I believe, results for this two examples should be the
opposite. There is still a possibility that I'm missing
something important, but this "thing" is beyond my knowledge
opportunities, so I need external advices, please.
Thanks for making progress so far,
Pavel
> I don't see that behavior with two ASA 9.0.2.3193
> databases:
>
> SELECT pkey, STRING ( '[', name, ']' ) FROM proxy_t;
> pkey,STRING('[',proxy_t.name,']')
> 1,'[Joseph ]'
>
> Here is the test I used...
>
> -- Database ddd2:
>
> CREATE TABLE t (
> pkey INTEGER NOT NULL PRIMARY KEY,
> name VARCHAR ( 100 ) NOT NULL );
> INSERT t VALUES ( 1, 'Joseph ' );
> COMMIT;
>
> -- Database ddd:
>
> CREATE SERVER target CLASS 'ASAODBC'
> USING 'DRIVER=Adaptive Server Anywhere 9.0;ENG=ddd2
> ;DBN=ddd2';
>
> CREATE EXTERNLOGIN DBA TO target REMOTE LOGIN DBA
> IDENTIFIED BY 'sql';
>
> CREATE EXISTING TABLE proxy_t AT 'target..DBA.t';
>
> SELECT pkey, STRING ( '[', name, ']' ) FROM proxy_t;
>
> =====
>
> Is there something different about your setup? Suddenly
> trimming spaces would be a *huge* problem, and there have
> been no other reports I am aware of.
>
> Breck
>
>
>
>
> On 27 Oct 2005 03:08:18 -0700, Pavel wrote:
>
> I've >come accross this:
> when >you create an so called existing table (proxy table)
> in >local database, the value of that column will be
> "Joseph". >
> differ. >Am I missing something? Could somebody please
> explain it? >
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
| |
| Breck Carter [TeamSybase] 2005-10-27, 7:41 am |
| Yes, *that's* a problem... the column gets trimmed when the proxy
table is in a JOIN with the local table.
I ran this test in 9.0.2.3193, using brand-new databases created with
all the default settings. The table t is identical in both databases,
and the proxy_t was defined as shown in an earlier posting.
BEGIN
SELECT STRING ( '[', rem.name, ']' ), * FROM t AS loc
JOIN proxy_t AS rem ON loc.pkey=rem.pkey
WHERE loc.name<>rem.name;
select STRING ( '[', proxy_t.name, ']' ), * from proxy_t;
END;
STRING('[',rem. name,']'),pkey,name,
pkey,name
'& #91;Joseph]',1,'Jose
ph ',1,'Joseph'
STRING('[',proxy_t.name,']'),pkey,name
'[Joseph ]',1,'Joseph'
Breck
On 27 Oct 2005 04:52:19 -0700, Pavel wrote:
[color=darkred]
>Dear people,
>
>I've checked the "ignore trailing blanks" from Sybase
>Central for both databases, they are both set to "No". But I
>didn't know about this feature, so for me from now on, this
>is a good thing to remember. Thanks, Rudy!
>
>Mr. Breck, you were right, it's not in proxy tables.
>Regarding what you've written, I've realized that the
>columns in proxy tables contain whitespaces (= columns are
>not trimmed) perfectly like in original tables.
>
>But the problem still persists. I've expanded your example
>of the following:
>
>- Database ddd:
>run the same CREATE TABLE as was run at ddd2 database
>
>and then, with server and proxy table created, run the
>following:
>
>SELECT * FROM t AS loc
>JOIN proxy_t AS rem ON loc.pkey=rem.pkey
>WHERE loc.name<>rem.name
>
>...this produces a result!! Also, running
>
>SELECT * FROM t AS loc
>JOIN proxy_t AS rem ON loc.pkey=rem.pkey
>WHERE loc.name=rem.name
>
>...produces no result!
>
>I believe, results for this two examples should be the
>opposite. There is still a possibility that I'm missing
>something important, but this "thing" is beyond my knowledge
>opportunities, so I need external advices, please.
>
>Thanks for making progress so far,
>Pavel
>
>http://www.amazon.com/exec/obidos/A...7/risingroad-20
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
|
| I see it clearly now, thanks.
I'll try to compare columns at the variable level, or make
up with something... any suggestions?
Also I'd like to know what's the end - is it a bug, and if
yes, will it be fixed in some EBF or next release? If it's
not a bug, why IS that trimming THERE?
Thanks in advance!
Pavel
> Yes, *that's* a problem... the column gets trimmed when
> the proxy table is in a JOIN with the local table.
>
> I ran this test in 9.0.2.3193, using brand-new databases
> created with all the default settings. The table t is
> identical in both databases, and the proxy_t was defined
> as shown in an earlier posting.
>
> BEGIN
> SELECT STRING ( '[', rem.name, ']' ), * FROM t AS loc
> JOIN proxy_t AS rem ON loc.pkey=rem.pkey
> WHERE loc.name<>rem.name;
> select STRING ( '[', proxy_t.name, ']' ), * from proxy_t;
> END;
>
> STRING('[',rem. name,']'),pkey,name,
pkey,name
> '& #91;Joseph]',1,'Jose
ph ',1,'Joseph'
>
> STRING('[',proxy_t.name,']'),pkey,name
> '[Joseph ]',1,'Joseph'
>
> Breck
>
>
> On 27 Oct 2005 04:52:19 -0700, Pavel wrote:
>
> But I >didn't know about this feature, so for me from now
> on, this >is a good thing to remember. Thanks, Rudy!
> are >not trimmed) perfectly like in original tables.
> example >of the following:
> knowledge >opportunities, so I need external advices,
> please. >
> have >> been no other reports I am aware of.
> the >> >remote database, the column value is "Joseph "
> but >> when >you create an so called existing table (proxy
> table) >> in >local database, the value of that column
> will be >> "Joseph". >
> oad-20 >> bcarter@risingroad.com
> Services >> www.risingroad.com
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
| |
| Breck Carter [TeamSybase] 2005-10-27, 9:23 am |
| It is a bug, probably introduced sometime in version 8. I have posted
a reproducible below.
Breck
=====
Reproducible for proxy table join trimming strings in ASA 8 and 9, but
not in 7:
-- Database ddd2:
CREATE TABLE t (
pkey INTEGER NOT NULL PRIMARY KEY,
name VARCHAR ( 100 ) NOT NULL );
INSERT t VALUES ( 1, 'Joseph ' );
COMMIT;
-- Database ddd:
CREATE TABLE t (
pkey INTEGER NOT NULL PRIMARY KEY,
name VARCHAR ( 100 ) NOT NULL );
INSERT t VALUES ( 1, 'Joseph ' );
COMMIT;
CREATE SERVER target CLASS 'ASAODBC'
USING 'DRIVER=Adaptive Server Anywhere 9. 0;ENG=ddd2;DBN=ddd2'
;
CREATE EXTERNLOGIN DBA TO target REMOTE LOGIN DBA IDENTIFIED BY 'sql';
CREATE EXISTING TABLE proxy_t AT 'target..DBA.t';
BEGIN
SELECT 'OK',
proxy_t.pkey,
STRING ( '[', proxy_t.name, ']' )
FROM proxy_t;
SELECT 'Not OK',
proxy_t.pkey,
STRING ( '[', proxy_t.name, ']' )
FROM proxy_t INNER JOIN t ON proxy_t.pkey = t.pkey;
END;
-- Not OK in SQL Anywhere 9.0.2.3193, 8.0.3.5122
'OK',pkey,STRING('[',proxy_t.name,']')
'OK',1,'[Joseph ]'
'Not OK',pkey,STRING('[',proxy_t.name,']')
'Not OK',1,'[Joseph]'
-- Actually works OK in 7.0.4.3532
'OK',pkey,STRING('[',proxy_t.name,']')
'OK',1,'[Joseph ]'
'Not OK',pkey,STRING('[',proxy_t.name,']')
'Not OK',1,'[Joseph ]'
On 27 Oct 2005 05:45:02 -0700, Pavel wrote:
[color=darkred]
>I see it clearly now, thanks.
>
>I'll try to compare columns at the variable level, or make
>up with something... any suggestions?
>
>Also I'd like to know what's the end - is it a bug, and if
>yes, will it be fixed in some EBF or next release? If it's
>not a bug, why IS that trimming THERE?
>
>Thanks in advance!
>Pavel
>
>http://www.amazon.com/exec/obidos/A...7/risingroad-20
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
|
| Thank you very much, have a nice day (all who tried to
help).
Pavel
> It is a bug, probably introduced sometime in version 8. I
> have posted a reproducible below.
>
> Breck
>
> =====
>
> Reproducible for proxy table join trimming strings in ASA
> 8 and 9, but not in 7:
>
> -- Database ddd2:
>
> CREATE TABLE t (
> pkey INTEGER NOT NULL PRIMARY KEY,
> name VARCHAR ( 100 ) NOT NULL );
> INSERT t VALUES ( 1, 'Joseph ' );
> COMMIT;
>
> -- Database ddd:
>
> CREATE TABLE t (
> pkey INTEGER NOT NULL PRIMARY KEY,
> name VARCHAR ( 100 ) NOT NULL );
> INSERT t VALUES ( 1, 'Joseph ' );
> COMMIT;
>
> CREATE SERVER target CLASS 'ASAODBC'
> USING 'DRIVER=Adaptive Server Anywhere 9.0;ENG=ddd2
> ;DBN=ddd2';
>
> CREATE EXTERNLOGIN DBA TO target REMOTE LOGIN DBA
> IDENTIFIED BY 'sql';
>
> CREATE EXISTING TABLE proxy_t AT 'target..DBA.t';
>
> BEGIN
> SELECT 'OK',
> proxy_t.pkey,
> STRING ( '[', proxy_t.name, ']' )
> FROM proxy_t;
> SELECT 'Not OK',
> proxy_t.pkey,
> STRING ( '[', proxy_t.name, ']' )
> FROM proxy_t INNER JOIN t ON proxy_t.pkey = t.pkey;
> END;
>
> -- Not OK in SQL Anywhere 9.0.2.3193, 8.0.3.5122
>
> 'OK',pkey,STRING('[',proxy_t.name,']')
> 'OK',1,'[Joseph ]'
>
> 'Not OK',pkey,STRING('[',proxy_t.name,']')
> 'Not OK',1,'[Joseph]'
>
> -- Actually works OK in 7.0.4.3532
>
> 'OK',pkey,STRING('[',proxy_t.name,']')
> 'OK',1,'[Joseph ]'
>
> 'Not OK',pkey,STRING('[',proxy_t.name,']')
> 'Not OK',1,'[Joseph ]'
>
>
>
>
> On 27 Oct 2005 05:45:02 -0700, Pavel wrote:
>
> make >up with something... any suggestions?
> if >yes, will it be fixed in some EBF or next release? If
> it's >not a bug, why IS that trimming THERE?
> databases >> created with all the default settings. The
> table t is >> identical in both databases, and the proxy_t
> was defined >> as shown in an earlier posting.
> proxy_t; >> END;
> now >> on, this >is a good thing to remember. Thanks,
> Rudy! >> >
> missing >> >something important, but this "thing" is
> beyond my >> knowledge >opportunities, so I need external
> advices, >> please. >
> ;ENG=ddd2 >> >> ;DBN=ddd2';
> Suddenly >> >> trimming spaces would be a *huge* problem,
> and there >> have >> been no other reports I am aware of.
> 9.0.2.2551 >> >> I've >come accross this:
> " >> but >> when >you create an so called existing table
> (proxy >> table) >> in >local database, the value of that
> column >> will be >> "Joseph". >
> they >> >> differ. >Am I missing something? Could somebody
> please >> >> explain it? >
> Anywhere and MobiLink Professional >> Services >>
> www.risingroad.com >>
> oad-20 >> bcarter@risingroad.com
> Services >> www.risingroad.com
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
| |
| Joshua Savill 2005-10-27, 9:23 am |
| Breck,
Thanks for the simply repro. I will submit the problem to engineering for
review.
Cheers,
--
Joshua Savill
iAnywhere Solutions - Product Support Analyst
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:4jk1m15fa2gknf8
tskeqkjqsgvff0tff0q@
4ax.com...
> It is a bug, probably introduced sometime in version 8. I have posted
> a reproducible below.
>
> Breck
>
> =====
>
> Reproducible for proxy table join trimming strings in ASA 8 and 9, but
> not in 7:
>
> -- Database ddd2:
>
> CREATE TABLE t (
> pkey INTEGER NOT NULL PRIMARY KEY,
> name VARCHAR ( 100 ) NOT NULL );
> INSERT t VALUES ( 1, 'Joseph ' );
> COMMIT;
>
> -- Database ddd:
>
> CREATE TABLE t (
> pkey INTEGER NOT NULL PRIMARY KEY,
> name VARCHAR ( 100 ) NOT NULL );
> INSERT t VALUES ( 1, 'Joseph ' );
> COMMIT;
>
> CREATE SERVER target CLASS 'ASAODBC'
> USING 'DRIVER=Adaptive Server Anywhere 9. 0;ENG=ddd2;DBN=ddd2'
;
>
> CREATE EXTERNLOGIN DBA TO target REMOTE LOGIN DBA IDENTIFIED BY 'sql';
>
> CREATE EXISTING TABLE proxy_t AT 'target..DBA.t';
>
> BEGIN
> SELECT 'OK',
> proxy_t.pkey,
> STRING ( '[', proxy_t.name, ']' )
> FROM proxy_t;
> SELECT 'Not OK',
> proxy_t.pkey,
> STRING ( '[', proxy_t.name, ']' )
> FROM proxy_t INNER JOIN t ON proxy_t.pkey = t.pkey;
> END;
>
> -- Not OK in SQL Anywhere 9.0.2.3193, 8.0.3.5122
>
> 'OK',pkey,STRING('[',proxy_t.name,']')
> 'OK',1,'[Joseph ]'
>
> 'Not OK',pkey,STRING('[',proxy_t.name,']')
> 'Not OK',1,'[Joseph]'
>
> -- Actually works OK in 7.0.4.3532
>
> 'OK',pkey,STRING('[',proxy_t.name,']')
> 'OK',1,'[Joseph ]'
>
> 'Not OK',pkey,STRING('[',proxy_t.name,']')
> 'Not OK',1,'[Joseph ]'
>
>
>
>
> On 27 Oct 2005 05:45:02 -0700, Pavel wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
| |
| Joshua Savill 2005-10-28, 8:23 pm |
| Problem fixed. Fix will appear in EBF 3214 for ASA 9.0.2.
--
Joshua Savill
iAnywhere Solutions - Product Support Analyst
"Joshua Savill" <jsavill@ianywhere.com> wrote in message
news:4360de98$1@foru
ms-1-dub...
> Breck,
>
> Thanks for the simply repro. I will submit the problem to engineering for
> review.
>
> Cheers,
> --
> Joshua Savill
> iAnywhere Solutions - Product Support Analyst
>
>
> "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
> message news:4jk1m15fa2gknf8
tskeqkjqsgvff0tff0q@
4ax.com...
>
>
|
|
|
|
|