v5.40's builtin false keyword cannot be passed in a bind parameter as the value of a boolean field

[akarelas]

This seems like it's a bad thing, because writing use v5.40; on a script doesn't let you override the true & false builtins afterwards by using something like use Mojo::JSON qw/ true false /;, nor with no builtin qw/ true false /, so we're all stuck with v5.40's builtin boolean values, and maybe you should consider this.

DBD::Pg thinks false is an empty string, and PostgreSQL returns an error:

My example script:

#!/usr/bin/env perl

use v5.40;
use FindBin '$RealBin';
use lib "$RealBin/local/lib/perl5";

use DBI;

my $dbh = DBI->connect('dbi:Pg:dbname=dbname;host=127.0.0.1', 'user', 'pass', {AutoCommit => 1});

$dbh->do('INSERT INTO "foo" ("value") VALUES (?)', undef, false);

Output:

DBD::Pg::db do failed: ERROR:  invalid input syntax for type boolean: ""
CONTEXT:  unnamed portal parameter $1 = '' at ./true_false.pl line 11.

[esabol]

I could be mistaken, as I'm just going by what I've read, but I don't think you need Perl v5.40 for this. use v5.36; use builtin qw(true false); and v5.38; use builtin qw(true false); should also work. I'm just mentioning this in case someone didn't know. 😄

[maros]

starting with 5.36, even !0 produces a native boolean value which would lead to this error when passed as a bind param.

perl -Mbuiltin=true,false,is_bool -E 'no warnings experimental::builtin; say "!!0 is ".is_bool(!!0); say "false is ".is_bool(false); say "0 is ".is_bool(0)'
!!0 is 1
false is 1
0 is

[karenetheridge]

You're not stuck with the builtin bools; you can explicitly pass JSON::PP::true as the bind parameter.

[akarelas]

you can explicitly pass JSON::PP::true as the bind parameter.

But typing JSON::PP::true (or a variable containing that value) is tedious. Would be much nicer to use the builtins, else would be easier to type int before true/false/a condition evaluating to builtin boolean.

If the maintainer of DBD::Pg does not intend to adapt to 5.40’s builtin booleans, I’ll just monkey-patch Mojo::Pg in my projects to achieve the desired result. But if I’m not mistaken this would be a welcome change in the Perl community. The ability to pass the native true & false as boolean field bind arguments.

[esabol]

If the maintainer of DBD::Pg does not intend to adapt to 5.40’s builtin booleans [...]

Maintaining DBD::Pg is nobody's full-time job. What we need is someone to submit a PR. I'm sure a PR would be most welcome by all. I say this strictly as an observer and proponent of this project who has contributed a couple PRs over the years.

[akarelas]

If the maintainer of DBD::Pg does not intend to adapt to 5.40’s builtin booleans [...]

What we need is someone to submit a PR.

I think the part that needs updating is written in C/XS or something like that. I can’t do that.

[esabol]

I can’t do that.

I didn't say "you." I said "someone." :smile: My point was that it's a community effort.

[akarelas]

For now I implememted a fix on my projects, by subclassing Mojo::Pg. That's good enough for me, for now.

[akarelas]

Maybe, to avoid breaking backwards-compatibility, there could be an Option, for converting built in booleans to Pg Booleans. And maybe another option also, for converting Pg Booleans to perl's built in booleans, when issuing a SELECT query!

[rabbiveesh]

I'm not actually convinced that this is a bug in DBD::Pg!

After trying to write a failing test, i discovered that when a bind param is set as SQL_BOOLEAN, core bools Just Work :tm:. So i would say it's the job of whatever query engine you're using to handle passing that to bind_param

[akarelas]

Either way, it would be good if this repo decided on a course (one way or another), so that after that we can press our query engines to adapt (if DBD::Pg doesn't).

[ilmari]

I think it would make sense to always bind native booleans as 0 and 1. It'd be a few lines' change in dbd_bind_ph.

[ilmari]

Note that using a "real" perl boolean false (e.g. !!0) as a postgres boolean has always failed, since it stringifies to an empty string:

$ perl -MDBI -le 'print $]; DBI->connect("dbi:Pg:")->selectrow_array("select ?::bool", undef, !!0)'
5.008009
DBD::Pg::db selectrow_array failed: ERROR:  invalid input syntax for type boolean: ""
CONTEXT:  unnamed portal parameter $1 = '' at -e line 1.

Since 5.36 we can at least detect it and bind it as 0 instead, but we can't defend against people using false from builtin::Backport on older versions of Perl.

[akarelas]

I believe binding false as 0 would be good enough for me.

[ilmari]

#129 implements my above suggestion, and adds tests and documentation.

[akarelas]

@ilmari Thanks, dude

[ilmari]

I just had a thought (and pushed a commit to do it, for discussion): should we respect pg_bool_tf and bind native booleans as t/f instead of 1/0 when it's set?

[esabol]

I just had a thought (and pushed a commit to do it, for discussion): should we respect pg_bool_tf and bind native booleans as t/f instead of 1/0 when it's set?

Seems reasonable to me. Thanks!

[esabol]

Thanks, @ilmari !