[DBAL-1187] [GH-827] Fix DISTINCT queries with limit and no order on SQL Server 2012 Created: 31/Mar/15  Updated: 08/Apr/15  Resolved: 08/Apr/15

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.6
Fix Version/s: 2.6
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: limit, mssql, offset, query, select-distinct, sql-server, sql-server-2012, sqlsrv


 Description   

This issue is created automatically through a Github pull request on behalf of zeroedin-bill:

Url: https://github.com/doctrine/dbal/pull/827

Message:

On SQLServer2012Platform, doModifyLimitQuery adds a do-nothing ORDER BY clause, because this is required in order to use OFFSET...FETCH NEXT N ROWS ONLY.

DISTINCT queries run via the paginator without an ORDER BY clause on SQL Server 2012 were failing with this error:

```
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
```

This PR fixes the error by adding 0 to the select list and changing the generated ORDER BY clause to read ORDER BY 1.

So a query that would have been generated as:
```sql
SELECT DISTINCT id_0
FROM (
SELECT p0_.id AS id_0
,p0_.preference_code AS preference_code_1
,p0_.id_zone AS id_zone_2
FROM preference p0_
WHERE (p0_.id_zone IN (2))
) dctrn_result
ORDER BY (SELECT 0)
OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY
```

Will now be generated as:
```sql
SELECT DISTINCT 0, id_0
FROM (
SELECT p0_.id AS id_0
,p0_.preference_code AS preference_code_1
,p0_.id_zone AS id_zone_2
FROM preference p0_
WHERE (p0_.id_zone IN (2))
) dctrn_result
ORDER BY 1
OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY
```



 Comments   
Comment by Doctrine Bot [ 08/Apr/15 ]

A related Github Pull-Request [GH-827] was merged:
https://github.com/doctrine/dbal/pull/827





[DBAL-1186] [GH-826] fix incorrect ordering of columns in clustered indexes on sql server Created: 31/Mar/15  Updated: 16/Apr/15  Resolved: 16/Apr/15

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: 2.6, 2.4.5, 2.5.2
Security Level: All

Type: Bug Priority: Minor
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: clustered, columns, index, mssql, order, sqlserver, sqlsrv


 Description   

This issue is created automatically through a Github pull request on behalf of zeroedin-bill:

Url: https://github.com/doctrine/dbal/pull/826

Message:

When schema information is fetched from SQL server, the getListTableIndexesSQL was fetching index columns ordered by sys.index_columns.index_column_id. This was incorrect, because that column is simply a unique id for the column within the index. The column ordering information is actually in the key_ordinal column.

This PR changes SQLServerPlatform to generate a resultset ordered by key_ordinal instead of index_column_id.

This was causing schema diffs on tables with composite primary keys to try to drop and re-create the indexes every time the diff is run.

So this solves index churn in schema diffs on SQL server.



 Comments   
Comment by Doctrine Bot [ 15/Apr/15 ]

A related Github Pull-Request [GH-826] was merged:
https://github.com/doctrine/dbal/pull/826





[DBAL-1158] Using alias with order by and then applying a limit causes an SQL invalid column name error Created: 02/Mar/15  Updated: 02/Mar/15

Status: Open
Project: Doctrine DBAL
Component/s: Drivers, Platforms
Affects Version/s: 2.5.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Karl Dawson Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, sqlsrv
Environment:

Windows Server 2012 with SQL Server 2012. Using PHP sqlsrv extension and SQL Native Client 11.



 Description   

I was originally having a problem where aliases were not working with order by/group by. I switched to 2.5.1 and this fixed the issue; however this led to another one. When applying a limit to a query using an alias in conjunction with order by, it generates the following error:

SQLSTATE [42S22, 207]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'sclr1'

See the following example:

$qb = $this->createQueryBuilder('u');

$select = array(
'u.title',
'SUM(u.seconds) AS total_seconds',
);

$qb->select($select)
->groupBy('u.title')
->orderBy('total_seconds', 'DESC')
->setMaxResults(5);

return $qb->getQuery()->getResult();

This will return the above error, but removing the setMaxResults(5) from the query will allow it to work.






[DBAL-1154] [GH-806] Fix broken functional test for SQL server Created: 23/Feb/15  Updated: 26/Feb/15  Resolved: 26/Feb/15

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: 2.6, 2.5.2
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: phpunit, sql-server, sqlsrv, testing, tests


 Description   

This issue is created automatically through a Github pull request on behalf of zeroedin-bill:

Url: https://github.com/doctrine/dbal/pull/806

Message:

Fixes default constraints test for SQL server – table name was wrong.



 Comments   
Comment by Doctrine Bot [ 26/Feb/15 ]

A related Github Pull-Request [GH-806] was merged:
https://github.com/doctrine/dbal/pull/806





[DBAL-1076] [GH-745] Added doModifyLimitQuery for SQLServer2012Platform that uses OFFSET... FETCH Created: 16/Dec/14  Updated: 24/Dec/14  Resolved: 24/Dec/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3, 2.4, 2.5
Fix Version/s: 2.6
Security Level: All

Type: Improvement Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: limit, offset, orderby, server, sql, sqlserver, sqlsrv


 Description   

This issue is created automatically through a Github pull request on behalf of zeroedin-bill:

Url: https://github.com/doctrine/dbal/pull/745

Message:

SQL Server 2012 introduced new syntax for paging records using the OFFSET... FETCH clause. See http://technet.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx for details on the specification.

This pull request:

  • Adds doModifyLimitQuery specific to SQLServer2012Platform, using OFFSET ... FETCH instead of ROW_NUMBER() OVER(blah blah)
  • Duplicates tests from SQLServerPlatformTest, using simpler OFFSET ... FETCH syntax

This version of doModifyLimitQuery will be much more robust than the one for SQLServer 2008 and below.



 Comments   
Comment by Doctrine Bot [ 24/Dec/14 ]

A related Github Pull-Request [GH-745] was closed:
https://github.com/doctrine/dbal/pull/745





[DBAL-940] ORDER BY with LIMIT in SQL Server does not work correctly Created: 17/Jul/14  Updated: 23/Oct/14  Resolved: 23/Oct/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: M.K. Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: limit, offset, orderBy, sqlsrv
Environment:

SQL Server


Issue Links:
Reference
relates to DBAL-927 [GH-622] improved sqlserver 'doModify... Resolved

 Description   

The function doModifyLimitQuery() in Doctrine\DBAL\Platforms\SQLServerPlatform does work correctly.

It removes the user generated ORDER BY (gets moved to OVER clause), but does not apply an ORDER BY on the row number created with ROW_NUMBER().

$orderBy = stristr($query, 'ORDER BY');

//Remove ORDER BY from $query (including nested parentheses in order by list).
$query = preg_replace('/\s+ORDER\s+BY\s+([^()]+|\((?:(?:(?>[^()]+)|(?R))*)\))+/i', '', $query);

$format  = 'SELECT * FROM (%s) AS doctrine_tbl WHERE doctrine_rownum BETWEEN %d AND %d';

The last format string should be:

$format  = 'SELECT * FROM (%s) AS doctrine_tbl WHERE doctrine_rownum BETWEEN %d AND %d ORDER BY doctrine_rownum';


 Comments   
Comment by Marco Pivetta [ 17/Jul/14 ]

Possible relation with DBAL-927

Comment by Steve Müller [ 23/Oct/14 ]

Fixed as of https://github.com/doctrine/dbal/commit/040d49c16a737d1349ae86443d64b3382cf4ce5d





[DBAL-833] [GH-542] [DBAL-825] Drop default constraints before altering column type on SQL Server Created: 07/Mar/14  Updated: 08/May/14  Resolved: 08/May/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Duplicate Votes: 0
Labels: sqlserver, sqlsrv

Issue Links:
Duplicate
duplicates DBAL-825 ALTER COLUMN on mssql is failing if d... Resolved
duplicates DBAL-826 [GH-536] [WIP] unit test for DBAL-825 Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/542

Message:

SQL Server implements column default values as constraints and therefore requires them to be dropped before a column type change.
This PR implements the recreation of default constraints on column type alterations.
Additionally some code in `SQLServerPlatform::getAlterTableSQL()` has been refactored to avoid code duplication and unnecessary SQL generation.
Please note that due to the issue's tests the PostgreSQL platform had to be altered to fulfill the same behaviour. PostgreSQL returned some strange default value like `666:smallint` when reverse engineering a column type change with a default value of `666` from type `smallint` to `integer`. So I think this PR fixes a bug in this platform, too. Additionally I had to change the deprecated default value retrieval SQL in PostgreSQL in order to work flawlessly. See the [documentation](http://www.postgresql.org/docs/9.3/static/catalog-pg-attrdef.html) at the very end.

I would also like to note that this PR is definitely not the end of the line concerning default values and column alterations. Some weird errors were revealed on other platforms while fixing this issue. MySQL for example denies default values on BLOB/TEXT type columns, DB2 just throws non-understandable syntax errors around and Oracle seems to map decimal/numeric types without a defined scale to integer when reverse engineering (not really related to this issue but it came up while testing).

Otherwise the tests pass on all platforms.



 Comments   
Comment by Doctrine Bot [ 07/Mar/14 ]

A related Github Pull-Request [GH-536] was closed:
https://github.com/doctrine/dbal/pull/536

Comment by Doctrine Bot [ 08/May/14 ]

A related Github Pull-Request [GH-542] was closed:
https://github.com/doctrine/dbal/pull/542

Comment by Steve Müller [ 08/May/14 ]

Fixed in commit: https://github.com/doctrine/dbal/commit/171a8762673ee61a89e3d6ce891cd2b475e7b5f7





[DBAL-830] [GH-539] unit test added for altering a column's default where the column name is... Created: 04/Mar/14  Updated: 16/Oct/14  Resolved: 10/Sep/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4.2
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: sqlsrv

Issue Links:
Reference
is referenced by DBAL-1010 [GH-693] [DBAL-1010] Fix renaming col... Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of DeepDiver1975:

Url: https://github.com/doctrine/dbal/pull/539

Message:

... a keyword - fails on mssql:

Exception : [Doctrine\DBAL\DBALException] An exception occurred while executing 'ALTER TABLE column_keyword_test DROP CONSTRAINT DF_D3D4D2F1_4BF2EAC0':

SQLSTATE [42000, 3728]: [Microsoft][SQL Server Native Client 11.0][SQL Server]'DF_D3D4D2F1_4BF2EAC0' is not a constraint.
SQLSTATE [42000, 3727]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not drop constraint. See previous errors.

With queries:
5. SQL: 'ALTER TABLE column_keyword_test DROP CONSTRAINT DF_D3D4D2F1_4BF2EAC0' Params: 
4. SQL: 'SELECT    col.name,
                          type.name AS type,
                          col.max_length AS length,
                          ~col.is_nullable AS notnull,
                          def.definition AS [default],
                          col.scale,
                          col.precision,
                          col.is_identity AS autoincrement,
                          col.collation_name AS collation,
                          CAST(prop.value AS NVARCHAR(MAX)) AS comment -- CAST avoids driver error for sql_variant type
                FROM      sys.columns AS col
                JOIN      sys.types AS type
                ON        col.user_type_id = type.user_type_id
                JOIN      sys.objects AS obj
                ON        col.object_id = obj.object_id
                JOIN      sys.schemas AS scm
                ON        obj.schema_id = scm.schema_id
                LEFT JOIN sys.default_constraints def
                ON        col.default_object_id = def.object_id
                AND       col.object_id = def.parent_object_id
                LEFT JOIN sys.extended_properties AS prop
                ON        obj.object_id = prop.major_id
                AND       col.column_id = prop.minor_id
                AND       prop.name = 'MS_Description'
                WHERE     obj.type = 'U'
                AND       (obj.name = 'column_keyword_test' AND scm.name = SCHEMA_NAME())' Params: 
3. SQL: 'ALTER TABLE column_keyword_test ADD CONSTRAINT DF_D3D4D2F1_ACF51D19 DEFAULT 23 FOR [select]' Params: 
2. SQL: 'CREATE TABLE column_keyword_test ([select] INT NOT NULL)' Params: 

Trace:
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Connection.php:988
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:971
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:612
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\SQLServerSchemaManager.php:232
C:\projects\doctrine\dbal\tests\Doctrine\Tests\DBAL\Functional\Schema\SchemaManagerFunctionalTestCase.php:619
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:976
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:831
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php:648
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:776
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:775
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:745
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php:349
C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php:176
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:268
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:506

#0 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(946): Doctrine\Tests\DbalFunctionalTestCase->onNotSuccessfulTest(Object(Doctrine\DBAL\DBALException))
#1 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php(648): PHPUnit_Framework_TestCase->runBare()
#2 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(776): PHPUnit_Framework_TestResult->run(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest))
#3 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(775): PHPUnit_Framework_TestCase->run(Object(PHPUnit_Framework_TestResult))
#4 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(745): PHPUnit_Framework_TestSuite->runTest(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest), Object(PHPUnit_Framework_TestResult))
#5 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php(349): PHPUnit_Framework_TestSuite->run(Object(PHPUnit_Framework_TestResult), false, Array, Array, false)
#6 C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php(176): PHPUnit_TextUI_TestRunner->doRun(Object(PHPUnit_Framework_TestSuite), Array)
#7 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(268): PHPUnit_TextUI_Command->run(Array, true)
#8 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(506): IDE_Base_PHPUnit_TextUI_Command::main()
#9 {main}


 Comments   
Comment by Doctrine Bot [ 02/Sep/14 ]

A related Github Pull-Request [GH-539] was closed:
https://github.com/doctrine/dbal/pull/539





[DBAL-826] [GH-536] [WIP] unit test for DBAL-825 Created: 03/Mar/14  Updated: 08/May/14  Resolved: 07/Mar/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Duplicate Votes: 0
Labels: sqlserver, sqlsrv

Issue Links:
Duplicate
duplicates DBAL-825 ALTER COLUMN on mssql is failing if d... Resolved
is duplicated by DBAL-833 [GH-542] [DBAL-825] Drop default cons... Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of DeepDiver1975:

Url: https://github.com/doctrine/dbal/pull/536

Message:

Here is the unit test for http://www.doctrine-project.org/jira/browse/DBAL-825

Let's see if I can come up with a solution as well ....



 Comments   
Comment by Doctrine Bot [ 07/Mar/14 ]

A related Github Pull-Request [GH-536] was closed:
https://github.com/doctrine/dbal/pull/536

Comment by Steve Müller [ 07/Mar/14 ]

Duplicated by DBAL-825 and addressed in DBAL-833

Comment by Doctrine Bot [ 08/May/14 ]

A related Github Pull-Request [GH-542] was closed:
https://github.com/doctrine/dbal/pull/542





[DBAL-482] SQL Server Schema Manager returns incorrect value for autoincrement on IDENTITY columns Created: 03/Apr/13  Updated: 01/May/13  Resolved: 01/May/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.2, 2.3, 2.3.3
Fix Version/s: 2.3.4
Security Level: All

Type: Bug Priority: Minor
Reporter: Bill Schaller Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: schematool, sqlserver, sqlsrv
Environment:

SQL Server



 Description   

When calculating table diffs, SQLServerSchemaManager returns column definitions for identity columns with _autoincrement set to FALSE.

This causes the schema update SQL generation to pump out a
ALTER TABLE x ALTER COLUMN id INT IDENTITY NOT NULL
for every single identity column in the schema.

The culprit is in DBAL\Schema\SQLServerSchemaManager, starting at line 43:

        $dbType = strtolower($tableColumn['TYPE_NAME']);
        $dbType = strtok($dbType, '(), ');

        $autoincrement = false;
        if (stripos($dbType, 'identity')) {
            $dbType = trim(str_ireplace('identity', '', $dbType));
            $autoincrement = true;
        }

When the column in question is an identity int column, the TYPE_NAME is "int identity". The second line of the snippet drops the "identity" signifier, causing the following lines that determine autoincrement to do nothing.

I simply moved the second line to below the autoincrement block ie:

        $dbType = strtolower($tableColumn['TYPE_NAME']);

        $autoincrement = false;
        if (stripos($dbType, 'identity')) {
            $dbType = trim(str_ireplace('identity', '', $dbType));
            $autoincrement = true;
        }

        $dbType = strtok($dbType, '(), ');

This change solves this issue for me, and as far as I can tell, has no other consequences.



 Comments   
Comment by Benjamin Eberlei [ 01/May/13 ]

Fixed for 2.3.4 and was fixed for 2.4 in a different way already.





[DBAL-422] Wrong VARCHAR default length in SQLServerPlatform Created: 24/Jan/13  Updated: 27/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Steve Müller Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: platform, sqlserver, sqlsrv, varchar


 Description   

In SQLServerPlatform the default length for a VARCHAR declaration is set to "255". But according to the SQLServer documentation from Microsoft the default length is "1", if omitted in the declaration.
See remarks: http://msdn.microsoft.com/en-us/library/ms186939.aspx
Also the default length is hardcoded in the "getVarcharTypeDeclarationSQLSnippet" method which in my opinion should be evaluated through "getVarcharDefaultLength".

I don't exactly know if the current implementation is intended, otherwise it should be fixed. I would then create an PR if desired.



 Comments   
Comment by Steve Müller [ 27/Dec/13 ]

The implementation is inconsistent on other platforms, too and can first be addressed in 3.0 I think. Otherwise this would be a BC break.





Generated at Thu May 28 22:36:12 UTC 2015 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.