Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

DBI: when 1 != '1'

by IlyaM (Parson)
on Sep 27, 2002 at 09:49 UTC ( #201146=perlmeditation: print w/replies, xml ) Need Help??

Funnily enough nearly all my meditations are inspired by very weird bugs I've found in my or others code. Today I found that code which worked on my home computer just fine didn't work in production. An the fix was simple as replacing 1 with '1'. Now it would take long time to describe the mechanics of that bug but I would like to share with other monks my knowledge about situations when 1 != '1' in Perl.

One of the first things Perl newbies learn is that there is very little difference between numbers and strings in Perl. In most cases you can use 'NNN' and NNN (where NNN is a number) interchangeably in your Perl code. But concerning Perl internals there is a difference: it stores numbers and strings differently and it is possible to distinguish them on the level of internals. Moreover there exist some tricks to check if variable contains a number or a string without resorting to writing C code.

Still usually Perl programmers do not see any difference between numbers and strings but there is very important exception - DBI. When you use placeholders in DBI types of variables may affects how it generates SQL queries. Simple example:

my $sth = $dbh->prepare('SELECT name FROM user WHERE id = ?'); # generates query "SELECT name FROM user WHERE id = 1" $sth->execute(1); # generates query "SELECT name FROM user WHERE id = '1'" $sth->execute('1');
Well, with most databases and with most field types these two queries are treated as same. However it is not always true - for example if our database is MySQL and field 'id' has 'enum' type these two queries are very different.

Another (a bit MySQL specific) example when the difference between 1 and '1' is critical:

my $sth = $dbh->prepare('SELECT name FROM user LIMIT ?'); # generates query "SELECT name FROM user LIMIT 1" $sth->execute(1); # generates ILLEGAL query "SELECT name FROM user LIMIT '1'" $sth->execute('1');

--
Ilya Martynov (http://martynov.org/)

Replies are listed 'Best First'.
Re: DBI: when 1 != '1'
by blakem (Monsignor) on Sep 27, 2002 at 10:42 UTC
    I've run across the same thing in DBI with postgresql's bool type. It has lead to some really odd looking code:
    sub is_purple { ... return $color eq 'purple' ? '1' : '0'; }
    The return value is destined to be bound into a SQL statement, and a more natural set of true/false values simply doesn't work. In most cases, I would redflag a line such as that, but in this one weird case it actually makes sense.

    -Blake

Re: DBI: when 1 != '1'
by tommyw (Hermit) on Sep 27, 2002 at 10:31 UTC

    I'll pass on the ability of Perl to distinguish between 1 and '1'. To my knowledge there's no way to separate them, hence all the questions about using a regex to determine whether a variable contains a number or not.

    Anyway... Oracle (about which I know) certainly has a problem when you pass it queries such as SELECT name FROM user WHERE id = '1', if id is a numeric value. Every other language will also have the same problem, since it's trying to compare a number with a string.

    Oracle's rule used to be (but it's changed now, and I'm not entirely sure what to) that the left-hand value got converted to the type of the right-hand value before the comparison is made. So, if you coded id = '1.0' the test would always fail (as the id 1 would get converted to '1'), whereas '1.0' = id would suceed (as '1.0' would be converted to 1).

    This caused some problem with writing general comparisons across different types: if a is numeric and b is a string, then a = b could fail (as above). However, b = a could actually cause a database error if b ever ended up holding a value which it was not possible to interpret as a numeric value.

    --
    Tommy
    Too stupid to live.
    Too stubborn to die.

      In Oracle:
      create table rdf_test(v1 number); insert into rdf_test values(1); select count(*) from rdf_test where v1 = 1; COUNT(*) ======== 1 select count(*) from rdf_test where v1 = '1'; COUNT(*) ======== 1 select count(*) from rdf_test where v1 = '1.0'; COUNT(*) ======== 1

      rdfield

        Yes, it look's like the string parameter gets converted to a number. Told you it had changed. ;-)

        Which means that

        SQL> select count(*) from rdf_test where '1.0' = v1; COUNT(*) ---------- 1 SQL> select count(*) from rdf_test where 'a' = v1; select count(*) from rdf_test where 'a' = v1 * ERROR at line 1: ORA-01722: invalid number SQL> select count(*) from rdf_test where v1 = 'a'; select count(*) from rdf_test where v1 = 'a' * ERROR at line 1: ORA-01722: invalid number

        Obviously the fix is to convert the number into a string (since the exception can't be generated then). Thus,

        1* select count(*) from rdf_test where 'a' = TO_CHAR(v1) SQL> / COUNT(*) ---------- 0
        Great. It's working fine now. But...
        SQL> select count(*) from rdf_test where '1.0' = TO_CHAR(v1); COUNT(*) ---------- 0
        which leaves it still being a headache.

        --
        Tommy
        Too stupid to live.
        Too stubborn to die.

      I'll pass on the ability of Perl to distinguish between 1 and '1'. To my knowledge there's no way to separate them
      But there is: for bitwise operators like "&" and "|", strings behave differently than numbers. If one is a number, both will be treated as a number. For example
      $\ = "\n"; for $a (1, "1") { print $a | " $a"; }
      will produce a different result for 1 and for "1".

      Coming back to DBI: It doesn't make this kind of distinction IIRC. Anything is a string.

      Binding placeholders to a variable type can help. I don't know for sure for enumerated types in MyQSL, I can't test that (easily), but I do recall that DBI appears to make a guess on a field type based on the first value you feed it. If it looks like a number, DBI will treat the column as a number, which will produce an error if the field contains a non-numerical value for one of the next rows. I think even that if the first value is undef, DBI treats it as a string... And various MS databases do mind, even if MySQL doesn't.

      This prevents that:

      use DBI ':sql_types'; my $dbh = DBI->connect(...); my $sth = $dbh->prepare("SELECT * FROM $table WHERE $field = ?"); $sth->bind_param(1, undef, SQL_VARCHAR); # or SQL_CHAR? untested. # For numeric types, experiment with SQL_NUMERIC or SQL_INTEGER ... # Now go ahead, DBI will treat any data for the (first) field as a str +ing...

      p.s. Disclaimer: this is all based on my memory of experiences of a few years ago. Please don't hold it too much against me if it contains a few minor errors.

Re: DBI: when 1 != '1'
by diotalevi (Canon) on Sep 27, 2002 at 15:55 UTC

    Interesting... very interesting. So what happens for values that are both numbers and strings? I just ran your is_number() trick against a string/number variable (actually a PVIV) and it correctly reported that there it's a number... it just also happens to be a string.

    use Devel::Peek; $a = 0; scalar "$a"; Dump($a); # prove it's a PVIV with IOK and POK set print is_number($a); # returns true sub is_number { ( $_[0] & ~ $_[0] ) eq '0' }

      The ~ operator has to decide whether to treat the scalar as a string or as a numeric. I would say it makes a lot of sense to prefer numeric treatment whenever that flag's set - as it's likely that a number that's also string is really a stringified number, and not that likely that it's a numified string.

      So long as any XS or otherwise non-Perl code plays by the same rule, you're not going to run into trouble with that situation.

      Makeshifts last the longest.

Re: DBI: when 1 != '1'
by thraxil (Prior) on Sep 27, 2002 at 14:43 UTC

    ah. that explains it.

    at work we use postgres on the dev machines and DB2 on the production server. i encountered this a few times where postgres would have no problem and DB2 would give me a cryptic error until i changed a parameter from $string to "$string". i decided that it was best for my sanity if i just forgot about it and buried the ugliness with a little help from DBIx::AnyDBD.

    anders pearson

Re: DBI: when 1 != '1'
by tadman (Prior) on Sep 28, 2002 at 00:46 UTC
    I spent a while chasing down a "bug" related to this, which I related in DBD::mysql Unusual Behavior, and thankfully solved by Re^2: DBD::mysql Unusual Behavior. It's one of those things that I really wish they would fix. I've patched the driver here so it works, but I have no idea how much collective head is being banged against metaphorical wall out there. Surely I'm not alone.

    Is there any way to tell DBI how to quote things? It's really quite awful at guessing sometimes.
      Is there any way to tell DBI how to quote things? It's really quite awful at guessing sometimes.

      In theory one can use $sth->bind_param to tell type which has variable to be inserted in place of placeholder so DBI has a chance to do proper quoting. In reality I'm not sure that it works for anything other than Oracle. At least I've never seen it being used with DBI drivers other than DBD::Oracle.

      --
      Ilya Martynov (http://martynov.org/)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://201146]
Approved by broquaint
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (10)
As of 2019-10-21 22:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?