found a bug with groupBy clause

Author: tim-taiCreated Mar 25, 2019Updated Mar 27, 2019

I found this when I was trying to create a query that will return a list of task.

Here is the query

javascript

var query = db.select(task.id,
       		task.note,
	      	).
		from(item).
		innerJoin(task, task.id.eq(item.task_id)).
		where(item.auditor_id.eq(123)).
		groupBy(item.task_id);

Here are the sample data for both table, task(master) and item(detail).

javascript

var task_data = [{
      'id': 1,
      'initiator_id': 1,
      'schedule_id': 1,
      'status': 0,
      'description': 'Task Example A'
    }, {
      'id': 2,
      'initiator_id': 2,
      'schedule_id': 2,
      'status': 0,
      'description': 'Task Example B'
    }];

var item_data = [{
        'id': 1,
        'task_id': 1,
        'auditor_id': 123,
        'tag_id': 1,
        'asset_name': 'Item A',
        'asset_type_name': 'Type A',
        'description': 'example A'
      }, {
        'id': 2,
        'task_id': 1,
        'auditor_id': 123,
        'tag_id': 2,
        'asset_name': 'Item B',
        'asset_type_name': 'Type B',
        'description': 'example B'
      }
      /*,{
            'id': 3,
            'task_id': 2,
            'auditor_id': 123,
            'tag_id': 3,
            'asset_name': 'Item C',
            'asset_type_name': 'Type C',
            'description': 'example C'
          }*/
    ];

So with the Sample data above the query should just return one row of task id (1) and description (example A), but somehow it return twice.

The result will be correct when third data row(commented) is added, which will return two task id(1, 2) and two description(example A, example B).

Here is the example: jsfiddle

Source: google/lovefield

View original on GitHub View discussion on GitHub