I have a problem related to the collecting fields module, but in the way to solve it I became confused about a part of the query, I'm using MSSQL as DB and the error points to: base.revision_id = revision.revision_id AS default_revision, what this part of the query is indeed to do in a SELECT statement? the database doesn't understand so I removed the part and everything goes OK but it belongs to the "protected function buildQuery" so, it should be use by lots of different functions and the change I did could crash my site.

Please bring me a hand with this. Thank you.

Comments

pabloroberto27’s picture

pabloroberto27 commented
Title: Maybe a wrong query » Maybe a wrong query base.revision_id = revision.revision_id AS default_revision
rob_johnston’s picture

rob_johnston commented

I have the same problem that occurs with MSSQL but not mySQL. It can be seen when creating a bean as written in #1917732: Incorrect syntax near '=' when creating a bean on MS SQL Server.

rob_johnston’s picture

rob_johnston commented
Category: support » bug
Priority: Normal » Critical
pabloroberto27’s picture

pabloroberto27 commented

MySQL accepts this kind of syntax? I have never seen an assignment operator in a SELECT clause, neither in MSSQL nor in MySQL.

rob_johnston’s picture

rob_johnston commented

¡Yo tampoco! I have never seen it, but apparently it is possible because I have run it on MySQL and it produces a column filled with the number 1. I don't know if it will work for every case, but for my bean creation problem I bet the following line should be changed from an "addExpression" to a "condition" (currently line 174 of entity.controller.inc):

$query->addExpression('base.' . $this->revisionKey . ' = revision.' . $this->revisionKey, $this->defaultRevisionKey);

$query->condition('base.' . $this->revisionKey, revision.' . $this->revisionKey);

And if the column is required for something, add this

$query->addExpression('1', $this->defaultRevisionKey);

This approach fails if a row with a value of zero in the $this->defaultRevisionKey column is required, because they will not be present.

I should say that I'm not at work right now, so I'm writing this without trying any of it.

rob_johnston’s picture

rob_johnston commented

Hi Pablo,

I think I found the solution! Last October, another person posted a patch here:
#1809346: Entity revisions support not working in SQL Server

It looks like it will work for me. Why don't we test that out and if it works for you, mark this issue as a duplicate of that issue and comment that you have reviewed his patch and that it works.

pabloroberto27’s picture

pabloroberto27 commented
Status: Active » Closed (duplicate)

Thanks a lot Rob,

As you say tha patch works very well so I'm going to close this ticket.

Have a nice day!