Select count laravel console show different result on cockroach console

[kocoten1992]

Describe the problem

On cockroachdb console, select count return correct result, on laravel console, it return wrong (I guess I should report to laravel, but they don't officially support cockroachdb atm).

To Reproduce

create user testing with password 'testing';

create database testing;

grant all on database testing to testing;

use testing;

create table a (
  id uuid primary key
);

create table b (
  id uuid primary key,
  type int,
  a_id uuid references a (id) on delete set null
);

insert into a (id) values ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa');

insert into b (id, type, a_id) values ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb1', 1, 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa');
insert into b (id, type, a_id) values ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb2', 2, 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa');

// return 1, laravel count
DB::table('b')->join('a', 'b.a_id', '=', 'a.id')->join('b as b_2', 'a.id', '=', 'b_2.a_id')->where('b_2.type', 1)->groupBy('b.id')->count('b.id');
1

// return 2, cockroachdb count
select count("b"."id") as aggregate from "b" inner join "a" on "b"."a_id" = "a"."id" inner join "b" as "b_2" on "a"."id" = "b_2"."a_id" where "b_2"."type" = 1 group by "b"."id";
  aggregate  
+-----------+
          1  
          1  
(2 rows)

If I select it instead of count, it return correct result:

// return 2, laravel select
DB::table('b')->join('a', 'b.a_id', '=', 'a.id')->join('b as b_2', 'a.id', '=', 'b_2.a_id')->where('b_2.type', 1)->groupBy('b.id')->get(['b.id']);
=> Illuminate\Support\Collection {#3153
     all: [
       {#3157
         +"id": "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb1",
       },
       {#3152
         +"id": "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb2",
       },
     ],
   }


// return 2, cockroachdb select
select "b"."id" from "b" inner join "a" on "b"."a_id" = "a"."id" inner join "b" as "b_2" on "a"."id" = "b_2"."a_id" where "b_2"."type" = 1 group by "b"."id";
                   id                   
+--------------------------------------+
  bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb1  
  bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb2  
(2 rows)

Environment:

• CockroachDB version [e.g. 2.0.x]

[email protected]:26257/testing> select version();
                                          version                                          
+-----------------------------------------------------------------------------------------+
  CockroachDB CCL a8afcab (x86_64-unknown-linux-gnu, built 2019/01/01 02:44:24, go1.10.7)  
(1 row)

• Server OS: Ubuntu 18.04

[kocoten1992]

I think should open this on laravel support than here.

[kocoten1992]

This is turn out a laravel issue, laravel/framework#22883