Query works or not, depending on FROM order

Question

question

Mischa Spelt asked Jun 06 2017 at 10:36 AM anthony.johnson commented Jul 12 2017 at 9:03 PM

Query works or not, depending on FROM order

I ran into a weird issue where a query that I knew should give me some rows returned an empty result set.

After a lot of trial-and-error, I found out that swapping the order of the tables in the FROM statement fixed the problem.

Is this a bug?

In the attached model, emptyresultset.fsm, run the Script to see the empty result set in the DEBUG table. If you swap the order of the tables in the FROM, as per the comment at the bottom of the script, you will get one row.

Software Version:

FlexSim 17.1.2

bug report query sql join

emptyresultset.fsm (20.6 KiB)

Answer 1 · 2017-06-08T05:19:31Z

anthony.johnson answered Jun 08 2017 at 5:19 AM anthony.johnson commented Jul 12 2017 at 9:03 PM

Yes, this is a bug. We'll get it fixed for the next release.

· 2

Mischa Spelt commented · Jul 12 2017 at 9:58 AM

@anthony.johnson did you mean 17.1.3? Because the demo model attached to the original post still does not give consistent results for both table orders.

0 ·

anthony.johnson ♦♦ Mischa Spelt commented · Jul 12 2017 at 9:03 PM

Mischa,

Yes, you are correct, and we managed, in fixing one bug, to introduce another more pernicious one. We will make a new release that fixes this by the end of this week. I apologize for the hassle that this has caused you, and thank you for your patience.

Also, our 17.2 beta, which should be out in the next day or two, includes yet more significant changes to our sql parser engine. Among other minor things, we have added the ability to use FlexScript dot syntax in SQL queries, as well as added support for SQL window functions. Since our SQL parser is apparently still pretty bug-prone, and since Talumis is one of our most advanced users of the SQL capabilities, could you please make sure to try running some of your SQL-using models in the beta when it comes out, and let us know if you run into any problems? That would really be appreciated. Thanks.

1 ·

question