subquery not work

[jaassoon]

Describe the bug

Hi, @oguimbal I came across this bug. Could you please take a look?

It can be reproduced in playground too.

Thanks in advance.

column "foo.bar_id" does not exist

To Reproduce

create table foo(
id uuid not null,
bar_id uuid,
version integer
);

select foo.* from foo where foo.version=(select Max(foo2.version) from foo foo2 where foo2.bar_id=foo.bar_id); 

pg-mem version

2.6.8

[EggDice]

Unfortunately it is still reproducible in 3.0.1

Based on my testing the queries fail when there is a clause in the subquery that references anything from the main query. This is the shortest way I was able to reproduce the error.

CREATE TABLE "test_record" ("name" character varying NOT NULL);

insert into test_record values('hello');
insert into test_record values('bello');

select (select name) as name from test_record;

The error:

column "name" does not exist

[EggDice]

I have tried to debug it but I am unable to fix it :( This unit test shows the issue, have added to src/tests/issues.spec.ts:

    describe('#310', () => {
       it('subquery reference', () => {
           db.createSchema('test');
           none(`CREATE TABLE "test_record_2" ("name" character varying NOT NULL)`);
           none(`insert into test_record_2 values('test')`);
           expect(one(`select (select name) as name from test_record_2`)).toEqual({ name: ['test'] });
       });
   });

the stacktrace:

505 | }
506 | export class QueryError extends Error {
507 |     readonly data: ErrorData;
508 |     readonly code: string | undefined;
509 |     constructor(err: string | ErrorData, code?: string) {
510 |         super(typeof err === 'string' ? err : errDataToStr(err));
              ^
error: column "name" does not exist

      at new QueryError (/Users/eggdice/Projects/pg-mem/src/interfaces.ts:510:9)
      at new ColumnNotFound (/Users/eggdice/Projects/pg-mem/src/interfaces.ts:547:9)
      at _buildValueReal (/Users/eggdice/Projects/pg-mem/src/parser/expression-builder.ts:93:19)
      at _buildValue (/Users/eggdice/Projects/pg-mem/src/parser/expression-builder.ts:51:11)
      at buildValue (/Users/eggdice/Projects/pg-mem/src/parser/expression-builder.ts:20:17)
      at buildCols (/Users/eggdice/Projects/pg-mem/src/transforms/selection.ts:102:25)
      at /Users/eggdice/Projects/pg-mem/src/transforms/selection.ts:134:55
      at usingValue (/Users/eggdice/Projects/pg-mem/src/parser/context.ts:11:20)
      at new Selection (/Users/eggdice/Projects/pg-mem/src/transforms/selection.ts:134:25)
      at buildSelection (/Users/eggdice/Projects/pg-mem/src/transforms/selection.ts:33:12)
✗ Github issues > #310 > subquery reference [11.86ms]

@oguimbal Can we ask you to look into this, please? :) <3