Changing 'sv_setpvn' to 'sv_setpvn_mg'

[lzsiga]

I've found some strange behavior in YaSQL which is a Sql*Plus alternative written in Perl that uses DBD-Oracle.

I've created a test-script to show the problem: https://github.com/lzsiga/yasql-fx/blob/main/test/test0001.sh

The problem has something to do with utf8, function substr, caching string-len and 'magical variables'.

One way to solve it would be changing every call of sv_setpvn to sv_setpvn_mg in oci8.c. I admit I don't really know why both still exist, I'd say sv_setpvn_mg is the reliable variant, sv_setpvn is the quick but unreliable one.

[spuelrich]

Hi, I have the same issue. I narrowed it down to the following script. It seems to depend on NSL_LANG setting. length and bytes::length return different values.

use strict;
use warnings;
use v5.26;
use Devel::Peek;
use DBI;
use DBD::Oracle;
require bytes;

sub test {
    $ENV{NLS_LANG} = $_[0];
    my $dbh = DBI->connect(
        'DBI:Oracle:host=xxx;service_name=xxx;port=1521',
        'xxx',
        'xxx',
        );

    my $sql = <<~EOsql;
        SELECT '1.555' nam FROM dual union all
        SELECT '2.6666' nam FROM dual union all
        SELECT '3.44'    nam FROM dual union all
        SELECT '4.555'  nam FROM dual order by nam
        EOsql

    my $sth = $dbh->prepare($sql);
    $sth->execute();

    say "---- env.nls_lang $ENV{NLS_LANG}";
    while (my $row = $sth->fetchrow_arrayref()) {
        say $row->[0] . q( ) . length($row->[0]) . q( - ) . bytes::length($row->[0]);
        #Dump($row->[0]);
    }
}

say "Perl: $^V";
say "DBD::Oracle $DBD::Oracle::VERSION";

test(q());
test(q(GERMAN_GERMANY.AL32UTF8));

output Perl v5.32 + DBD::Oracle v1.80

Perl: v5.32.0
DBD::Oracle 1.80
---- env.nls_lang
1.555 5 - 5
2.6666 6 - 6
3.44 4 - 4
4.555 5 - 5
---- env.nls_lang GERMAN_GERMANY.AL32UTF8
1.555 5 - 5
2.6666 6 - 6
3.44 4 - 4
4.555 5 - 5

output Perl v5.40 + DBD::Oracle v1.90

Perl: v5.40.0
DBD::Oracle 1.90
---- env.nls_lang
1.555 5 - 5
2.6666 6 - 6
3.44 4 - 4
4.555 5 - 5
---- env.nls_lang GERMAN_GERMANY.AL32UTF8
1.555 5 - 5
2.6666 5 - 6
3.44 5 - 4
4.555 5 - 5

Perl v5.32 + DBD::Oracle v1.80
NLS_LANG empty OK
NLS_LANG =AL32UTF8 OK

Perl v5.40 + DBD::Oracle v1.90
NLS_LANG empty OK
NLS_LANG =AL32UTF8 FAILS

With the Devel::Peek::Dump enabled I see also a lot of wrong "magic" with Perl v5.40:

1.555 5 - 5
SV = PVMG(0x1d3733f8760) at 0x1d3732da210
  REFCNT = 1
  FLAGS = (SMG,POK,pPOK,UTF8)
  IV = 0
  NV = 0
  PV = 0x1d37347fb70 "1.555"\0 [UTF8 "1.555"]
  CUR = 5
  LEN = 16
  MAGIC = 0x1d373720ef0
    MG_VIRTUAL = &PL_vtbl_utf8
    MG_TYPE = PERL_MAGIC_utf8(w)
    MG_LEN = 5

2.6666 5 - 6
SV = PVMG(0x1d3733f8760) at 0x1d3732da210
  REFCNT = 1
  FLAGS = (SMG,POK,pPOK,UTF8)
  IV = 0
  NV = 0
  PV = 0x1d37347fb70 "2.6666"\0 [UTF8 "2.6666"]
  CUR = 6
  LEN = 16
  MAGIC = 0x1d373720ef0
    MG_VIRTUAL = &PL_vtbl_utf8
    MG_TYPE = PERL_MAGIC_utf8(w)
    MG_LEN = 5

3.44 5 - 4
SV = PVMG(0x1d3733f8760) at 0x1d3732da210
  REFCNT = 1
  FLAGS = (SMG,POK,pPOK,UTF8)
  IV = 0
  NV = 0
  PV = 0x1d37347fb70 "3.44"\0 [UTF8 "3.44"]
  CUR = 4
  LEN = 16
  MAGIC = 0x1d373720ef0
    MG_VIRTUAL = &PL_vtbl_utf8
    MG_TYPE = PERL_MAGIC_utf8(w)
    MG_LEN = 5

4.555 5 - 5
SV = PVMG(0x1d3733f8760) at 0x1d3732da210
  REFCNT = 1
  FLAGS = (SMG,POK,pPOK,UTF8)
  IV = 0
  NV = 0
  PV = 0x1d37347fb70 "4.555"\0 [UTF8 "4.555"]
  CUR = 5
  LEN = 16
  MAGIC = 0x1d373720ef0
    MG_VIRTUAL = &PL_vtbl_utf8
    MG_TYPE = PERL_MAGIC_utf8(w)
    MG_LEN = 5

[lzsiga]

@spuelrich Hi, does perl -Ca option give some error message?

[spuelrich]

@lzsiga yes

panic: sv_len_utf8 cache 5 real 6 for 2.6666 at ora.pl line 30.

I split the output. It's the length(...) that raises the error.

[lzsiga]

I'd say it has to be handled in three steps:

• In Perl core, make sv_setpvn synonym to sv_setvpn_magic
• In DBD-Oracle, replace every sv_setpvn to sv_setvpn_magic
• In actual Perl code, use $row= undef