Skip to Content.
Sympa Menu

freetds - Re: [freetds] Unexpected results with null fields

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: KERTEL - Maxime MARAIS <mmarais AT kertel.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Unexpected results with null fields
  • Date: Tue, 06 Jun 2006 14:18:25 +0200

ZIGLIO, Frediano, VF-IT a écrit :
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

I Freddy,

My database is set in such a way that SELECT 'x' + NULL returns NULL under either ODBC or query analyser without setting any option.

SET ANSI_NULLS only modifies the way SELECT * FROM myField = NULL is computed.
With SET ANSI_NULLS ON, this request never returns data (SELECT * FROM myField is NULL must be used instead)
With SET ANSI_NULLS OFF, this request returns rows where myField is NULL.

The right SET option is CONCAT_NULL_YIELDS_NULL. But this option is defined at the database level and not at the session level. As a result, the same results would be expected with either FreeTDS or ODBC considering the way my database is set.

If SET CONCAT_NULL_YIELDS_NULL ON if executed prior to run select queries, the problem remains: I still get '0000' instead of '010119700000' when max(T.gdh) is null.

-
Maxime MARAIS
Kertel.





Archive powered by MHonArc 2.6.24.

Top of Page