#1  
Old 13th July 2020, 10:51 PM
h.hasenack h.hasenack is offline
Senior Member
 
Join Date: Dec 2008
Posts: 114
Unhappy UNION qith Literats and #DUMMY causes unwanted spaces

Run SQL statement below, and check out the contants of the resulting table. The field containg 'Gebaude' has 4 or 5 trailing spaces.
Our customer found this using NexusDB4.5021 en ginem, but is also there in NexusDB 4.5022
{code}
SELECT
Kategorie_2
INTO
###Temp
FROM
(
SELECT 'Regelungstechnik' AS Kategorie_2 FROM #Dummy
UNION ALL
SELECT 'Rohrleitungen' AS Kategorie_2 FROM #Dummy
UNION ALL
SELECT 'Geb?ude' AS Kategorie_2 FROM #Dummy
) T
{code}

This issue has been reported 13 june 2020, and has not been addressed upto today. Our customer is waiting for a fix, and does not like to add workarounds in his SQL statements everywhere.
__________________
Regards,

H.Hasenack
S&G Asset Management
Netherlands
  #2  
Old 13th July 2020, 11:10 PM
Eivind Bakkestuen
 
Posts: n/a
Default Re: UNION qith Literats and #DUMMY causes unwanted spaces

> This 'issue ' (https://www.nexusdb.com/mantis/view.php?id=2365)has
> been reported 13 june 2020, and has not been addressed upto today. Our
> customer is waiting for a fix, and does not like to add workarounds in
> his SQL statements everywhere.


Can you please provide us with tables that reproduces the problem?

--

Eivind [NX]
  #3  
Old 13th July 2020, 11:52 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: UNION qith Literats and #DUMMY causes unwanted spaces

h.hasenack wrote:

>
> Run SQL statement below, and check out the contants of the resulting
> table. The field containg 'Gebaude' has 4 or 5 trailing spaces.
> Our customer found this using NexusDB4.5021 en ginem, but is also there
> in NexusDB 4.5022
> {code}
> SELECT
> Kategorie_2
> INTO
> ###Temp
> FROM
> (
> SELECT 'Regelungstechnik' AS Kategorie_2 FROM #Dummy
> UNION ALL
> SELECT 'Rohrleitungen' AS Kategorie_2 FROM #Dummy
> UNION ALL
> SELECT 'Geb?ude' AS Kategorie_2 FROM #Dummy
> ) T
> {code}
>
> This 'issue ' (https://www.nexusdb.com/mantis/view.php?id=2365)has been
> reported 13 june 2020, and has not been addressed upto today. Our
> customer is waiting for a fix, and does not like to add workarounds in
> his SQL statements everywhere.


string literals are of type CHAR

cast them to VARCHAR if that is what you want.
  #4  
Old 14th July 2020, 12:47 AM
h.hasenack h.hasenack is offline
Senior Member
 
Join Date: Dec 2008
Posts: 114
Lightbulb

This is clearly not what I (or our customer FTM) expected. Could'nt find the ISO SQL 2003 spec for this either so quickly. Nevertheless I reported this back to our customer, and he'll have to rethink his queries.

Though it seems this change was introduced in one of the later versions of NexusDB as it is fairly old SQL code (2yr+) that thy are now checking and updating for out latest release of our software.
__________________
Regards,

H.Hasenack
S&G Asset Management
Netherlands
  #5  
Old 14th July 2020, 02:48 AM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: UNION qith Literats and #DUMMY causes unwanted spaces

h.hasenack wrote:

>
> This is clearly not what I (or our customer FTM) expected. Could'nt find
> the ISO SQL 2003 spec for this either so quickly. Nevertheless I
> reported this back to our customer, and he'll have to rethink his
> queries.
>
> Though it seems this change was introduced in one of the later versions
> of NexusDB as it is fairly old SQL code (2yr+) that thy are now checking
> and updating for out latest release of our software.


As far as I remember, string literals have always been CHAR.

The relevant section of the SQL standard is:

5.3 <literal>

....

Syntax Rules

17) The declared type of a <character string literal> is fixed-length character
string. The length of a <character string literal> is the number of <character
representation>s that it contains.
  #6  
Old 17th July 2020, 10:45 PM
Ad Franse
 
Posts: n/a
Default Re: UNION qith Literats and #DUMMY causes unwanted spaces

Hi Hans,
this tells it all

Ad Franse




select 'Regelungstechnik' as cat2 into test01_t01 from #dummy;
select 'rohrleitungen' as cat2 into test01_t02 from #dummy;
select * into test01_t09 from ( select * from
test01_t01
union all
select * from
test01_t02 );

select cast( 'Regelungstechnik' as varchar( 100)) as cat2 into
test02_t01 from #dummy;
select * into test02_t09 from ( select * from
test02_t01
union all
select * from
test01_t01 );
select * from #fields where table_name like 'test0%';


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pad spaces on left David Guest nexusdb.public.support.sql 2 23rd February 2017 01:09 AM
Identifiers/name spaces in NexusDB V3 Ole Willy Tuv nexusdb.public.discussions 2 10th November 2006 06:52 AM
LIST() trim spaces Thomas Lohrum nexusdb.public.support.sql 18 3rd April 2006 11:19 PM
Alias on path with spaces Anders Gustavsson nexusdb.public.support 3 9th July 2005 10:43 PM
JTabor-dummy-test.zip J Tabor Binaries 0 2nd December 2003 03:21 PM


All times are GMT +11. The time now is 02:50 PM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.