Dbal::syncInserted() builds invalid sqlite query for Entity with combined primary key

[Jmp00000001]

When an entity is identified by multiple rows:

<?php
use ORM\Entity;

class MyEntity extends Entity {
  protected static $primaryKey = ['row_0', 'row_1', 'row_2'];

  ...
}

saving it via

$myEntity = new MyEntity();
...
$myEntity->save();

throws a PDOException because
Dbal::syncInserted() - https://github.com/tflori/orm/blob/master/src/Dbal/Dbal.php#L308
builds and executes a invalid query:

SELECT * FROM "my_entity" WHERE ("row_0","row_1","row_2") IN (('value_0','value_1','value_2'));

This happens with version >= 1.5.0

[tflori]

hm. which database and database version you are using?

because it should work (at least in mysql 5.5 - 5.7 and postgres 9.4):
https://www.db-fiddle.com/f/9Yc53spSv4MH1zfRtemB5r/0

[tflori]

I've just tried with sqlite - here we would need to add the VALUES keyword:
https://www.db-fiddle.com/f/4RZfQiT8GmAGqYFbZixX2q/0

but this is not working in mysql - so maybe it's another thing we have to use server specific syntax 😞

[tflori]

awww.. who reads summaries.. you wrote already it's sqlite. I will provide a fix latest tomorrow morning.

[Jmp00000001]

So, yes we use sqlite (3.11.0) ^^

[Jmp00000001]

Should have stated it again in the text ^^

Thank you!

[tflori]

solved with version 1.5.2 thx @Jmp00000001

[splitbrain]

This unfortunately seems not to be fixed, yet. There are two problems:

The code seems to generate the wrong number of parenthesises. In our example we got something like:

 SELECT * FROM "table" WHERE ("col1","col2","col3") IN (VALUES ('1','2','3')))

As you can see there is one closing parenthesis too much.

Even when fixing the generated SQL, it requires a relatively new SQLite version. On @Jmp00000001's Ubuntu with PHP v7.2.11-2+ubuntu16.04.1+deb.sury.org+1 we had SQLite 3.11 which failed to parse this syntax. On my Arch Linux system, with 3.25.1 it works.

It seems the feature was introduced with 3.15.0 https://www.sqlite.org/changes.html#version_3_15_0

I'm not sure if it needs to be fixed for old SQLite versions, but at least the parenthesis mismatch should be fixed.

[tflori]

at least it's worse to mention it somewhere, thx

[tflori]

sorry for the bug. solved with version 1.5.3

[Jmp00000001]

I can confirm that it works with at least sqlite-1.5.1

[Jmp00000001]

*sqlite3-1.5.1