Redshift incompatibility - string literal used in partition by clause.

[ajones-sibipro]

Describe the bug

The segment_page_views_table fails to build on redshift since Amazon Redshift doesn't support string literals in PARTITION BY clauses.

Steps to reproduce

Example taken from compiled code:

  with
  unioned_sources AS (
      select 'segment_page_views_table' as source_name, * from pages
  ),

  row_numbering as (

      select
          *,
          row_number() over (partition by source_name, id order by received_at asc) as row_num
      from unioned_sources

  ) select * from row_numbering

Expected results

Table should build.

Actual results

SQL Error [XX000]: ERROR: failed to find conversion function from "unknown" to text

System information

- package: Fleetio/dbt_segment version: [">=0.3.0", "<0.4.0"]

Which database are you using dbt with?

• [ ] postgres
• [x] redshift
• [ ] bigquery
• [ ] snowflake
• [ ] other (specify: ____)

The output of dbt --version:

Core:
  - installed: 1.7.3
  - latest:    1.7.3 - Up to date!

Plugins:
  - redshift: 1.7.1 - Up to date!
  - postgres: 1.7.3 - Up to date!

[0adamjones]

@ajones-sibipro Hey there! Thanks for pointing this out. Are you willing to submit a PR for this fix? We don't have a redshift instance we can test in.

[lindseywatts]

changing

unioned_sources AS ( select 'segment_page_views_table' as source_name, * from pages ),

to

unioned_sources AS ( select cast('segment_page_views_table' as text) as source_name, * from pages )

fixes it for me.

i have never submitted a PR for an open source project before. if someone can help me make sure i do everything right i would be happy to submit. @ajones-sibipro @0adamjones

[0adamjones]

@lindseywatts Nice! Are you running redshift? If so and that fix works for you, then I'll take your word for it 😄

Also for submitting a pr, the easiest way is with Github desktop. Download and then: 1) Clone this repo 2) Make a new branch with your changes 3) Commit the changes to remote 4) Click "Create Pull Request" from the Github Desktop UI

That should take you to our PR form template that you can fill out and then submit for review.

[lindseywatts]

@0adamjones added: https://github.com/fleetio/dbt-segment/pull/17

[0adamjones]

This should be resolved in 0.3.1 👍