Is this a bug or user error

Jones01

Hi,

Noticed some odd behaviour with a simple dataset I am pulling in from our db.

From the column named Area onwards the data doesn't align to the column headers. e.g the AREA column should 1420 etc. It all needs shifting left.

I have a feeling as I am selecting from two table and both have an ID column it can't work it out for some reason.

bit worrying.

GrantSmith

That's how the data is displayed. Text and dates are typically left justified and numbers are right justified. There aren't any extra spaces or padding added automatically (not without possibly having spaces in your actual data)

Do the IDs exist in both dataset? Are they both the same datatype (numbers?) Have you manually filtered for the same ID in both datasets to find it in both? Are you joining based on any other criteria in your join or is it just based on the ID?

Jones01

Hi,

Sorry I didn't explain myself properly. The data doesn't align to the column names so the data is misaligned by 1 column after a certain point. As in the value and the column doesn't match.

I deleted the data and pulled the data again and it worked fine. Very odd.

MichelleH

@Jones01 Is your dataset set to Append? I've encountered this in the past when adding or subtracting columns and Domo adds data in the same column position, rather than by column name.

Jones01

@MichelleH it was just a replace. query was selecting from two tables.

MichelleH

@Jones01 Have you made any changes to your query recently? Or is there a UNION in your query?

Jones01

it was a brand new connection and query. no union just a join.

can't replicate it now unfortunately.

MichelleH

@Jones01 If you're unable to replicate it then it's possible it was just a temporary issue with the data preview.

Jones01

I've just found this has happened again. I was debugging the values in a visualisation as they looked wrong, checking joins etc and found the data doesn't line up with the columns once imported into the dataset.

This is crazy.

I am selecting from two tables and joining with a column called zone_id.

The dataset has the columns for the zone_id from the first table and the second table but the headers don't have the second zone_id so after the join key it all steps out. 

I would have thought the headers are correct and the column with the values should be removed.

RobSomers

@Jones01 Are you doing the join in an ETL, a dataset view, or prior to importing into Domo?

Jones01

prior to importing into domo.

I fixed the issue by aliasing the second zone id. s.zone_id as secondzoneid.

Shouldn't really have to do that and select * should just work,

RobSomers

@Jones01 Having two columns named the same would create a problem. When they're imported into Domo, your query has p.zone_id and s.zone_id. But when the data gets imported, since the p and the s are only used to identify which zone_id is which within your query, the column names are actually just zone_id and zone_id. Since they're named the same, Domo will think they're the same column and have trouble.

Jones01

I can understand it may have a problem but it shouldn't fail silently and then impact the integrity of the data.

It should either rename the second key to zone_id_1 or as it currently leaves out the column header it should remove the entire column as well so the data isn't misaligned.

MichelleH

@Jones01 Selecting two columns of the same name is bound to cause issues since SQL operates based off of the field names.

In these situations, I would recommend specifying which columns you need from the right table like this:

select
A.*,
B.`Column 1`,
B.`Column 2`
from `Table A` as A
left join `Table B` as B
on A.`id` = B.`id`

Jones01

@MichelleH thanks. Yes I aliased the duplicate column name and that made everything worked.

Not sure I agree that it is bound to cause issues as when the data is being imported the situation could be quite easily handled programmatically.

I think we'll just stop using select * for now.

Thanks again