| Subject: | Incomplete query sent to DB when query contains incorrect characters (1.49) |
| Date: | Mon, 17 Mar 2008 11:37:55 +0300 |
| To: | bug-DBD-Pg [...] rt.cpan.org |
| From: | Roman Mashirov <mrj [...] nandu.ru> |
While sending Postgres via selectrow_array query with incorrect
characters in it only part of the query sent to DB. As a result query
without whole 'where' part runned on DB, so got join of two large tables
without condition and very long execution time of the query.
The query is:
select count(*)
from product p, search_result, main_price mp, stock s where visible='t'
and main_product is null and state <> 2 and product_id=object and
search=? and mp.product=p.product_id and mp.price>=3000 and
mp.price<=8000 and s.product=product_id and arrived='f' and order_id is
null and amount>=200♦ and catalogue in (select catalogue from
catalogue_access where visitor_class in (6))
Codes after 'amount>=200' is 'e2 99 a6'
Calling with following script:
#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect('dbi:Pg:dbname=cms2 user=mrj', 'mrj', '');
open FILE, '<', '/tmp/sqlq';
local $/;
my $a = <FILE>;
close FILE;
$dbh->trace(5);
$dbh->selectrow_array($a, undef, 167788);
Got following trace:
perl tt.pl
DBI::db=HASH(0x822ab2c) trace level set to 0x0/5 (DBI @ 0x0/0) in DBI
1.50-ithread (pid 7184)
Note: perl is running without the recommended perl -w option
-> selectrow_array for DBD::Pg::db (DBI::db=HASH(0x82296b0)~0x822ab2c
'select count(*)
from product p, search_result, main_price mp, stock s where visible='t'
and main_product is null and state <> 2 and product_id=object and
search=? and mp.product=p.product_id and mp.price>=3000 and
mp.price<=8000 and s.product=product_id and arrived='f' and order_id is
null and amount>=200�.� and catalogue in (select catalogue from
catalogue_access where visitor_class in ...' undef 167788) thr#804bd00
1 -> prepare for DBD::Pg::db (DBI::db=HASH(0x822ab2c)~INNER 'select count(*)
from product p, search_result, main_price mp, stock s where visible='t'
and main_product is null and state <> 2 and product_id=object and
search=? and mp.product=p.product_id and mp.price>=3000 and
mp.price<=8000 and s.product=product_id and arrived='f' and order_id is
null and amount>=200�.� and catalogue in (select catalogue from
catalogue_access where visitor_class in ...' undef) thr#804bd00
dbih_setup_handle(DBI::st=HASH(0x822ac64)=>DBI::st=HASH(0x822add8),
DBD::Pg::st, 822ac70, Null!)
dbih_make_com(DBI::db=HASH(0x822ab2c), 822aed0, DBD::Pg::st, 172, 0)
thr#804bd00
dbih_setup_attrib(DBI::st=HASH(0x822add8), Err, DBI::db=HASH(0x822ab2c))
SCALAR(0x815d7f4) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x822add8), State,
DBI::db=HASH(0x822ab2c)) SCALAR(0x815d854) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x822add8), Errstr,
DBI::db=HASH(0x822ab2c)) SCALAR(0x815d824) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x822add8), TraceLevel,
DBI::db=HASH(0x822ab2c)) 5 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x822add8), FetchHashKeyName,
DBI::db=HASH(0x822ab2c)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x822add8), HandleSetErr,
DBI::db=HASH(0x822ab2c)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0x822add8), HandleError,
DBI::db=HASH(0x822ab2c)) undef (not defined)
dbdpg: dbd_st_prepare (select count(*)
from product p, search_result, main_price mp, stock s where visible='t'
and main_product is null and state <> 2 and product_id=object and
search=? and mp.product=p.product_id and mp.price>=3000 and
mp.price<=8000 and s.product=product_id and arrived='f' and order_id is
null and amount>=200♦ and catalogue in (select catalogue from
catalogue_access where visitor_class in (6))
)
dbdpg: dbd_st_split_statement
dbdpg: Created segment (select count(*)
from product p, search_result, main_price mp, stock s where visible='t'
and main_product is null and state <> 2 and product_id=object and search=)
1 <- prepare= DBI::st=HASH(0x822ac64) at tt.pl line 17
dbdpg: dbd_bind_ph ph_name: (1) newvalue: 167788(16842752)
dbdpg: Bind (1) <== (167788) (type=0)
dbdpg: dbd_st_execute
dbdpg: PQexec
dbdpg: Running PQexec with (select count(*)
from product p, search_result, main_price mp, stock s where visible='t'
and main_product is null and state <> 2 and product_id=object and
search='167788')
Just after last row BD starts to work with truncated query and server
loads rises up.