Fix #16734 - Use SQL count instead of fetch and count results

Ref: #15652

Signed-off-by: William Desportes <williamdes@wdes.fr>

2 files changed, 255 insertions, 1 deletions

diff --git a/test/classes/SqlTest.php b/test/classes/SqlTest.php
index 8fa0d73915..c4ec86853d 100644
--- a/test/classes/SqlTest.php
+++ b/test/classes/SqlTest.php
@@ -314,7 +314,7 @@ class SqlTest extends AbstractTestCase
 
     /**
      * We can not say all the columns are from the same table if all the columns
-     * are funtion columns (table is '')
+     * are function columns (table is '')
      */
     public function testWithOnlyFunctionColumns(): void
     {
@@ -346,4 +346,221 @@ class SqlTest extends AbstractTestCase
 
         return $analyzedSqlResults;
     }
+
+    public function dataProviderCountQueryResults(): array
+    {
+        // sql query
+        // session tmpval
+        // num rows
+        // result
+        // just browsing
+        return [
+            [
+                'SELECT * FROM company_users WHERE id != 0 LIMIT 0, 10',
+                ['max_rows' => 250],
+                -1,
+                -1,
+            ],
+            [
+                'SELECT * FROM company_users WHERE id != 0',
+                [
+                    'max_rows' => 250,
+                    'pos' => -1,
+                ],
+                -1,
+                -2,
+            ],
+            [
+                'SELECT * FROM company_users WHERE id != 0',
+                [
+                    'max_rows' => 250,
+                    'pos' => -1,
+                ],
+                -1,
+                -2,
+            ],
+            [
+                'SELECT * FROM company_users WHERE id != 0',
+                [
+                    'max_rows' => 250,
+                    'pos' => 250,
+                ],
+                -1,
+                249,
+            ],
+            [
+                'SELECT * FROM company_users WHERE id != 0',
+                [
+                    'max_rows' => 250,
+                    'pos' => 4,
+                ],
+                2,
+                6,
+            ],
+            [
+                'SELECT * FROM company_users WHERE id != 0',
+                [
+                    'max_rows' => 'all',
+                    'pos' => 4,
+                ],
+                2,
+                2,
+            ],
+            [
+                null,
+                [],
+                2,
+                0,
+            ],
+            [
+
+                'SELECT * FROM company_users LIMIT 1,4',
+                [
+                    'max_rows' => 10,
+                    'pos' => 4,
+                ],
+                20,
+                20,
+
+            ],
+            [
+
+                'SELECT * FROM company_users',
+                [
+                    'max_rows' => 10,
+                    'pos' => 4,
+                ],
+                20,
+                4,
+            ],
+            [
+
+                'SELECT * FROM company_users WHERE not_working_count != 0',
+                [
+                    'max_rows' => 10,
+                    'pos' => 4,
+                ],
+                20,
+                0,
+            ],
+            [
+
+                'SELECT * FROM company_users WHERE working_count = 0',
+                [
+                    'max_rows' => 10,
+                    'pos' => 4,
+                ],
+                20,
+                15,
+
+            ],
+            [
+                'UPDATE company_users SET a=1 WHERE working_count = 0',
+                [
+                    'max_rows' => 10,
+                    'pos' => 4,
+                ],
+                20,
+                20,
+            ],
+            [
+                'UPDATE company_users SET a=1 WHERE working_count = 0',
+                [
+                    'max_rows' => 'all',
+                    'pos' => 4,
+                ],
+                20,
+                20,
+            ],
+            [
+                'UPDATE company_users SET a=1 WHERE working_count = 0',
+                ['max_rows' => 15],
+                20,
+                20,
+            ],
+            [
+                'SELECT * FROM company_users WHERE id != 0',
+                [
+                    'max_rows' => 250,
+                    'pos' => 4,
+                ],
+                2,
+                6,
+                true,
+            ],
+            [
+                'SELECT *, (SELECT COUNT(*) FROM tbl1) as c1, (SELECT 1 FROM tbl2) as c2 '
+                . 'FROM company_users WHERE id != 0',
+                [
+                    'max_rows' => 250,
+                    'pos' => 4,
+                ],
+                2,
+                6,
+                true,
+            ],
+            [
+
+                'SELECT * FROM company_users',
+                [
+                    'max_rows' => 10,
+                    'pos' => 4,
+                ],
+                20,
+                18,
+                true,
+            ],
+            [
+                'SELECT *, 1, (SELECT COUNT(*) FROM tbl1) as c1, '
+                . '(SELECT 1 FROM tbl2) as c2 FROM company_users WHERE subquery_case = 0',
+                [
+                    'max_rows' => 10,
+                    'pos' => 4,
+                ],
+                20,
+                42,
+
+            ],
+            [
+                'SELECT ( as c2 FROM company_users WHERE working_count = 0',// Invalid query
+                ['max_rows' => 10],
+                20,
+                20,
+
+            ],
+        ];
+    }
+
+    /**
+     * @dataProvider dataProviderCountQueryResults
+     */
+    public function testCountQueryResults(
+        ?string $sqlQuery,
+        array $sessionTmpVal,
+        int $numRows,
+        int $expectedNumRows,
+        bool $justBrowsing = false
+    ): void {
+        if ($justBrowsing) {
+            $GLOBALS['cfg']['Server']['DisableIS'] = true;
+        }
+
+        $_SESSION['tmpval'] = $sessionTmpVal;
+
+        $analyzed_sql_results = $sqlQuery === null ? [] : $this->parseAndAnalyze($sqlQuery);
+
+        $result = $this->callFunction(
+            $this->sql,
+            Sql::class,
+            'countQueryResults',
+            [
+                $numRows,
+                $justBrowsing,
+                'my_dataset',// db
+                'company_users',// table
+                $analyzed_sql_results,
+            ]
+        );
+        $this->assertSame($expectedNumRows, $result);
+    }
 }
diff --git a/test/classes/Stubs/DbiDummy.php b/test/classes/Stubs/DbiDummy.php
index 98c6518c57..447245585e 100644
--- a/test/classes/Stubs/DbiDummy.php
+++ b/test/classes/Stubs/DbiDummy.php
@@ -949,6 +949,11 @@ class DbiDummy implements DbiExtension
             ],
             [
                 'query'  => 'SELECT TABLE_NAME FROM information_schema.VIEWS'
+                    . ' WHERE TABLE_SCHEMA = \'my_dataset\' AND TABLE_NAME = \'company_users\'',
+                'result' => [],
+            ],
+            [
+                'query'  => 'SELECT TABLE_NAME FROM information_schema.VIEWS'
                     . ' WHERE TABLE_SCHEMA = \'my_db\' '
                     . 'AND TABLE_NAME = \'test_tbl\' AND IS_UPDATABLE = \'YES\'',
                 'result' => [],
@@ -1575,6 +1580,10 @@ class DbiDummy implements DbiExtension
                 'result' => [],
             ],
             [
+                'query'  => "SHOW TABLE STATUS FROM `my_dataset` WHERE `Name` LIKE 'company\_users%'",
+                'result' => [],
+            ],
+            [
                 'query'  => 'SELECT *, `TABLE_SCHEMA` AS `Db`, `TABLE_NAME` AS `Name`,'
                 . ' `TABLE_TYPE` AS `TABLE_TYPE`, `ENGINE` AS `Engine`,'
                 . ' `ENGINE` AS `Type`, `VERSION` AS `Version`, `ROW_FORMAT` AS `Row_format`,'
@@ -2395,6 +2404,34 @@ class DbiDummy implements DbiExtension
                     (object) ['type' => 'string'],
                 ],
             ],
+            [
+                'query' => 'SELECT COUNT(*) FROM company_users WHERE not_working_count != 0',
+                'result' => false,
+            ],
+            [
+                'query' => 'SELECT COUNT(*) FROM company_users',
+                'result' => [
+                    [4],
+                ],
+            ],
+            [
+                'query' => 'SELECT COUNT(*) FROM company_users WHERE working_count = 0',
+                'result' => [
+                    [15],
+                ],
+            ],
+            [
+                'query' => 'SELECT COUNT(*) FROM `my_dataset`.`company_users`',
+                'result' => [
+                    [18],
+                ],
+            ],
+            [
+                'query' => 'SELECT COUNT(*) FROM company_users WHERE subquery_case = 0',
+                'result' => [
+                    [42],
+                ],
+            ],
         ];
         /**
          * Current database.