Bug with PreSQL and PostSQL (MySQL db). "Disable Auto Configure" doesn't fix my issue.

[StevePurple]

Issue:  The PreSQL and PostSQL statements are being executed in an OutPut Data tool when the workflow shouldn't even be going down that branch because a filter condition is not met.  The output data itself doesn't even happen.  When I run the workflow I can see in the results pane that the workflow correctly evaluates the condition as false and proceeds to the next appropriate tool.  But then at the end of the processing I see in the results pane where it executes the PreSQL statement of a tool in the part or the workflow that was never executed.

I'm using a 64 bit ODBC driver to connect to a MySQL db (InnoDB engine).   Alteryx version 2018.3.5.52487.

Here's what replicates the bug:

Create a workflow, add a filter and only continue the workflow on the true side.  Set the filter so that it can never be true, for example constants like 'Enabled' = 'Disabled'.  Add an Output data tool.  In the output tool specific some command in the PreSQL property.  My command was a table clear, Delete From DatabaseName.TableName, with no where condition, just delete all rows.

If you're able to replicate the bug what you'll see is that Alteryx correctly evaluated the condition as false and doesn't go down that branch.  But then after the rest of the workflow is finished it executes the PreSQL statement.

In my use case I was building a macro to load a MySQL table use the Load Data with a Local infile.  I wanted to parameterize the local infile name and path, and the database name and table.  I also wanted a yes/no option to optionally clear the table before loading it.  When I chose "Yes" to clear the table everything worked fine -- the SQL didn't get executed a second time.  But when I chose "No" that's when the entire workflow completed (I loaded the table, but didn't clear first) and then the PreSQL SQL was executed, clearing the table that I just filled.

Thanks in advance!

Steve

[Thableaus · Accepted solution]

Hi @StevePurple 

If you posted an image of your workflow maybe we could help you better.

But I might guess what's going on.

When you use a Filter Tool, don't fool yourself: if a condition is not met entirely through your dataset, that doesn't mean the flow is going to stop over there.

An empty table is carried through that stream, even if there are no records on it. So that might be the problem.

Cheers,

[StevePurple]

Wow!  Thanks for your response, I didn't know an empty stream is carried through.  Your hunch was right, I figured that branch would simply stop.

I ultimately changed the "Delete From db.table" statement so that it included a where statement which would either always be true or always be false to correspond with yes/no parameter to clear the table.  In other words, I got my macro working the way I wanted.

Is there a way to simply stop the stream?

FYI, the subject text is limited to a certain number of characters, I had originally included "Suspected bug" but I wanted to get the Disable Auto Configure wording in there because I saw that come up when I was searching for an answer to my problem.

Anyway, thanks for your response!

Steve

[Thableaus]

@StevePurple 

Yes, there is with the Message Tool.

If you can see clearly, there is an option under "Message type" that stops records from going through the tool.

So you can set up a condition and if this condition is true, then the flow will stop and throw an error.

Otherwise, if it's false, the records will come through.

Cheers,

[StevePurple]

OK, thanks again!  Good to know.  But I realized I think there is still a problem with Alteryx ...

In the two screenshots below I'm running a workflow under two scenarios. 

1)  A stream is reduced to an empty stream at the filter shown by box 1.  In this scenario you can see that the Execute load happens before the Execute Delete.

2)  In this scenario I change the filter so that it does not create an empty stream.  You can see the Execute Delete happens (as expected) before the Execute Load.

It seems my Block until Done tool is being ignored in scenario 1.

Steve

Second Instance, a non-empty stream is passed through ...

[Thableaus]

@StevePurple 

I'm not so sure how's this behavior with Block Until Done tool.

But it seems weird. I can see the Filter Tools follow the right order, I'm assuming these statements run no matter what happens.

The weirdest thing, though, is that the writing part which it should be the "Table/Query" part runs out of order too. This shouldn't happen, I guess. 

You could ask in this Knowledge Base Topic as well, that explains how the SQL statements work.

Cheers,