[DDC-3364] QueryBuilder fails when using alias in having with like expr Created: 21/May/14  Updated: 26/Oct/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: webDEVILopers Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: GROUP_CONCAT, dql, expression, having, like, mysql
Environment:

Debian, MySQL, PHP, Zend Framework 2, Doctrine Module, Doctrine Extensions



 Description   

In my select I create an alias. I use a like expr on this alias in the having clause.

Trying several variations I still get the following error:
Expected '.' or '(', got 'long_name'

My example including variations inside comments:

$having = $qb->expr()->like('long_name', $qb->expr()->literal('%' . $term . '%'));

$qb->select(array(
    'b.id', 'b.name AS branch_name',
    'c.name AS company_name',
    'GroupConcat(b.name, \', \', c.name) AS long_name' // same for 'c.name AS long_name'
))
->from('Application\Entity\Branch', 'b')
->join('b.company', 'c')
->where('b.compartment = 1')
->having('(' . $having . ')') // same for having($having)
->groupBy('c.id')
->orderBy('c.name')
->addOrderBy('b.name');

I use a Doctrine Extension for the MySQL GROUP_CONCAT functionality:
https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/GroupConcat.php

This should have no effect on the result since I tried a simple alias - see comment - instead of it.

The problem seems to be using an alias inside the having clause when adding the like expr.
The following clause would work:

$having = $qb->expr()->like('c.name', $qb->expr()->literal('%' . $term . '%'));

I also tried this workaround using the GROUP_CONCAT method inside the where part:

->andWhere($qb->expr()->like('GroupConcat(b.name, \', \', c.name)', $qb->expr()->literal('%' . $term . '%')))

Allthough I used the groupBy part I got this error:
General error: 1111 Invalid use of group function



 Comments   
Comment by Marco Pivetta [ 26/Jun/14 ]

What is the actual failure/exception type? What about the generated SQL?

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

Moved to ORM, the error and use case is ORM related.





[DDC-3308] Cross platform support for DQL "WHERE ... IN" with multiple fields/columns Created: 13/Sep/14  Updated: 14/Sep/14

Status: Awaiting Feedback
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.4.2
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Minor
Reporter: Markus Wößner Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql, mysql, sqlite


 Description   

DQL does not support "WHERE ... IN" statements which refer to more than one field.

Example: While

... WHERE e.id IN (1, 2) ...

is valid DQL

... WHERE (e.id, e.name) IN ((1, "amy"), (2, "fred")) ...

is not.

This was discussed some years ago here https://groups.google.com/forum/#!msg/doctrine-user/bE9RfiF4ZGk/vaiEvsX5_rwJ and it appears that it is not SQL-99. Sqlite does not support it, MySQL does.

As http://sqlfiddle.com/#!7/6169b/1 shows it is not a big deal to transform such a query into an equal query which prevents the usage of "WHERE ... IN" constraints at all. I guess the case "pair(a,b) is not unique" can be safely ignored if redundant pairs are skipped on temp table insertion.



 Comments   
Comment by Marco Pivetta [ 14/Sep/14 ]

This looks everything but trivial to me, especially considering that it introduces usage of UNION, which we also do not support because of portability rules.

Comment by Markus Wößner [ 14/Sep/14 ]

As far as I understand the UNION statement would only be needed when doing an "inline table" approach.

Isn't it a usual technique in Doctrine "AST to SQL output walking" to write temp tables?

Comment by Marco Pivetta [ 14/Sep/14 ]

Yes, we use subqueries in a lot of places, but the paginator walkers are already a real mess from a maintenance PoV, so I don't think we want to go down that route.





[DDC-3270] abstract class database entity generation Created: 23/Aug/14  Updated: 19/Oct/14  Resolved: 19/Oct/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Yan Ni Assignee: Marco Pivetta
Resolution: Invalid Votes: 0
Labels: mysql, orm
Environment:

WAMP symfony2



 Description   

I create an abstract class A using @ORM annotations, then create class B which is a subclass of class A. When I use these to update the mysql database, however, a table for class A was also generated, which shouldn't have happened(because class A is an abstract class).



 Comments   
Comment by Marco Pivetta [ 19/Oct/14 ]

abstract types in the ORM are called MappedSuperclass}}es. The fact that a class is {{abstract doesn't mean that it has no concrete table representing it.





[DDC-3096] JoinColumn definition does not regard column type with value translation Created: 22/Apr/14  Updated: 22/Apr/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.4.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Volker Nauruhn Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: entityPersister, mapping, mysql, orm
Environment:

Symfony 2.4.3



 Description   

I made a custom column type for doctrine which converts values between MySQL and PHP.

When I use a field with this column type as JoinColumn in a ManyToOne relation plus the column has a different name than the field, the BasicEntityPersister gets always "null" when he is asking for type of the given column name because he is ascing for given column name and not field name.

Example
=====================

Make.php:

/**
 @ORM\Column(name="language_code", type="locale")
 */
private $locale;

Foobar.php

/**
 @ORM\ManyToOne(targetEntity="Make")
 @ORM\JoinColumn(name="make_locale", referencedColumnName="language_code")
 */
private $makes;

The localeType translates between long and short language codes. For exmaple "de" (PHP) to "de_DE" (MySQL).



 Comments   
Comment by Marco Pivetta [ 22/Apr/14 ]

This is not a blocker, as you're really going into custom implementations.

You should probably provide a failing test case to clarify what you are doing





[DDC-2802] Mysql index length cannot be specified Created: 19/Nov/13  Updated: 23/May/14  Resolved: 20/Dec/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers
Affects Version/s: 2.3.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Nikolay Konev Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 0
Labels: mysql
Environment:

MS Windows 8, php 5.4



 Description   

MySQL don't allow creating indexes on BLOB/TEXT columns without speciifed size.

Trying to create index:

// colname is LONGTEXT
$tableScheme->addUniqueIndex(['colname'], 'index_name');

MySQL throws
> Syntax error or access violation: 1170 BLOB/TEXT column 'colname' used in key specification without a key length

I can suggest to improve parsing add*Index $columnNames params, for allowing per-column index size definition:

$tableScheme->addUniqueIndex(['colname(64)'], 'index_name');

It can be used not just in described case.



 Comments   
Comment by Nikolay Konev [ 19/Nov/13 ]

It matters, because indices definitions are part of tbale creating f.e. in MySQL.

For now it disallows to create table in one query.

Working on solution in forked repo now.

Comment by Benjamin Eberlei [ 20/Dec/13 ]

This is too specific a schema feature. Use EventListener to listen to events triggered from the MySQLPlatform to update the SQL accordingly.

Comment by Jesse Schalken [ 23/May/14 ]

Is it possible to get more information on how to solve this issue? Do I have to generate the CREATE TABLE statement myself? How does the ORM manage to apply indexes to text/string columns?

Thanks





[DDC-2747] Doctrine Checking Null but not default Created: 17/Oct/13  Updated: 17/Oct/13  Resolved: 17/Oct/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Clay Garland Assignee: Marco Pivetta
Resolution: Won't Fix Votes: 0
Labels: default, mysql, nullable
Environment:

FreeBSD, PHP 5.4.13, MySQL 5.6.10



 Description   

Doctrine ORM checks fields for the acceptance of null values on entity commits. It does not, however, check if those same fields allow have default values. Our DBA likes to specify not-null, in addition to a default, so that a manual entry of a null value cannot take place. It seems that Doctrine should check that the field has a default value after it checks if it accepts null, in order to prevent errors on not-null fields with default values that are not passed explicit values.



 Comments   
Comment by Marco Pivetta [ 17/Oct/13 ]

Clay Garland this is not up to doctrine to check. If your schema enforces non-null fields, so should your object graph do as well.

That means that your setters or constructors should guarantee consistent state of the properties in your entities at any time, regardless of the ORM.

Comment by Marco Pivetta [ 17/Oct/13 ]

Also a side-note: the ORM always assumes that your data is valid. It just limits itself to saving and loading it.

Comment by Clay Garland [ 17/Oct/13 ]

Thank you for the clarification, I got a ticket from a dev and our DBA said it was doctrine was throwing the error, but now that I've looked at the Entity, he has no default value assigned to his private variable and doctrine is just doing what it's told.

Clay





[DDC-2658] Inserting NULL for Undefined Variables Created: 07/Sep/13  Updated: 07/Sep/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3.1
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: John Julien Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: default, mysql, null
Environment:

CentOS Linux, VMware, MySQL



 Description   

When a MySQL schema contains a line like this:
`failedattempts` int(11) NOT NULL DEFAULT '0',

If a new entity is created and the failedattempts variable is never set, Doctrine attempts to insert the value NULL which throws an SQL error. If a value is undefined for a new object, shouldn't it just be excluded from the insert fields?






[DDC-2632] Doctrine reverse engineer doesn't honor NOT NULL foreign keys Created: 24/Aug/13  Updated: 24/Aug/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers, ORM
Affects Version/s: 2.3.4
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Paolo Avezzano Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: engineer, mysql, null, nullable, reverse, yaml
Environment:

MAMP, latest version. Symfony up to date



 Description   

In this table, the two foreign keys (which I had to downgrade from primary keys because of Doctrine and then added a separate PK named 'Id') called 'Oggetto' and 'Sistema' have been set as NOT NULL.


-- -----------------------------------------------------
-- Table `mydb`.`Composizione`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Composizione` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`Composizione` (
  `Id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `Oggetto` INT UNSIGNED NOT NULL ,
  `Sistema` INT NOT NULL ,
  INDEX `fk_Oggetto_has_Sistema_Sistema1_idx` (`Sistema` ASC) ,
  INDEX `fk_Oggetto_has_Sistema_Oggetto1_idx` (`Oggetto` ASC) ,
  PRIMARY KEY (`Id`) ,
  CONSTRAINT `fk_Oggetto_has_Sistema_Oggetto1`
    FOREIGN KEY (`Oggetto` )
    REFERENCES `mydb`.`Oggetto` (`Id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Oggetto_has_Sistema_Sistema1`
    FOREIGN KEY (`Sistema` )
    REFERENCES `mydb`.`Sistema` (`Id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Converting into Yaml here it is what I get.

    type: entity
    table: Composizione
    fields:
        id:
            id: true
            type: integer
            unsigned: true
            nullable: false
            column: Id
            generator:
                strategy: IDENTITY
    manyToOne:
        oggetto:
            targetEntity: Oggetto
            cascade:      {  }
            mappedBy:     null
            inversedBy:   null
            joinColumns:
                Oggetto:
                    referencedColumnName: Id
            orphanRemoval: false
        sistema:
            targetEntity: Sistema
            cascade:      {  }
            mappedBy:     null
            inversedBy:   null
            joinColumns:
                Sistema:
                    referencedColumnName: Id
            orphanRemoval: false
    lifecycleCallbacks: {  }

Basically it lost in translation the NOT NULL part.
To make it work I had to manually add two "nullable: false" lines as I did below:

    type: entity
    table: Composizione
    fields:
        id:
            id: true
            type: integer
            unsigned: true
            nullable: false
            column: Id
            generator:
                strategy: IDENTITY
    manyToOne:
        oggetto:
            targetEntity: Oggetto
            cascade:      {  }
            mappedBy:     null
            inversedBy:   null
            joinColumns:
                Oggetto:
                    referencedColumnName: Id
                    nullable:     false
            orphanRemoval: false
        sistema:
            targetEntity: Sistema
            cascade:      {  }
            mappedBy:     null
            inversedBy:   null
            joinColumns:
                Sistema:
                    referencedColumnName: Id
                    nullable:     false
            orphanRemoval: false
    lifecycleCallbacks: {  }

Is it a bug or am I missing something?

Regards,
Paolo Avezzano






[DDC-2555] Syntax Error when column is "separator" Created: 16/Jul/13  Updated: 16/Jul/13  Resolved: 16/Jul/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Tayniak Boris Assignee: Marco Pivetta
Resolution: Invalid Votes: 0
Labels: mysql, orm
Environment:

Symfony 2.3.1



 Description   

I had to made a column called "separator", 'Create Table' works, but when 'insert into':

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'separator) VALUES ('Test', 'Test', 'a:1:

{i:0;s:4:\"test\";}

', 'a:5:{s:10:\"http-' at line 1

After some searches ... Separator used by MySQL, so i changed the name:

php app/console d:s:u --force
Updating database schema...

[Doctrine\DBAL\DBALException]
An exception occurred while executing 'ALTER TABLE seo CHANGE separator link_separator VARCHAR(255) DEFAULT NULL':

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'separator link_separator VARCHAR(255) DEFAULT NULL' at line 1

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'separator link_separator VARCHAR(255) DEFAULT NULL' at line 1

Same Error... I will manualy update but for me it's a bug ...



 Comments   
Comment by Marco Pivetta [ 16/Jul/13 ]

Reserved SQL keywords need manual escaping by either using a custom quoting strategy or by marking the table name with backticks in mappings, such as:

"`separator`"

The ORM is not responsible for escaping identifiers out of the box.

Comment by Tayniak Boris [ 16/Jul/13 ]

I have never writen any SQL Request for this error
Only Doctrine, I re-use the request with ` but Doctrine create the table, the insert and alter the table

Create table: no error
Insert into: error
Alter table: error

use Doctrine\ORM\Mapping as ORM;
[...]
/**

  • @ORM\Column(name="separator", type="string", nullable=true)
    */
Comment by Marco Pivetta [ 16/Jul/13 ]

Tayniak Boris that's actually a problem in the DBAL layer. it should have failed during DDL execution





[DDC-2188] DQL arithmetic priority is not considered in expression 'a/(b*c)' Created: 05/Dec/12  Updated: 06/Apr/13  Resolved: 06/Apr/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL, ORM
Affects Version/s: 2.3
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Mahdi Dehghani Assignee: Fabio B. Silva
Resolution: Fixed Votes: 0
Labels: Cli, mysql


 Description   

A DQL query that contains an arithmetic expression like 'a/(b*c)' will be compiled to 'a/b*c' in SQL and therefore the result of '100/(2*2)' will be 100, instead of 25.

Note: I have tested it by mysql only



 Comments   
Comment by Marco Pivetta [ 16/Mar/13 ]

Being handled in DDC-2355





[DDC-2161] Moving entity relationship doesn't move foreign key in mysql table Created: 22/Nov/12  Updated: 03/Feb/14  Resolved: 25/Nov/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers, ORM
Affects Version/s: Git Master
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Alessandro Tagliapietra Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: Cli, mysql
Environment:

Mac OSX, mysql 5.5.28, php 5.4.8, fresh symfony install with doctrine/orm 77d060ab74212919f1eaace72842ea43bf420f43


Attachments: File Argument.php     File Post.php     File Post2.php    
Issue Links:
Reference
relates to DBAL-392 Moving entity relationship doesn't mo... Resolved

 Description   

Edit: moved the bug to the dbal project, please delete this or set as invalid, new bug:

http://www.doctrine-project.org/jira/browse/DBAL-392

------------------------------------------------------

These are the steps I've tried to reproduce the problem:

  • composer create-project symfony/framework-standard-edition path/ 2.1.3
  • edited parameters for mysql db
  • created attached 'Post' entity (note the parent/children fields)
  • updated schema with doctrine:schema:create

I see that the foreign key in phpmyadmin is correctly set to Post.id which is correct.

Now:

  • created another entity 'Argument'
  • moving the children field from 'Post' to 'Argument' removing setters getters into 'Post' and changing target entity field accordingly
  • Updated DB schema (doctrine:schema:update --force)

The situation is now the one you can find in Post2 and Argument attachments.

The problem is that now, the foreign key in Post field it's still on Post.id and not on Argument.id and schema:update says the DB is in sync with the metadata.

Manually deleting the foreign key and running schema:update makes the key being correctly created into Argument.id



 Comments   
Comment by Benjamin Eberlei [ 24/Nov/12 ]

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

Comment by Benjamin Eberlei [ 25/Nov/12 ]

On wrong project, see DBAL-392

Comment by Doctrine Bot [ 03/Feb/14 ]

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





[DDC-2139] Table collate/charset doesn't inherits from database Created: 12/Nov/12  Updated: 25/Nov/12  Resolved: 25/Nov/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM, Tools
Affects Version/s: 2.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: William Knak Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 0
Labels: charset, collate, collation, mysql, utf, utf8
Environment:

Symfony2



 Description   

Using symfony2 command doctrine:schema:create the tables and Varchar fields are Collate/Charset are not inherited from database. I changed MySQL defaults to latin1/latin1_general_ci; The doctrine:database:create works; but tables and fields don't. So I found there is a code on MySqlPlataform.php:

if ( ! isset($options['charset']))

{ $options['charset'] = 'utf8'; }

if ( ! isset($options['collate']))

{ $options['collate'] = 'utf8_unicode_ci'; }

$query .= 'DEFAULT CHARACTER SET ' . $options['charset'];
$query .= ' COLLATE ' . $options['collate'];

So if you don't pass collate and charset as entity options, it will put utf8 and utf8_unicode_ci instead of dabatase default charset.

My suggestion is to change a little bit to:

if (isset($options['charset']))

{ $query .= 'DEFAULT CHARACTER SET ' . $options['charset']; }

if (isset($options['collate']))

{ $query .= ' COLLATE ' . $options['collate']; }

 Comments   
Comment by Benjamin Eberlei [ 25/Nov/12 ]

If we change it, then it will be like before and users disliked that very much. The decision is to use UTF-8 as default and everybody else has to change the information instead of the other way around.





[DDC-1274] there is no way to set collate of the table charset ! Created: 13/Jul/11  Updated: 12/Nov/12  Resolved: 13/Jul/11

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers
Affects Version/s: 2.0.6
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: sina miandashti Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: charset, collate, collation, mysql
Environment:

zend frame work 1.10 with doctrine orm 2 and dbal



 Description   

i searched a lot for this

there is no way to set the collate of table fields to for ex. utf8_persian_ci in doctrine settings



 Comments   
Comment by Benjamin Eberlei [ 13/Jul/11 ]

This is not a supported feature, if you set the collation in the CREATE DATABASE statement this will inherit to all fields automatically.

Comment by William Knak [ 12/Nov/12 ]

Actually Doctrine 2.3.0 does not allow inheritance. Please look at Issue #DDC-2139 . At least, if there's an option to set a global default collation (wich I didn't find), the problem will occurs every time a table of string/varchar field was created by Symfony2 Doctrine Command.





Generated at Wed Nov 26 11:21:34 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.