[DBAL-968] SQL Server modifyLimitQuery broken Created: 11/Aug/14  Updated: 11/Aug/14

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

Type: Bug Priority: Critical
Reporter: William Schaller Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: paginator, sqlserver
Environment:

SQL Server



 Description   

The recent change to SQLServerPlatform.php @jaylinski:improved sqlserver 'doModifyLimitQuery' select-from pattern broke the ORM Paginator's queries on SQL server.

I investigated, and found that some of the test cases for the SQL Server platform weren't actually correct SQL. Also, there were no test cases that covered what the paginator is doing, so I've written test cases for those. I will open a pull request for this issue.

The modifyLimitQuery method in SQLServerPlatform.php should be fixed to pass the fixed old tests and the new tests.

My concern is that that method is becoming too complex, but that's an issue for another day.



 Comments   
Comment by Marco Pivetta [ 11/Aug/14 ]

I'm gonna cry. Thank you, MSSQL, you make our lives so much "easier"





[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-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-713] MSSQL: Wrong Placement of "ROW_NUMBER() OVER" when using Subqueries in SELECT part Created: 19/Dec/13  Updated: 18/Feb/14  Resolved: 15/Feb/14

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

Type: Bug Priority: Major
Reporter: M.K. Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: sqlserver
Environment:

PHP 5.4.4 (with pdo_sqlsrv extension)
Windows 7



 Description   

I'm trying to create a DQL query like that:

SELECT Task.id AS id, Task.date AS date, (
	SELECT COUNT(p.posNr)
	FROM Project\Entity\Position p
	WHERE Task.id=p.ref
) AS poscount
FROM Project\Entity\Task Task
WHERE Task.id <> 0 AND Task.status < 3
ORDER BY Task.date DESC

This works flawlessly on MSSQL until i try to apply a LIMIT/OFFSET by using setFirstResult() and setMaxResults().
Applying a Limit results in an invoke of "doModifyLimitQuery()" in "Doctrine\DBAL\Platforms\SQLServerPlatform".

The function implementation clearly states what's wrong:

//Replace only first occurrence of FROM with $over to prevent changing FROM also in subqueries.
$over  = 'ORDER BY ' . implode(', ', $overColumns);
$query = preg_replace('/\sFROM\s/i', ", ROW_NUMBER() OVER ($over) AS doctrine_rownum FROM ", $query, 1);

This breaks support with subqueries in SELECT statements.



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

Yeah this seems to be indeed wrong. This method is going one step forward and one step back with each adjustment I don't feel comfortable trying to fix that one as it is really sensitive. But you are welcome to provide a patch for this on github (or someone else).

Comment by Tom Drissen [ 14/Jan/14 ]

I ran into this bug today, which is really annoying. Is there any change this will be fixed shortly?
How about checking parenthesises to determine the 'base' FROM/table?

Comment by Steve Müller [ 14/Jan/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/512

Comment by Doctrine Bot [ 08/Feb/14 ]

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

Comment by Steve Müller [ 15/Feb/14 ]

Fixed in commit: https://github.com/doctrine/dbal/commit/c4d4c5f2dbebab8a8a2cc40ca889c0e85362ad11

Comment by M.K. [ 18/Feb/14 ]

Important Notice:

The usage of preg_replace() in this bugfix can cause Apache to abort the PHP execution because of a stack overflow.
Apache's default value for "ThreadStackSize" on Windows is 1MB. This is not sufficient if you use preg_replace() on long queries.
I had to increase the size to 8MB.
This should be mentioned somewhere.

Comment by Marco Pivetta [ 18/Feb/14 ]

M.K. how long is the query with which you are experiencing this? I'd say it's more a PHP bug than problem of the DBAL

Comment by Steve Müller [ 18/Feb/14 ]

That might be an issue of using recursion in the regular expression maybe. I'm not sure if that is known limitation of PHP. But it seems weird to me you have to adjust apache config instead of PHP for this.

Comment by M.K. [ 18/Feb/14 ]

My Query is 1275 characters long.

It's not a bug at all, just a misconfiguration in Apache. But it's really hard to debug this problem, because Apache just kills PHP and doesn't say a word about it. So it would be kind to document this somewhere, so that new Doctrine Users can configure their Apache correctly.





[DBAL-559] SQL Server Platform error on LOCK MODE in cases of inheritance Created: 17/Jul/13  Updated: 08/Sep/13  Resolved: 08/Sep/13

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

Type: Bug Priority: Critical
Reporter: Fryderyk Benigni Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: sqlserver
Environment:

Windows Server, SQL Server 2008


Issue Links:
Duplicate
duplicates DDC-1624 Locking CTI doesnt work on SQL Server Resolved

 Description   

Whenever an inheritance is implemented the SQL Server Platform add a Lock Mode at the end of the query while for example on SQL Server 2008 it should add a lock on each joined object.

Current implementation of appendLockHint:

/**
 * {@inheritDoc}
 */
public function appendLockHint($fromClause, $lockMode)
{
    switch ($lockMode) {
        case LockMode::NONE:
            $lockClause = ' WITH (NOLOCK)';
            break;
        case LockMode::PESSIMISTIC_READ:
            $lockClause = ' WITH (HOLDLOCK, ROWLOCK)';
            break;
        case LockMode::PESSIMISTIC_WRITE:
            $lockClause = ' WITH (UPDLOCK, ROWLOCK)';
            break;
        default:
            $lockClause = '';
    }

    return $fromClause . $lockClause;
}

If the lock mode is added on each object the format of the query would be correct.



 Comments   
Comment by Benjamin Eberlei [ 08/Sep/13 ]

This is a known bug, see DDC-1624





[DBAL-497] SQLServerPlatform modifies limit query incorrectly when column names start with "from" Created: 18/Apr/13  Updated: 20/Apr/13  Resolved: 20/Apr/13

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

Type: Bug Priority: Major
Reporter: Shane Neuerburg Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: sqlserver
Environment:

OS X using FreeTDS driver, unixODBC
SQL Server 2008



 Description   

I am working with a table that has columns that start with the word "from" (fromNumber, fromCity, etc). When SQLServerPlatform->doModifyLimitQuery is called, "from" in the names are incorrectly replaced. I suggest adding a whitespace requirement after FROM in the preg_replace on this line:
https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php#L848

So this:

$query = preg_replace('/\sFROM/i', ", ROW_NUMBER() OVER ($over) AS doctrine_rownum FROM", $query);

Becomes this:

$query = preg_replace('/\sFROM\s/i', ", ROW_NUMBER() OVER ($over) AS doctrine_rownum FROM ", $query);


 Comments   
Comment by Shane Neuerburg [ 18/Apr/13 ]

Pull request:
https://github.com/doctrine/dbal/pull/306





[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: William 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 Sun Sep 21 18:16:31 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.