View | Details | Raw Unified | Return to bug 8594
Collapse All | Expand All

(-)a/C4/Reports/Guided.pm (-5 / +38 lines)
Lines 413-423 sub select_2_select_count ($) { Link Here
413
    $sql =~ s/\bSELECT\W+(?:\w+\W+){1,}?FROM\b|\bSELECT\W\*\WFROM\b/SELECT count(*) FROM /ig;
413
    $sql =~ s/\bSELECT\W+(?:\w+\W+){1,}?FROM\b|\bSELECT\W\*\WFROM\b/SELECT count(*) FROM /ig;
414
    return $sql;
414
    return $sql;
415
}
415
}
416
sub strip_limit ($) {
416
417
    my $sql = shift or return;
417
# This removes the LIMIT from the query so that a custom one can be specified.
418
    ($sql =~ /\bLIMIT\b/i) or return ($sql, 0, undef);
418
# Usage:
419
    $sql =~ s/\bLIMIT\b\s*(\d+)(\s*\,\s*(\d+))?\s*/ /ig;
419
#   ($new_sql, $offset, $limit) = strip_limit($sql);
420
    return ($sql, (defined $2 ? $1 : 0), (defined $3 ? $3 : $1));   # offset can default to 0, LIMIT cannot!
420
#
421
# Where:
422
#   $sql is the query to modify
423
#   $new_sql is the resulting query
424
#   $offset is the offset value, if the LIMIT was the two-argument form,
425
#       0 if it wasn't otherwise given.
426
#   $limit is the limit value
427
#
428
# Notes:
429
#   * This makes an effort to not break subqueries that have their own
430
#     LIMIT specified. It does that by only removing a LIMIT if it comes after
431
#     a WHERE clause (which isn't perfect, but at least should make more cases
432
#     work - subqueries with a limit in the WHERE will still break.)
433
#   * If your query doesn't have a WHERE clause then all LIMITs will be
434
#     removed. This may break some subqueries, but is hopefully rare enough
435
#     to not be a big issue.
436
sub strip_limit {
437
    my ($sql) = @_;
438
439
    return unless $sql;
440
    return ($sql, 0, undef) unless $sql =~ /\bLIMIT\b/i;
441
442
    # Two options: if there's no WHERE clause in the SQL, we simply capture
443
    # any LIMIT that's there. If there is a WHERE, we make sure that we only
444
    # capture a LIMIT after the last one. This prevents stomping on subqueries.
445
    if ($sql !~ /\bWHERE\b/i) {
446
        (my $res = $sql) =~ s/\bLIMIT\b\s*(\d+)(\s*\,\s*(\d+))?\s*/ /ig;
447
        return ($res, (defined $2 ? $1 : 0), (defined $3 ? $3 : $1));
448
    } else {
449
        my $res = $sql;
450
        $res =~ m/.*\bWHERE\b/gsi;
451
        $res =~ s/\G(.*)\bLIMIT\b\s*(\d+)(\s*\,\s*(\d+))?\s*/$1 /is;
452
        return ($res, (defined $3 ? $2 : 0), (defined $4 ? $4 : $2));
453
    }
421
}
454
}
422
455
423
sub execute_query ($;$$$) {
456
sub execute_query ($;$$$) {
(-)a/t/db_dependent/Reports/Guided.t (-1 / +92 lines)
Line 0 Link Here
0
- 
1
# Copyright 2012 Catalyst IT Ltd.
2
#
3
# This file is part of Koha.
4
#
5
# Koha is free software; you can redistribute it and/or modify it under the
6
# terms of the GNU General Public License as published by the Free Software
7
# Foundation; either version 2 of the License, or (at your option) any later
8
# version.
9
#
10
# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
11
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
12
# A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
13
#
14
# You should have received a copy of the GNU General Public License along
15
# with Koha; if not, write to the Free Software Foundation, Inc.,
16
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
17
18
use strict;
19
use warnings;
20
21
use Test::More tests => 19;                      # last test to print
22
23
use_ok('C4::Reports::Guided');
24
25
# This is the query I found that triggered bug 8594.
26
my $sql = "SELECT aqorders.ordernumber, biblio.title, biblio.biblionumber, items.homebranch, 
27
    aqorders.entrydate, aqorders.datereceived, 
28
    (SELECT DATE(datetime) FROM statistics 
29
        WHERE itemnumber=items.itemnumber AND 
30
            (type='return' OR type='issue') LIMIT 1) 
31
    AS shelvedate, 
32
    DATEDIFF(COALESCE(
33
        (SELECT DATE(datetime) FROM statistics 
34
            WHERE itemnumber=items.itemnumber AND 
35
            (type='return' OR type='issue') LIMIT 1), 
36
    aqorders.datereceived), aqorders.entrydate) AS totaldays
37
FROM aqorders
38
LEFT JOIN biblio USING (biblionumber)
39
LEFT JOIN items ON (items.biblionumber = biblio.biblionumber 
40
    AND dateaccessioned=aqorders.datereceived)
41
WHERE (entrydate >= '2011-01-01' AND (datereceived < '2011-02-01' OR datereceived IS NULL))
42
    AND items.homebranch LIKE 'INFO'
43
ORDER BY title";
44
45
my ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
46
is($res_sql, $sql, "Not breaking subqueries");
47
is($res_lim1, 0, "Returns correct default offset");
48
is($res_lim2, undef, "Returns correct default LIMIT");
49
50
# Now the same thing, but we want it to remove the LIMIT from the end
51
52
my $test_sql = $res_sql . " LIMIT 242";
53
($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
54
# The replacement drops a ' ' where the limit was
55
is(trim($res_sql), $sql, "Correctly removes only final LIMIT");
56
is($res_lim1, 0, "Returns correct default offset");
57
is($res_lim2, 242, "Returns correct extracted LIMIT");
58
59
$test_sql = $res_sql . " LIMIT 13,242";
60
($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
61
# The replacement drops a ' ' where the limit was
62
is(trim($res_sql), $sql, "Correctly removes only final LIMIT (with offset)");
63
is($res_lim1, 13, "Returns correct extracted offset");
64
is($res_lim2, 242, "Returns correct extracted LIMIT");
65
66
# After here is the simpler case, where there isn't a WHERE clause to worry
67
# about.
68
69
# First case with nothing to change
70
$sql = "SELECT * FROM items";
71
($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
72
is($res_sql, $sql, "Not breaking simple queries");
73
is($res_lim1, 0, "Returns correct default offset");
74
is($res_lim2, undef, "Returns correct default LIMIT");
75
76
$test_sql = $sql . " LIMIT 242";
77
($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
78
is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case");
79
is($res_lim1, 0, "Returns correct default offset");
80
is($res_lim2, 242, "Returns correct extracted LIMIT");
81
82
$test_sql = $sql . " LIMIT 13,242";
83
($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
84
is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case (with offset)");
85
is($res_lim1, 13, "Returns correct extracted offset");
86
is($res_lim2, 242, "Returns correct extracted LIMIT");
87
88
sub trim {
89
    my ($s) = @_;
90
    $s =~ s/^\s*(.*?)\s*$/$1/s;
91
    return $s;
92
}

Return to bug 8594