[freetds] Unexpected results with null fields

ZIGLIO, Frediano, VF-IT Frediano.Ziglio at vodafone.com
Tue Jun 6 07:41:34 EDT 2006


> 
> Hello,
> 
> I am running a query looking like the following one (the one 
> I use also 
> select other fields):
> 
> SELECT id, isnull(
> 	right('0'+convert(varchar,day(max(T.gdh))),2)
> 	+right('0'+convert(varchar,month(max(T.gdh))),2)
> 	+convert(varchar,year(max(T.gdh)))
> 	+right('0'+convert(varchar,datepart(hour,max(T.gdh))),2)
> 	+right('0'+convert(varchar,datepart(minute,max(T.gdh))),2)
> 	,'010119700000')
> FROM MyTable T
> GROUP BY id
> 
> with MyTable(id int not null, gdh datetime null).
> 
> I expect to get either the biggest date (T.gdh) for each id 
> in MyTable 
> ddmmyyyyhhmm formated or '010119700000' when max(T.gdh) if null
> 
> When I run this query from MS Query Analyser, I get the expected 
> results: dates looking like ddmmyyyyhhmm or '010119700000'.
> 
> When I run this query from a Perl script using ODBC + a DSN 
> with MSSQL 
> Driver on Windows I also get the expected results.
> 
> But, when I run this query from a Perl script using DBD::Sybase + 
> FreeTDS on Linux I get dates ddmmyyyyhhmm formated or '0000' when 
> max(T.gdh) is null. It appears the '0000' comes from the 
> right('0'+....) 
> functions: Replaced the first right('0'...) with 
> right('A'...) and i get 
> some 'A000' instead of '0000'.
> 
> Any idea about this unexpected behaviour?
> 

Hi!
  yes, it depends on settings. ODBC like query analyser concatenate
NULLs using empty string while dblib (and ctlib) by default yield NULL
this means that

SELECT 'x' + NULL

return 'x' under ODBC and NULL under ctlib.

The setting is SET ANSI_NULLS (search it under query analyser help)

freddy77




More information about the FreeTDS mailing list