incorrect value from keys( %{$sth->{ParamValues}} ) after using $DBI::lasth when using prepare_cached()

[sherrardb]

good day,
please forgive me if this has already been covered or if i have misread/misunderstood something in the docs.

context:
in order to debug an unrelated problem, i was inserting code of the form:

warn $sth->{Statement};
warn join( ', ', map { $sth->{ParamValues}->{$_} } sort { $a <=> $b } keys( %{$sth->{ParamValues}} ) );

alone, this works as expected.

but there are other sections of code where i don't have easy, direct access to the statement handle, so thought i'd use $DBI::lasth to access the statement handle from the caller:

my $lasth = $DBI::lasth;
warn $lasth->{Statement};
warn join( ', ', map { $lasth->{ParamValues}->{$_} } sort { $a <=> $b } keys( %{$last->{ParamValues}} ) );

but after this section is triggered, the previous idiom produces an empty list for calls to

keys( %{$sth->{ParamValues}} ) )

the culprit seems to be the combination of triggering keys( $DBI::lasth->{ParamValues} ) and using $dbh->prepare_cached(). removing either seems to make the code work as expected.

MRE:

#! /bin/perl

$|++;

use strict;
use warnings;

use DBI qw();
use Data::Dumper qw();

my $dbh = DBI->connect( 'DBI:mysql:<USER>:<HOST>', '<DBNAME>', '<DBPASS>' );

my $TABLE_NAME = '_test_dbi_lasth';

$dbh->do( qq[CREATE TABLE $TABLE_NAME ( foo CHAR(5) )] );

{
  my $sth = $dbh->prepare_cached( qq[SELECT foo FROM $TABLE_NAME WHERE foo = ?] );
  $sth->bind_param( 1, 'bar' );
  $sth->execute();

  DBI->trace( '2' );
  warn join( ', ', sort( { $a <=> $b } keys( %{$sth->{ParamValues}} ) ) );
  DBI->trace( '0' );
  $sth->finish();
}

my $lasth = $DBI::lasth;
DBI->trace( '2' );
keys( %{$lasth->{ParamValues}} );
DBI->trace( '0' );

{
  my $sth = $dbh->prepare_cached( qq[SELECT foo FROM $TABLE_NAME WHERE foo = ?] );
  $sth->bind_param( 1, 'bar' );
  $sth->execute();

  DBI->trace( '2' );
  warn join( ', ', sort( { $a <=> $b } keys( %{$sth->{ParamValues}} ) ) );
  DBI->trace( '0' );
  $sth->finish();
}

$dbh->do( qq[DROP TABLE $TABLE_NAME] );

the telling line from the trace output appears to be

    <> FETCH= ( HASH(0x563d576ce810)0keys ) [1 items] ('ParamValues' from cache) at dbi-lasth-bug.pl line 41

which differs from the working case

    <- FETCH= ( HASH(0x558f9f73fd50)1keys ) [1 items] at dbi-lasth-bug.pl line 41

we are stuck using an older version of for reasons that are not easy to address

~$ perl -MDBI -we 'print $DBI::VERSION."\n";'
1.643

so please forgive me if this has been addressed in the current version of the module.

[sherrardb]

note: i have stumbled across another issue which appears to be related to this one, in that it also goes away when i switch from prepare_cached() to prepare().

if it seems warranted after the root cause of the current issue has been determined, then i will generate a proper MRE, but the crux of the new issue is that if bind_param() is used on a cached statement, the actual SQL generated can be mangled based on the particular values bound in previous instances.

no problem:

my $sth = $dbh->prepare_cached( $sql );
...
# bind an empty value on an INT column
$sth->bind_param( 21, '', 4 );
...
$sth->execute();
# generates "..., anchor = '', key_data = '..."

but if you have first bound a non-empty value on the same column:

my$sth = $dbh->prepare_cached( $sql );
...
# bind a non-empty value on an INT column
$sth->bind_param( 21, 1731773295, 4 );
...
$sth->execute();
# generates "..., anchor = 1731773295, key_data = '..."

# then later, on the same $dbh

my $sth = $dbh->prepare_cached( $sql );
...
# bind an empty value on an INT column
$sth->bind_param( 21, '', 4 );
...
$sth->execute();
# generates "..., anchor = , key_data = '..."

this throws the error DBD::mysql::st execute failed: ... for the right syntax to use near ' key_data = '\0005\000B\0002\0\0\0 presumably because of the missing empty single quotes after the previous parameter.

[sherrardb]

good day,
i just wanted to check on the status of this issue and see whether there is anything you require from me.

thx

[Tux]

I have no idea what is going on or where to look. Reading this again, I wonder if it is actually DBI internals or the DBD (DBD::mysql in this case)
Are you able to take a deeper dive?

[sherrardb]

I have no idea what is going on or where to look. Reading this again, I wonder if it is actually DBI internals or the DBD (DBD::mysql in this case) Are you able to take a deeper dive?

indeed i can, if you can provide some pointers. :-)

as a simple test, i think i can (and probably should have already) test against DBD::MariaDB, even thought that might not provide conclusive results.

given that a portion of DBD::mysql is in C and XS, is there any way to insert further debugging and test directly against the contents of my checked-out github repo without having to do a full install?

[sherrardb]

the behavior is the same from DBD::MariaDB. do you have any suggestions as to how to pick apart whether the problem predates the fork of DBD::MariaDB from DBD::mysql vs whether the problem is in the DBI internals?

[Tux]

I can reproduce

🐧 cat issue-173.pl
#!/usr/bin/perl

use 5.026002;
use warnings;

use DBI;
use Data::Peek;

my $dbn = getpwuid $<;
my $dbh = DBI->connect ("DBI:MariaDB:dbname=$dbn");

my $TABLE_NAME = "_test_dbi_lasth";

$dbh->do (qq{create table $TABLE_NAME (foo char (5))});

{   my $sth = $dbh->prepare_cached (
	qq{select foo from $TABLE_NAME where foo = ?});
    $sth->bind_param (1, "bar");
    $sth->execute ();

    DBI->trace (99);
    DDumper { pv => [ sort { $a <=> $b } keys %{$sth->{ParamValues}} ]};
    DBI->trace (0);
    $sth->finish ();
    }

warn "=" x 50, "\n";
my $lasth = $DBI::lasth;
DBI->trace (99);
DDumper { keys => [ keys %{$lasth->{ParamValues}} ]};
DBI->trace ("0");

{   my $sth = $dbh->prepare_cached (
	qq{select foo from $TABLE_NAME where foo = ?});
    $sth->bind_param (1, "bar");
    $sth->execute ();

    DBI->trace (99);
    DDumper { pv => [ sort { $a <=> $b } keys %{$sth->{ParamValues}} ]};
    DBI->trace (0);
    $sth->finish ();
    }

$dbh->do (qq{drop table $TABLE_NAME});

Linux 6.15.6-1-default [openSUSE Tumbleweed 20250714]
This is perl 5, version 42, subversion 0 (v5.42.0) built for x86_64-linux-thread-multi-quadmath

DBI 1.647, DBD::MariaDB 1.24. mariadb-11.8.2

🐧 perl issue-173.pl
    DBI 1.647-ithread default trace level set to 0x0/3 (pid 1715552 pi 379d92a0) at issue-173.pl line 21
    -> FETCH for DBD::MariaDB::st (DBI::st=HASH(0x39e628b0)~INNER 'ParamValues') thr#379d92a0
    -> mariadb_st_FETCH_attrib for 37c2b6c8, key ParamValues
    <- FETCH= ( HASH(0x39e5d8d0)1keys ) [1 items] at issue-173.pl line 22
{   pv               => [
        '0'
        ]
    }
==================================================
    DBI 1.647-ithread default trace level set to 0x0/3 (pid 1715552 pi 379d92a0) at issue-173.pl line 29
{   keys             => []
    }
    DBI 1.647-ithread default trace level set to 0x0/3 (pid 1715552 pi 379d92a0) at issue-173.pl line 38
    <> FETCH= ( HASH(0x39e5d8d0)0keys ) [1 items] ('ParamValues' from cache) at issue-173.pl line 39
{   pv               => []
    }

But for now I have no leads (yet)

[sherrardb]

@Tux here's another data point, even though i'm not sure whether it is material or useful:
the problem seems to go away if we use an explicit FETCH() on $lasth, instead of simple hash-based access.

#!/usr/bin/perl

$|++;

use 5.026002;
use strict;
use warnings;

use DBI qw();


my $dbh = DBI->connect( 'DBI:mysql:<USER>:<HOST>', '<DBNAME>', '<DBPASS>' );

my $TABLE_NAME = '_test_dbi_lasth';

$dbh->do( qq[CREATE TABLE $TABLE_NAME ( foo CHAR(5) )] );

{
  my $sth = $dbh->prepare_cached( qq[SELECT foo FROM $TABLE_NAME WHERE foo = ?] );
  $sth->bind_param( 1, 'bar' );
  $sth->execute();

  DBI->trace( 99 );
  warn join( ', ', sort( { $a <=> $b } keys( %{$sth->{ParamValues}} ) ) );
  DBI->trace( 0 );
  $sth->finish();
}

my $lasth = $DBI::lasth;
DBI->trace( 99 );
warn join( ', ', sort( { $a <=> $b } keys( %{$lasth->FETCH( 'ParamValues' )} ) ) );
DBI->trace( 0 );

{
  my $sth = $dbh->prepare_cached( qq[SELECT foo FROM $TABLE_NAME WHERE foo = ?] );
  $sth->bind_param( 1, 'baz' );
  $sth->execute();

  DBI->trace( 99 );
  warn join( ', ', sort( { $a <=> $b } keys( %{$sth->{ParamValues}} ) ) );
  DBI->trace( 0 );
  $sth->finish();
}

$dbh->do( qq[DROP TABLE $TABLE_NAME] );

nets the expected results from each warn.

[sherrardb]

@Tux and another data point, which i think may be material:
i started poking around in the XS code and discovered that lasth() returns the inner handle, not the outer.

    if (type == '!') {  /* special case for $DBI::lasth */
        /* Currently we can only return the INNER handle.       */
        /* This handle should only be used for true/false tests */
        ST(0) = (imp_xxh) ? sv_2mortal(newRV_inc(DBI_LAST_HANDLE)) : &PL_sv_undef;
    }

and i was able to confirm that the inner handle and the return value of lasth() appear to be the same object and that the same behavior occurs if you get ParamValues from the inner handle.

#!/usr/bin/perl

$|++;

use strict;
use warnings;

use DBI qw();


my $dbh = DBI->connect( 'DBI:mysql:<USER>:<HOST>', '<DBNAME>', '<DBPASS>' );

my $TABLE_NAME = '_test_dbi_lasth';

$dbh->do( qq[CREATE TABLE $TABLE_NAME ( foo CHAR(5) )] );

my $inner;
{
  my $sth = $dbh->prepare_cached( qq[SELECT foo FROM $TABLE_NAME WHERE foo = ?] );
  $inner = ( DBI::_handles( $sth ) )[1];
  $sth->bind_param( 1, 'bar' );
  $sth->execute();

  DBI->trace( 99 );
  warn join( ', ', sort( { $a <=> $b } keys( %{$sth->{ParamValues}} ) ) );
  DBI->trace( 0 );
  $sth->finish();
}

DBI->trace( 99 );
warn join( ', ', keys( %{$inner->{ParamValues}} ) );
DBI->trace( 0 );

{
  my $sth = $dbh->prepare_cached( qq[SELECT foo FROM $TABLE_NAME WHERE foo = ?] );
  $sth->bind_param( 1, 'baz' );
  $sth->execute();

  DBI->trace( 99 );
  warn join( ', ', sort( { $a <=> $b } keys( %{$sth->{ParamValues}} ) ) );
  DBI->trace( 0 );
  $sth->finish();
}

$dbh->do( qq[DROP TABLE $TABLE_NAME] );

without knowing very much about the actual mechanics of what is happening in

        ST(0) = (imp_xxh) ? sv_2mortal(newRV_inc(DBI_LAST_HANDLE)) : &PL_sv_undef;

but based on my experimentation with the above line and extrapolating from the comment

        /* This handle should only be used for true/false tests */

it appears that we should not expect to be able to use ParamValues from the inner handle, as returned from lasth(). but none of that makes it clear why doing so creates brokenness for future usage of ParamValues from the outer handle.