Athena Driver does not cast to float

[JohnMwashuma]

Describe the bug

Am getting an unknown float type error when I create the following measure and try querying a csv on amazon s3 using the Athena Driver. I tried updating the athena driver and cube js backend packages to recent versions and it's still failling.

Dimensions

dimensions: {
    {
      beds: {
        sql: `beds`,
        type: `number`,
        title: `Beds`
      },
      name: {
        sql: `name`,
        type: `string`,
        title: `Facility Name`
      },
      osm_id: {
        sql: `osm_id`,
        type: `string`
      },
      source: {
        sql: `source`,
        type: `string`
      },
      amenity: {
        sql: `amenity`,
        type: `string`,
        title: `Amenity`
      },
      operator: {
        sql: `operator`,
        type: `string`
      },
      addr_full: {
        sql: `addr_full`,
        type: `string`
      },
      emergency: {
        sql: `emergency`,
        type: `string`
      },
      healthcare: {
        sql: `healthcare`,
        type: `string`
      },
      wheelchair: {
        sql: `wheelchair`,
        type: `string`
      },
      electricity: {
        sql: `electricity`,
        type: `string`,
        title: `Electricity Type`
      },
      water_source: {
        sql: `water_source`,
        type: `string`
      },
      contact_phone: {
        sql: `contact_phone`,
        type: `string`,
        title: `Phone`
      },
      opening_hours: {
        sql: `opening_hours`,
        type: `string`
      },
      operator_type: {
        sql: `operator_type`,
        type: `string`
      },
      insurance_health: {
        sql: `insurance_health`,
        type: `string`
      },
      facility_location: {
        sql: `facility_location`,
        type: `string`
      },
      operational_status: {
        sql: `operational_status`,
        type: `string`
      },
      staff_count_nurses: {
        sql: `staff_count_nurses`,
        type: `string`
      },
      staff_count_doctors: {
        sql: `staff_count_doctors`,
        type: `string`
      },
      healthcare_equipment: {
        sql: `healthcare_equipment`,
        type: `string`
      },
      _facility_location_altitude: {
        sql: `_facility_location_altitude`,
        type: `string`
      },
      _facility_location_latitude: {
        sql: `_facility_location_latitude`,
        type: `string`
      },
      _facility_location_longitude: {
        sql: `_facility_location_longitude`,
        type: `string`
      },
      _facility_location_precision: {
        sql: `_facility_location_precision`,
        type: `string`
      }
    }
  }

Measures

  measures: {
    {
      num_grid: {
        sql: `count(*) filter (where ${electricity} = 'grid' and ${electricity} = 'solar')`,
        type: `number`
      },
      num_power: {
        sql: `count(*) filter (where ${electricity} = 'grid' and ${electricity} = 'solar')`,
        type: `number`
      },
      facilities: {
        sql: `count(1)`,
        type: `number`,
        title: `Facilities`
      },
      total_beds: {
        sql: `sum(${beds})`,
        type: `number`,
        title: `Total Beds`
      },
      num_electricity: {
        sql: `(${facilities}-${num_no_electricity})`,
        type: `number`,
        title: `Facilities with electricity`
      },
      num_no_electricity: {
        sql: `count(*) filter (where ${electricity} = 'no')`,
        type: `number`,
        title: `Num no power`
      },
      percent_electricity: {
        sql: `CAST((((${facilities}-${num_no_electricity}))/${facilities}) AS FLOAT)`,
        type: `number`,
        title: `Percent Electricity`
      }
    }
  ]

Generated SQL

NB: I have replaced the actual values for cube name and table name with <cube_name> and <table_name> respectively.

"SELECT\n CAST(count(\"<cube_name>\".beds) as float) \"<cube_name>"\n FROM\n \"<table_name>\" AS \"<cube_name>\"\n LIMIT 5000",

Error

Error: SYNTAX_ERROR: line 2:7: Unknown type: float
    at QueryQueue.parseResult (../node_modules/@cubejs-backend/query-orchestrator/orchestrator/QueryQueue.js:99:13)
    at QueryQueue.executeInQueue (../node_modules/@cubejs-backend/query-orchestrator/orchestrator/QueryQueue.js:88:19)
    at runMicrotasks (<anonymous>)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async ../node_modules/@cubejs-backend/query-orchestrator/orchestrator/QueryCache.js:262:17
    at async ../node_modules/@cubejs-backend/query-orchestrator/orchestrator/QueryOrchestrator.js:53:24
    at async OrchestratorApi.executeQuery (../node_modules/@cubejs-backend/server-core/core/OrchestratorApi.js:32:20)
    at async ../node_modules/@cubejs-backend/api-gateway/index.js:594:26
    at async Promise.all (index 0)
    at async ApiGateway.load (../node_modules/@cubejs-backend/api-gateway/index.js:580:23)

Version:

"@cubejs-backend/athena-driver": "^0.20.0",
"@cubejs-backend/server": "^0.20.0",

[paveltiunov]

Hey @JohnMwashuma ! It'd be beneficial if you can provide a full cube schema.

[JohnMwashuma]

Hey @JohnMwashuma ! It'd be beneficial if you can provide a full cube schema.

@paveltiunov I have updated the issue with all the cube dimensions and measures

[paveltiunov]

@JohnMwashuma CAST(AS FLOAT) indeed doesn't work in Athena. You may want to try double instead.