The only bit I find confusing is why SvNV returns 0 for the non-number cases

Both sv_2nv/SvNV and sv_2iv/SvIV are coercing to the given type (and Perl does interpret "non-numeric" strings as zero...)

You may want to play with the following to get a better idea of what's going on for sv_2nv and sv_2iv (in particular, observe the flags NOK, IOK, and that both NV and IV are almost always being created): 

#!/usr/bin/perl -w

use Inline C => <<'END_C';

void num_test(SV* sv) {

    sv_2nv(sv);
    //sv_2iv(sv);

    do_sv_dump(0, Perl_debug_log, sv, 0, 0, 0, 0);   // like Devel::Pe
+ek
}

END_C

num_test('aa');
num_test('123');
num_test('123aa');
num_test('aa123');
num_test('1.23');

[download]

Output for sv_2nv uncommented: 

Argument "aa" isn't numeric in subroutine entry at ./809635.pl line 15
+.
SV = PVNV(0x669cf0) at 0x65ed30
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 0
  PV = 0x665880 "aa"\0
  CUR = 2
  LEN = 8
SV = PVNV(0x7d8540) at 0x65ecb0
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 123
  PV = 0x644a50 "123"\0
  CUR = 3
  LEN = 8
Argument "123aa" isn't numeric in subroutine entry at ./809635.pl line
+ 17.
SV = PVNV(0x7d85e0) at 0x65ecd0
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 123
  PV = 0x644be0 "123aa"\0
  CUR = 5
  LEN = 8
Argument "aa123" isn't numeric in subroutine entry at ./809635.pl line
+ 18.
SV = PVNV(0x7d8608) at 0x65ed20
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 0
  PV = 0x644cb0 "aa123"\0
  CUR = 5
  LEN = 8
SV = PVNV(0x840a58) at 0x65ed00
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 1.23
  PV = 0x665710 "1.23"\0
  CUR = 4
  LEN = 8

[download]

Output for sv_2iv uncommented: 

Argument "aa" isn't numeric in subroutine entry at ./809635.pl line 15
+.
SV = PVNV(0x669cf0) at 0x65ed30
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,IOK,NOK,POK,READONLY,pIOK,pNOK,pPOK)
  IV = 0
  NV = 0
  PV = 0x665880 "aa"\0
  CUR = 2
  LEN = 8
SV = PVIV(0x63e130) at 0x65ecb0
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,IOK,POK,READONLY,pIOK,pPOK)
  IV = 123
  PV = 0x644a50 "123"\0
  CUR = 3
  LEN = 8
Argument "123aa" isn't numeric in subroutine entry at ./809635.pl line
+ 17.
SV = PVNV(0x7d8540) at 0x65ecd0
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,IOK,NOK,POK,READONLY,pIOK,pNOK,pPOK)
  IV = 123
  NV = 123
  PV = 0x644be0 "123aa"\0
  CUR = 5
  LEN = 8
Argument "aa123" isn't numeric in subroutine entry at ./809635.pl line
+ 18.
SV = PVNV(0x7d85e0) at 0x65ed20
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,IOK,NOK,POK,READONLY,pIOK,pNOK,pPOK)
  IV = 0
  NV = 0
  PV = 0x644cb0 "aa123"\0
  CUR = 5
  LEN = 8
SV = PVNV(0x7d8608) at 0x65ed00
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pIOK,pNOK,pPOK)
  IV = 1
  NV = 1.23
  PV = 0x665710 "1.23"\0
  CUR = 4
  LEN = 8

[download]

It's only by accident (kind of) that you get the desired result for SQL_INTEGER because you're doing cast_ok = (SvIOK(sv) && !SvNOK(sv)); and - as a special case - NV isn't being created for ints like 123.

In reply to Re^7: Why does sv_2nv on non-number look like an NV by almut
in thread Why does sv_2nv on non-number look like an NV by mje

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.