doctrine / dbal

    /**

     * @group DBAL-713

     */

    public function testModifyLimitQueryWithSubSelectInSelectList()

    {

        $querySql = "SELECT " .

            "u.id, " .

            "(u.foo/2) foodiv, " .

            "CONCAT(u.bar, u.baz) barbaz, " .

            "(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " .

            "FROM user u " .

            "WHERE u.status = 'disabled'";

        $alteredSql = "SELECT TOP 10 " .

            "u.id, " .

            "(u.foo/2) foodiv, " .

            "CONCAT(u.bar, u.baz) barbaz, " .

            "(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " .

            "FROM user u " .

            "WHERE u.status = 'disabled'";

        $sql = $this->_platform->modifyLimitQuery($querySql, 10);

        self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql);

    }

    /**

     * @group DBAL-713

    /**

     * @group DBAL-834

     */

    public function testModifyLimitQueryWithAggregateFunctionInOrderByClause()

    {

        $querySql = "SELECT " .

            "MAX(heading_id) aliased, " .

            "code " .

            "FROM operator_model_operator " .

            "GROUP BY code " .

            "ORDER BY MAX(heading_id) DESC";

        $alteredSql = "SELECT TOP 1 " .

            "MAX(heading_id) aliased, " .

            "code " .

            "FROM operator_model_operator " .

            "GROUP BY code " .

            "ORDER BY MAX(heading_id) DESC";

        $sql = $this->_platform->modifyLimitQuery($querySql, 1, 0);

        self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 1), $sql);

    }

    /**

     * @throws \Doctrine\DBAL\DBALException

    /**

     * @throws \Doctrine\DBAL\DBALException

     */

    public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnFromBaseTable()

    {

        $querySql = "SELECT DISTINCT id_0, name_1 "

            . "FROM ("

            . "SELECT t1.id AS id_0, t2.name AS name_1 "

            . "FROM table_parent t1 "

            . "LEFT JOIN join_table t2 ON t1.id = t2.table_id "

            . "ORDER BY t1.id ASC"

            . ") dctrn_result "

            . "ORDER BY id_0 ASC";

        $alteredSql = "SELECT DISTINCT TOP 5 id_0, name_1 "

            . "FROM ("

            . "SELECT t1.id AS id_0, t2.name AS name_1 "

            . "FROM table_parent t1 "

            . "LEFT JOIN join_table t2 ON t1.id = t2.table_id"

            . ") dctrn_result "

            . "ORDER BY id_0 ASC";

        $sql = $this->_platform->modifyLimitQuery($querySql, 5);

        self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 5), $sql);

    }

    /**

    /**

     * @throws \Doctrine\DBAL\DBALException

     */

    public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnFromJoinTable()

    {

        $querySql = "SELECT DISTINCT id_0, name_1 "

            . "FROM ("

            . "SELECT t1.id AS id_0, t2.name AS name_1 "

            . "FROM table_parent t1 "

            . "LEFT JOIN join_table t2 ON t1.id = t2.table_id "

            . "ORDER BY t2.name ASC"

            . ") dctrn_result "

            . "ORDER BY name_1 ASC";

        $alteredSql = "SELECT DISTINCT TOP 5 id_0, name_1 "

            . "FROM ("

            . "SELECT t1.id AS id_0, t2.name AS name_1 "

            . "FROM table_parent t1 "

            . "LEFT JOIN join_table t2 ON t1.id = t2.table_id"

            . ") dctrn_result "

            . "ORDER BY name_1 ASC";

        $sql = $this->_platform->modifyLimitQuery($querySql, 5);

        self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 5), $sql);

    }

    /**

     * @throws \Doctrine\DBAL\DBALException

    /**

     * @throws \Doctrine\DBAL\DBALException

     */

    public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnsFromBothTables()

    {

        $querySql = "SELECT DISTINCT id_0, name_1, foo_2 "

            . "FROM ("

            . "SELECT t1.id AS id_0, t2.name AS name_1, t2.foo AS foo_2 "

            . "FROM table_parent t1 "

            . "LEFT JOIN join_table t2 ON t1.id = t2.table_id "

            . "ORDER BY t2.name ASC, t2.foo DESC"

            . ") dctrn_result "

            . "ORDER BY name_1 ASC, foo_2 DESC";

        $alteredSql = "SELECT DISTINCT TOP 5 id_0, name_1, foo_2 "

            . "FROM ("

            . "SELECT t1.id AS id_0, t2.name AS name_1, t2.foo AS foo_2 "

            . "FROM table_parent t1 "

            . "LEFT JOIN join_table t2 ON t1.id = t2.table_id"

            . ") dctrn_result "

            . "ORDER BY name_1 ASC, foo_2 DESC";

        $sql = $this->_platform->modifyLimitQuery($querySql, 5);

        self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 5), $sql);

    }

    public function testModifyLimitSubquerySimple()

    {