'reshuffling a singleton select' is not supported by MemSQL Distributed

user_4780732 0
Viewed 162

Everything works find with the original query, but when translated in knex, it threw the error.

reshuffling a singleton select' is not supported by MemSQL Distributed.

const finalQuery = knex
.select('*')
.from('tpx.capacity_planning_weekly_hsd_consumption as t1')
.leftJoin(
  knex('tpx.capacity_planning_weekly_hsd_consumption as hsd2')
    .sum('gbs_in', { as: 'gbsIn' })
    .select('hostname', 'mac_domain')
    .where('hostname', 'acr01.49thst.pa.panjde')
    .andWhere('mac_domain', '5')
    .andWhere('hsd2.week_ending', '2020-07-18')
    .andWhere('hsd2.node_leg', 'FN842')
    .as('t2'),
  function() {
    this.on('t1.mac_domain', '=', 't2.mac_domain')
    this.on('t1.hostname', '=', 't2.hostname')
  }
)
.where('t1.hostname', 'acr01.49thst.pa.panjde')
.andWhere('t1.mac_domain', '5')
.andWhere('t1.week_ending', '2020-07-18')
.toSQL()

origin query in memsq

SELECT SUM(gbs_in) as totalConsumption,
hsdPart.gbsIn as nodeConsumption,
ROUND(hsdPart.gbsIn / SUM(gbs_in),2) as ConsumptionPercentage
FROM
capacity_planning_weekly_hsd_consumption as hsd
JOIN
(select
SUM(gbs_in ) as gbsIn,
hsd2.hostname,
hsd2.mac_domain
FROM
capacity_planning_weekly_hsd_consumption hsd2
WHERE hsd2.hostname = "acr01.49thst.pa.panjde"
AND hsd2.mac_domain = '5'
AND hsd2.week_ending = '20200718'
AND hsd2.node_leg = 'FN842') as hsdPart
on hsd.mac_domain = hsdPart.mac_domain
and hsd.hostname = hsdPart.hostname
WHERE hsd.hostname = "acr01.49thst.pa.panjde"
AND hsd.mac_domain = '5'
AND hsd.week_ending = '20200718'
singlestore knex.js
1 Answers

Try using the same int date format in knex query as in Singlestore's query:

 const finalQuery = knex
.select('*')
.from('tpx.capacity_planning_weekly_hsd_consumption as t1')
.leftJoin(
  knex('tpx.capacity_planning_weekly_hsd_consumption as hsd2')
    .sum('gbs_in', { as: 'gbsIn' })
    .select('hostname', 'mac_domain')
    .where('hostname', 'acr01.49thst.pa.panjde')
    .andWhere('mac_domain', '5')
    .andWhere('hsd2.week_ending', '20200718')
    .andWhere('hsd2.node_leg', 'FN842')
    .as('t2'),
  function() {
    this.on('t1.mac_domain', '=', 't2.mac_domain')
    this.on('t1.hostname', '=', 't2.hostname')
  }
)
.where('t1.hostname', 'acr01.49thst.pa.panjde')
.andWhere('t1.mac_domain', '5')
.andWhere('t1.week_ending', '20200718')
.toSQL()

If not resolved read this article and figure out if anything logical missing according to your data.

user_196281730
Related