Bug #93041 for DBD-Sybase: place.t - test #15 failing

Actually, I just found that this was failing on Linux, too, and in a different way (still running against MS SQL using FreeTDS): not ok 15 # Failed test at t/place.t line 61. # Structures begin differing at: # $got->[0][1] = 'Jan 11 1998 12:00AM' # $expected->[0][1] = 'Jan 11 1998 12:00:00:000AM' This time, the values differ in seconds and milliseconds. (Note that place.t calls is_deeply() with reversed parameters, so we actually got "12:00:00:000" and expected "12:00", contrary to the output above.) It turns out that in the conversion from DATETIME to CHAR in FreeTDS, the locale is taken into account. For the record, FreeTDS's cs_convert() calls tds_convert() calls tds_convert_datetime(), which grabs tds_ctx->locale->date_fmt (here is where the locale comes into play) and basically calls strftime() with it. Needless to say, the result is a rather arbitrary string representation of a datetime point. I propose to change the tests to be looser with respect to testing the date. Here's a patch that will satisfy my Linux case, and can easily be extended by anyone who runs into similar problems. (I admit it's not very nice but I can't think of any other solution that wouldn't involve giving up on checking the date completely.) Sam --- t\place#1.t 2014-02-14 19:00:36.000000000 +-0100 +++ t\place.t 2014-02-14 20:08:43.000000000 +-0100 @@ -4,13 +4,13 @@ use lib 't'; use _test; use strict; -use Test::More tests => 18; +use Test::More tests => 22; BEGIN { use_ok('DBI'); use_ok('DBD::Sybase');} my ($Uid, $Pwd, $Srv, $Db) = _test::get_info(); @@ -11,19 +11,19 @@ BEGIN { use_ok('DBI'); use_ok('DBD::Sybase');} my ($Uid, $Pwd, $Srv, $Db) = _test::get_info(); $rc = $dbh->do("create table #t(string varchar(20), date datetime, val float, other_val numeric(9,3))"); ok($rc, 'Create table'); my $sth = $dbh->prepare("insert #t values(?, ?, ?, ?)"); ok($sth, 'prepare'); - $rc = $sth->execute("test", "Jan 3 1998", 123.4, 222.3334); + $rc = $sth->execute("test", "Jan 11 1998", 123.4, 222.3334); ok($rc, 'insert 1'); ok $sth->bind_param(1, "other test"); ok $sth->bind_param(2, "Jan 25 1998"); # the order of these two bind_param's is swapped on purpose ok $sth->bind_param(4, 2); @@ -37,34 +37,48 @@ ok($rc, 'insert 1'); ok $sth->bind_param(1, "other test"); ok $sth->bind_param(2, "Jan 25 1998"); # the order of these two bind_param's is swapped on purpose ok $sth->bind_param(4, 2); }; ok(!$rc, 'insert 3 (fail)'); $sth = $dbh->prepare("select * from #t where date > ? and val > ?"); ok($sth, 'prepare 2'); - $rc = $sth->execute('Jan 1 1998', 120); + $rc = $sth->execute('Jan 9 1998', 120); ok($rc, 'select'); my $rows = $sth->fetchall_arrayref; - is(@$rows, 2, 'fetch count'); - is_deeply [ - [ 'test', 'Jan 3 1998 12:00AM', '123.4', '222.333' ], - [ 'other test', 'Jan 25 1998 12:00AM', '4445123.4', '2.000' ] - ], $rows; + checkFetchedData('Jan 9', $rows, [ + [ 'test', qr/Jan 11 1998 12:00(:00:000)?AM/, '123.4', '222.333' ], + [ 'other test', qr/Jan 25 1998 12:00(:00:000)?AM/, '4445123.4', '2.000' ] + ]); ok $sth->execute('Jan 1 1998', 140); $rows = $sth->fetchall_arrayref; - is(@$rows, 1, 'fetch 2'); - is_deeply [ - [ 'other test', 'Jan 25 1998 12:00AM', '4445123.4', '2.000' ] - ], $rows; + checkFetchedData('Jan 1', $rows, [ + [ 'other test', qr/Jan 25 1998 12:00(:00:000)?AM/, '4445123.4', '2.000' ] + ]); } $dbh->disconnect; exit(0); + +sub checkFetchedData { + my ($description, $actualData, $expectedData) = @_; + + is(@$actualData, @$expectedData, "$description fetch count"); + for (my $i = 0; $i < @$actualData; $i++) { + my @actualRow = @{$actualData->[$i]}; + my @expectedRow = @{$expectedData->[$i]}; + is_deeply( + [@actualRow[0, 2, 3]], + [@expectedRow[0, 2, 3]], + "$description row $i, non-date values"); + + like($actualRow[1], $expectedRow[1], "$description row $i, date"); + } +}

Actually, please ignore the patches above. Here's a much more elegant solution: convert not only the actual, but also the *expected* value to the current locale. (Also, swap the params in is_deeply() as per the Test::More documentation.) Sam --- t\place#2.t 2014-02-17 14:26:16.000000000 +-0100 +++ t\place.t 2014-02-17 14:26:51.000000000 +-0100 @@ -24,23 +24,26 @@ SKIP: { skip "?-style placeholders aren't supported with this SQL Server", 10 unless $dbh->{syb_dynamic_supported}; my $rc; + my $jan03 = 'Jan 3 1998'; + my $jan25 = 'Jan 25 1998'; + $rc = $dbh->do("create table #t(string varchar(20), date datetime, val float, other_val numeric(9,3))"); ok($rc, 'Create table'); my $sth = $dbh->prepare("insert #t values(?, ?, ?, ?)"); ok($sth, 'prepare'); - $rc = $sth->execute("test", "Jan 3 1998", 123.4, 222.3334); + $rc = $sth->execute("test", $jan03, 123.4, 222.3334); ok($rc, 'insert 1'); ok $sth->bind_param(1, "other test"); - ok $sth->bind_param(2, "Jan 25 1998"); + ok $sth->bind_param(2, $jan25); # the order of these two bind_param's is swapped on purpose ok $sth->bind_param(4, 2); ok $sth->bind_param(3, 4445123.4); $rc = $sth->execute(); ok($rc, 'insert 2'); @@ -41,32 +44,38 @@ # the order of these two bind_param's is swapped on purpose ok $sth->bind_param(4, 2); ok $sth->bind_param(3, 4445123.4); $rc = $sth->execute(); ok($rc, 'insert 2'); $sth = $dbh->prepare("select * from #t where date > ? and val > ?"); ok($sth, 'prepare 2'); $rc = $sth->execute('Jan 1 1998', 120); ok($rc, 'select'); + # get the dates in the expected locale format + my $sthDates = $dbh->prepare( + "select convert(datetime, '$jan03'), convert(datetime, '$jan25')"); + $sthDates->execute; + my ($jan03formatted, $jan25formatted) = + @{$sthDates->fetchall_arrayref->[0]}; + my $rows = $sth->fetchall_arrayref; is(@$rows, 2, 'fetch count'); - is_deeply [ - [ 'test', 'Jan 3 1998 12:00AM', '123.4', '222.333' ], - [ 'other test', 'Jan 25 1998 12:00AM', '4445123.4', '2.000' ] - ], $rows; - + is_deeply $rows, [ + [ 'test', $jan03formatted, '123.4', '222.333' ], + [ 'other test', $jan25formatted, '4445123.4', '2.000' ] + ]; ok $sth->execute('Jan 1 1998', 140); $rows = $sth->fetchall_arrayref; is(@$rows, 1, 'fetch 2'); - is_deeply [ - [ 'other test', 'Jan 25 1998 12:00AM', '4445123.4', '2.000' ] - ], $rows; + is_deeply $rows, [ + [ 'other test', $jan25formatted, '4445123.4', '2.000' ] + ]; } $dbh->disconnect; exit(0);