Pchp.Library.Spl.TypeError when using Fluent PDO & MS SQL and query return no records

[Alikont]

When using Fluent PDO some queries return TypeError during execution. But they work fine in PHP 7

public static function getUser($host, $dbName, $login) {
        $dsn = "sqlsrv:Server=" . $host . "; Database=" . $dbName . ";";
        $pdo = new PDO($dsn, '', '');
        //attributes
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $builder = new \Envms\FluentPDO\Query($pdo);

        $query = $builder->from('manager' . ' t')
                        ->leftJoin('role' . ' r ON r.id=t.role_id')
                        ->select('r.is_admin, r.name as role')
                        ->where('t.login', $login)->where('t.archived', 0);
        $arUser = $query->fetch();
        return $arUser;
}  

using Pchp.Core;

const string host = "(localdb)\\MSSQLLocalDB";
const string dbName = "fluentpdo";

var phpContext = Context.CreateEmpty();

var user = DBHelper.getUser(phpContext, host, dbName, "login1");

Exception Pchp.Library.Spl.TypeError: ''

Callstack

   at Pchp.Core.StrictConvert.ToLong(PhpValue value)
   at Envms.FluentPDO.Queries.Select.fetch(PhpValue column, Int64 cursorOrientation) in C:\Users\al1ko\Downloads\fluentPDOSample (1)\fluentPDOSample\vendor\envms\fluentpdo\src\Queries\Select.php:line 123
   at System.Runtime.CompilerServices.RuntimeHelpers.DispatchTailCalls(IntPtr callersRetAddrSlot, IntPtr callTarget, IntPtr retVal)
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at DBHelper.getUser(Context <ctx>, PhpValue host, PhpValue dbName, PhpValue login) in C:\Users\al1ko\Downloads\fluentPDOSample (1)\fluentPDOSample\PhpLibrary\DBHelper.php:line 26
   at Program.<Main>$(String[] args) in C:\Users\al1ko\Downloads\fluentPDOSample (1)\fluentPDOSample\ConsoleApp\Program.cs:line 11

Database creation script:

CREATE TABLE [dbo].[manager](
    [id] [int] IDENTITY(23,1) NOT NULL,
    [role_id] [int] NOT NULL,
    [name] [nvarchar](255) NOT NULL,
    [login] [nvarchar](255) NOT NULL,
    [archived] [int] NOT NULL,
    CONSTRAINT [PK_manager_id] PRIMARY KEY CLUSTERED ( [id] ASC )
) ON [PRIMARY]

CREATE TABLE [dbo].[role] (
    [id] [int] IDENTITY(13,1) NOT NULL,
    [name] [nvarchar](255) NOT NULL,
    [is_admin] [int] NOT NULL,
    [is_system] [int] NOT NULL,
    [is_default] [int] NOT NULL,
    [zone_id] [int] NOT NULL,
    CONSTRAINT [PK_role_id] PRIMARY KEY CLUSTERED ([id] ASC ) 
) ON [PRIMARY]

Generated SQL (it expects zero entries on return)

exec sp_executesql N'SELECT t.*, r.is_admin, r.name as role FROM manager t LEFT JOIN role r ON r.id=t.role_id WHERE t.login = @_0 AND t.archived = @_1',N'@_0 nvarchar(6),@_1 bigint',@_0=N'login1',@_1=0

Full reproduce repository: https://github.com/Alikont/Peachpie-FluentPDO-Issue-TypeError

[jakubmisek]

Thank you for the issue, and for the example!

[Alikont]

Hello, thanks for the quick fix.

We've noticed a degradation in 1.0.22.

Multiple joins are not applied in PDO query builder.

I've updated the repo on the link. It fails with

Peachpie.Library.PDO.PDOException: 'The multi-part identifier "r2.name" could not be bound.'

Because query skips second join.

 $builder->from('manager' . ' t')
                            ->leftJoin('role' . ' r ON r.id=t.role_id')
                            ->leftJoin('role' . ' r2 ON r2.id=t.role_id')
                            ->select('r.is_admin, r2.name as role')
                            ->where('t.login', $login)->where('t.archived', 0);

SELECT t.*, r.is_admin, r2.name as role
FROM manager t
    LEFT JOIN role r ON r.id=t.role_id
WHERE t.login = ?
    AND t.archived = ?

1.0.21 works fine.

Should I create a new issue?

[jakubmisek]

@Alikont I'm thinking about two possible causes:

• the behavior of rowCount changed https://github.com/peachpiecompiler/peachpie/commit/2d1b45d1f61e23e87828ca6f31b8f20baa0aa559
• default PHP_VERSION changed to 8.0 (this can be changed using <LangVersion> property)

[Alikont]

It fails before query even executes, it's somewhere inside query builder, so it's definitely not the rowCount fix.

Our main PHP project uses 7.2, but setting it also doesn't work.

I narrowed it down to specific line of code.

this regex should fill $maches variable by ref, it correctly parses the expression, but the &$matches parameter is empty after method exits. Then FluentPDO thinks that SQL is invalid and fails to build it correctly.

1.0.21:

1.0.22:

[jakubmisek]

I see! Ok, I'll prepare a unit test, and fix it.