Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.

Public Report
Report From: Delphi for PHP/Library    [ Add a report in this area ]  
Report #:  111804   Status: Open
The Database Component's "RecordCount" Property
Project:  HTML5 Builder Build #:  5.1.167.137
Version:    5.0 Submitted By:   Michael Ainsworth
Report Type:  Crash / Data loss / Total failure Date Reported:  1/8/2013 3:12:34 PM
Severity:    Critical / Show Stopper Last Updated: 1/19/2013 11:03:24 PM
Platform:    All platforms Internal Tracking #:   4994
Resolution: None (Resolution Comments) Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
Accessing the Database component's "RecordCount" property causes the component to re-execute the current query as a subquery.

E.g., the original query below:

    "SELECT * FROM members WHERE member_id IN (1, 2, 3, 4)"

produces something along the lines of:

    "SELECT COUNT(*) FROM (SELECT * FROM members WHERE member_id IN (1, 2, 3, 4)) AS subquery"

I've listed this issue as "Critical / Show Stopper" for several reasons.

1. If you're using PostgreSQL or another RDBMS that allows function calling in SELECT statements, this has potentially serious data integrity issues. E.g., calling "SELECT add_member_credit_card_charge(1)" twice is going to produce two credit card charge recordings for the user.  
2. This incurs a significant performance penalty for queries producing a large result set.
3. It won't work with some RDBMS and/or statement types. E.g., MySQL "CALL".
Steps to Reproduce:
1. Create a Database component and set up the connection parameters to point to a PostgreSQL database.
2. Create a Query component and link it to the database component.
3. Set the SQL of the Query component to a PostgreSQL function call that modifies data in some known way (e.g., incrementing a counter column).
4. In an area of code (form creation, button click, etc), execute the query, then access the RecordCount property. If you check your database, you'll see that the data has been modified twice.


---------------------------
<?php
require_once("rpcl/rpcl.inc.php");
//Includes
use_unit("forms.inc.php");
use_unit("extctrls.inc.php");
use_unit("stdctrls.inc.php");
use_unit("dbtables.inc.php");
use_unit("db.inc.php");

//Class definition
class Page2 extends Page
{
    public $Database1 = null;
    public $Query1 = null;
    public $Datasource1 = null;
    function Page2Create($sender, $params)
    {

        // Make sure you run: delete from thing_select

        header('Content-Type: text/plain');

        $this->Query1->first();
        do {
            echo $this->Query1->thing_id . ', ' . $this->Query1->name_ . "\n";
        } while ($this->Query1->NextRecord());

        echo "\nThe total number of records is: " . $this->Query1->RecordCount;
        echo "\nDid I say " . $this->Query1->RecordCount . "? I'll just check again... yes, it's " . $this->Query1->RecordCount;

        // Each time RecordCount is accessed above, a "select count(*) from ($sql) as rpclcountquery" is executed.
        // Run the following SQL: select * from thing_select
        // You'll see that there are *2* entries with a same-second timestamp.

        exit;

    }
}

global $application;

global $Page2;

//Creates the form
$Page2=new Page2($application);

//Read from resource file
$Page2->loadResource(__FILE__);

//Shows the form
$Page2->show();

?>
---------------------------
Workarounds
Call the built-in PHP "count()" function on the result set. Note that this workaround is easy to forget/miss and may not work in all situations.
Attachment
h5b_record_count.zip
Comments

Michael Ainsworth at 1/9/2013 8:26:23 PM -
Is this issue under investigation?

I just found another issue that manifested itself through this bug (although I haven't found the cause), whereby the SQL clause becomes empty. The error reported is this:

Application raised an exception class PDOException with message 'SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"
LINE 1: select count(*) from () as rpclcountquery
                              ^'

I'll post more information as/if I find it.

Tomohiro Takahashi at 1/9/2013 8:53:25 PM -
> Is this issue under investigation?
As you know, QualityCentral is just for reporting area.
If you need intensive support(including official reply) from Embarcadero, please contact technical support service first.
http://support.embarcadero.com/

Michael Ainsworth at 1/9/2013 9:05:01 PM -
The above comment is invalid. It was being reset somewhere in the code.

Please update me as you find a workaround to the above solution, as both our application code and the RPCL library code have $something->RecordCount scattered throughout the code.

Tomohiro Takahashi at 1/9/2013 9:16:57 PM -
Could you please attach sample project(including sample database/table schema and test data) to reproduce your issue?

Michael Ainsworth at 1/16/2013 3:46:01 PM -
I will attach a sample project in just a minute.

Michael Ainsworth at 1/16/2013 4:15:35 PM -
I have created the attachment. It consists of: a) an H5B project with a single unit; and b) a PostgreSQL dump of a database containing test data.

Follow the steps below:
1. Create/import the h5b_rc_bug database (you may need to create the h5b_test user and grant the appropriate table/function permissions - you may also need to change the database host, etc).
2. Delete everything from thing_select.
3. Run the page once.
4. Select everything from thing_select. You'll see that there are two records with a very close timestamp.

Note that the select_things() SQL function is simply a wrapper around "select * from thing" that adds a new record with a timestamp to the thing_select table.

Note also that accessing Query->RecordCount multiple times only re-issues the request as a subquery once, as in the example. I.e., the query is called once, the once again as a subquery, even though Query->Record count is called 3 times (one might expect the query to have been executed a total of 4 times, one for the original query, 3 for each attempt at accessing Query->RecordCount).

This issue is quite serious for the two issues stated in the OP:
1. Performance. We have a lot of queries with calls to RecordCount, so it's effectively doubling the execution time. RPCL is already slow without this bug.
2. Data integrity. I've just been rewriting code to prevent duplicate entries in one of the tables I'm working with. A bug of this nature in a financial product would be unacceptable.

The bug can be seen in dbpdo.inc.php (note that my line numbers may be slightly off due to the modifications I've made to RPCL).
* DBPDO::countSQL() on line 617.
* DBInterbase::countSQL() on line 789.
* DBOci::countSQL() on line 1265.
* DBSqlSrv::countSQL() on line 1541.

The actual function that executes this SQL is:
* DBPDO::count() on line 641.

I don't know if this should be classified as a "bug" or as something that has arisen from a lack of understanding of SQL and the built-in PHP database libraries.

Server Response from: ETNACODE01