Nested queries does not work with PostgreSQL

[pavolbiely]

When using PostgreSQL the following code fails:

<?php
/** @var $db Nette\Database\Connection */
$db->table('a')->where('id', $db->table('b'));
?>

It throws an exception Recoverable Error with message Object of class Nette\Database\Table\Selection could not be converted to string. The problem is at line 207 here https://github.com/nette/nette/blob/477ba20fbd190e4db702e49619c3744097d3fae2/Nette/Database/Table/Selection.php#L207.

[srigi]

I would like to reopen this issue. I am experiencing very similar error when doing the same using PostgreSql 9.2.2:

$posts = $connection->table('post')->where('id',
  $connection->table('post_tag')->where('tag_id', 3)
);

yields Notice, Array to string conversion, debugger output

Then I tried modification, which is perfectly working with MySQL 5.5

$posts = $connection->table('post')->where('id',
  $connection->table('post_tag')->select('post_id')->where('tag_id', 3)
);

I get different error PDOException #08P01, SQLSTATE[08P01]: <<Unknown error>>: 7 ERROR: bind message supplies 0 parameters, but prepared statement "pdo_stmt_00000003" requires 1, debugger output

Nette 2.0.8 and Nette 2.1-dev

[hrach]

Please could you try test it with #945?

[srigi]

Hi, I tested that commit, but unfortunately it didn't fix the problem. I'm still getting exactly the same error PDOException #08P01....

[hrach]

Well, I have prepared fix for the second error, but I can simulate the first one notice.

[milo]

Notice happend with DiscoveredReflection and multiple primary key on post_tag. There is a GIST (https://gist.github.com/milo/4991664) for reproduction. Generally it happends when pass array to SqlBuilder->addSelect(array()).

I found another bug during the GIST writing maybe. When you define post_tag table without primary key. This case is commented out in GIST too.

[hrach]

@milo could you please explain https://gist.github.com/hrach/4990603? It's a little bit connected :)

[hrach]

All fixed in #945 pull.

[milo]

@hrach Comment to you GIST behavior... The ID column does not exist in subquery, so, PostgreSQL take a look onto "parent" table and take this one. This is usualy used for corelated subqueries, but in this case it is not so clever :-) Whole this IN condition is actually TRUE for every book table row.

It looks strange, but it is a Nette\Database mistake that builds SQL query with estimated column name.