5.92 fatal filter issue for ALL users in TV

Please report bugs and any annoyances here. Kindly include all possible details: steps to reproduce, expected result, actual result, screenshots, ... etc.
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

5.92 fatal filter issue for ALL users in TV

Post by onoehring » 2020-11-19 17:55

Hi,

I have set a filter that has worked before and I did not change anything in the table where this occurs.

Code: Select all

http://localhost/mytable_view.php?SortField=12%20desc,13&SortDirection=desc&FilterAnd[1]=and&FilterField[1]=10&FilterOperator[1]=equal-to&FilterValue[1]=7*X1
It throws an SQL error for the regular user.

Flyspeed SQL Query (I highly recommend that tool) tells me
Invalid SELECT statement. Unecpected token ")" at line 1, pos 2805
It's the first brace here (the last in this code section), almost at the end of the SQL, the second ) left to the order keyword.

Code: Select all

/* ox.bx*rx */ <=> '7*X1' )
For me as super admin the error is more specific (I changed all fieldnames for privacy) - but I guess there is something mixed up internally (this IS the SQL produced by AG!):

Code: Select all

SELECT `x_cx`.`ID_cxNum` AS 'ID_cxNum', if(CHAR_LENGTH(`x_cx`.`ccode`)>11, concat(left(`x_cx`.`ccode`,11),' ...'), `x_cx`.`ccode`) AS 'ccode', IF(    CHAR_LENGTH(`x_lx1`.`lx_atx`), CONCAT_WS('',   `x_lx1`.`lx_atx`), '') /* Platz */ AS 'ID_lx', IF(    CHAR_LENGTH(`x_stx1`.`stx`), CONCAT_WS('',   `x_stx1`.`stx`), '') /* stx */ AS 'ID_vxstyxstxstx', IF(    CHAR_LENGTH(`x_pxe1`.`pxbezx`), CONCAT_WS('',   `x_pxe1`.`pxbezx`), '') /* px */ AS 'ID_pxe', IF(    CHAR_LENGTH(`x_pxezx1`.`pxezx`), CONCAT_WS('',   `x_pxezx1`.`pxezx`), '') /* pxzx */ AS 'ID_pxezx', `x_cx`.`Kunde` AS 'Kunde', `x_cx`.`bex` AS 'bex', IF(    CHAR_LENGTH(`x_ax1`.`ax`), CONCAT_WS('',   `x_ax1`.`ax`), '') /* ax */ AS 'ID_ax', IF(    CHAR_LENGTH(`x_bxe1`.`bx`), CONCAT_WS('',   `x_bxe1`.`bx`), '') /* bx */ AS 'ID_bxe', IF(    CHAR_LENGTH(`x_lx1`.`oxbxrx_atx`), CONCAT_WS('',   `x_lx1`.`oxbxrx_atx`), '') /* ox.bx*rx */ AS 'oxbxrx_atx', IF(    CHAR_LENGTH(`xllby1`.`IDllbY`), CONCAT_WS('',   `xllby1`.`IDllbY`), '') /* Tiefe */ AS 'IDllbY', IF(    CHAR_LENGTH(`xllbz1`.`IDllbZ`), CONCAT_WS('',   `xllbz1`.`IDllbZ`), '') /* Ebene */ AS 'IDllbZ', IF(    CHAR_LENGTH(`x_vxstyx1`.`vxstyx`), CONCAT_WS('',   `x_vxstyx1`.`vxstyx`), '') /* vxstyx */ AS 'ID_vxstyx', IF(    CHAR_LENGTH(`x_cxtyxen1`.`cxtyx_Kurz`), CONCAT_WS('',   `x_cxtyxen1`.`cxtyx_Kurz`), '') /* tyx */ AS 'ID_cxtyxen', IF(    CHAR_LENGTH(`x_cxbax1`.`cxbax_Kurz`), CONCAT_WS('',   `x_cxbax1`.`cxbax_Kurz`), '') /* bax */ AS 'ID_cxbax', IF(    CHAR_LENGTH(`x_colx1`.`Farbe`), CONCAT_WS('',   `x_colx1`.`Farbe`), '') /* Farbe */ AS 'ID_colxNum', `x_cx`.`lez` AS 'lez', `x_cx`.`llb` AS 'llb', `x_cx`.`bex_2` AS 'bex_2', COALESCE(`x_cx`.`ID_cxNum`) AS 'x_cx.ID_cxNum' FROM `x_cx` LEFT JOIN `x_lx` as x_lx1 ON `x_lx1`.`ID_lxNum`=`x_cx`.`ID_lx` LEFT JOIN `x_vxstyxstx` as x_vxstyxstx1 ON `x_vxstyxstx1`.`ID_vxstyxstx`=`x_cx`.`ID_vxstyxstxstx` LEFT JOIN `x_stx` as x_stx1 ON `x_stx1`.`ID_stxNum`=`x_vxstyxstx1`.`ID_stx` LEFT JOIN `x_pxe` as x_pxe1 ON `x_pxe1`.`ID_pxe`=`x_cx`.`ID_pxe` LEFT JOIN `x_pxezx` as x_pxezx1 ON `x_pxezx1`.`ID_pxezx`=`x_cx`.`ID_pxezx` LEFT JOIN `x_ax` as x_ax1 ON `x_ax1`.`ID_axNum`=`x_cx`.`ID_ax` LEFT JOIN `x_vxstyx` as x_vxstyx1 ON `x_vxstyx1`.`ID_vxstyx`=`x_cx`.`ID_vxstyx` LEFT JOIN `x_cxtyxen` as x_cxtyxen1 ON `x_cxtyxen1`.`ID_cxtyxenNum`=`x_cx`.`ID_cxtyxen` LEFT JOIN `x_cxbax` as x_cxbax1 ON `x_cxbax1`.`ID_cxbaxNum`=`x_cx`.`ID_cxbax` LEFT JOIN `x_colx` as x_colx1 ON `x_colx1`.`ID_colxNum`=`x_cx`.`ID_colxNum` LEFT JOIN `x_bxe` as x_bxe1 ON `x_bxe1`.`ID_bxeNum`=`x_lx1`.`ID_bxe` LEFT JOIN `xllby` as xllby1 ON `xllby1`.`IDllbY`=`x_lx1`.`IDllbY` LEFT JOIN `xllbz` as xllbz1 ON `xllbz1`.`IDllbZ`=`x_lx1`.`IDllbZ`   WHERE   1=1  and ( IF(    CHAR_LENGTH(`x_lx1`.`oxbxrx_atx`), CONCAT_WS('',   `x_lx1`.`oxbxrx_atx`), '') /* ox.bx*rx */ <=> '7*X1' ) ) order by                                         12 desc,13                                         desc LIMIT 0, 15


Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by onoehring » 2020-11-19 18:13

Hi,

just checked: If I remove the brace ), the SQL works as expected.

I was NOT able to create the filter using the regular create&save filter tools on the page itself.
And:
I was NOT able to use the filter, when I changed the column numbers to their MySQL equivalent column names.

Olaf

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by a.gneady » 2020-11-20 15:36

Hmm ... so, the steps to reproduce this bug is to define the filters then get the link via 'Save and apply filters'? I understand that when trying to define the filters you're unable to reproduce this bug ... only via the link, right?
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by onoehring » 2020-11-20 16:27

Hi,

yes, I created a link using the way you described.
But the filters also fail directly when I click the button "apply and save filters".

Olaf

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by a.gneady » 2020-11-20 16:50

OK, could you send me the AXP and a screenshot of the filters page, with filters defined please? I'll try to reproduce this issue and see what causes the SQL error.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by onoehring » 2020-11-21 11:36

Hi Ahmed,

I am sending the axp, full error SQL and a small screencast (2MB, mp4) by mail.
Thanks already.

Olaf

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by a.gneady » 2020-11-23 11:03

Hmm ... So, if I understand correctly, you have an auto-fill lookup field that populates its content from a field configured as a link, and the link is set as a filter link. I'll try to inspect this issue in detail and see how to fix it in future releases. I noticed that the filter link you pasted into notepad in the screencast contains the same 'SortDirection' parameter twice which is abnormal. Was this link taken from the 'Save filters' code? I also noticed that the parent caption field of the lookup in question, ecomo_location.OrtBereichReihe_Auto is a varchar of size 50 ... This is weird because the link itself looks much longer than 50 characters.

I'll check this issue anyway and make a fix in future releases.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by onoehring » 2020-11-25 18:15

Hi Ahmed,

sorry for my late response.

Concerning your fist note (SortDirection):
Yes, I changed the link that i initially took from the save filters page a little. The previous link looked like this:

Code: Select all

/ecomo_container_view.php?SortField=12 desc,13&SortDirection=desc&FilterAnd[1]=and&FilterField[1]=10&FilterOperator[1]=equal-to&FilterValue[1]=
Where the shown value from the current record was appended (like 7*X1)
With my adjustmenst and your note taken into account the (filter) link looks like this - but still does not do anything:

Code: Select all

/ecomo_container_view.php?SortField=ID_posY desc,ID_posZ&SortDirection=desc&FilterAnd[1]=and&FilterField[1]=OrtBereichReihe_Auto&FilterOperator[1]=equal-to&FilterValue[1]=7*X1
The field numbers match my MySQL field names. As I changed the field order in MySQL this filter was too hard to remember each time.

Concerning your second note:
I also noticed that the parent caption field of the lookup in question, ecomo_location.OrtBereichReihe_Auto is a varchar of size 50 ... This is weird because the link itself looks much longer than 50 characters.
Yes, ecomo_location.OrtBereichReihe_Auto is a textfield that is filled after insert/update of a record into ecomo_location by a script (concatenating several fields). I do not understand what you mean by "the link itself looks much longer than 50 characters". The field contains values like "7*X1". The (filter) link shall do this:
Show only records form table _container that have "7*X1" (or whatever the user has clicked on) in the column OrtBereichReihe_Auto AND sort by the two other fields ID_posY (desc) AND ID_posX (desc).
This is probably the answer to your question why the link is so long. It set's filter and sort fields at the same time (and it did work with AG <5.92 (5.9?)).

Olaf

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by a.gneady » 2020-11-26 15:43

Hmm ... that's quite a complex use case :) But I've taken note of this and will inspect the issue for a fix in future releases.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by onoehring » 2020-11-26 17:59

Hi Ahmed,

the problem is not my filter, the problem is, that the filter is not working at all (or am I the only one having problems with filter in 5.92?).

It's not working when I paste a link in the address line, nor when a filter is being created using the filter-page (the ) is making the sql fail).

Olaf

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by a.gneady » 2020-11-28 13:08

I'm unable to reproduce the issue generally on the online demo as can be seen in this screencast ... But I'll investigate it in your particular use case though:

Image

You can actually test the filter link from the above screencast yourself
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by onoehring » 2020-12-01 14:52

Hi Ahmed,

thanks for trying out. I am not sure why the filter fails me - maybe because the SQL simply does throw an error.
I did look at the generated SQL of the error (above) again.
Two things I notice:
1. When I clock on the link in the column Ort*Bereich (where it should set a filter to e.g. 7*X1) no filter is set. But all records are returned - without the sort order (even when I change the link to the valid format

Code: Select all

http://localhost/ecomo_container_view.php?SortField=ID_posY desc,ID_posZ&SortDirection=desc&FilterAnd[1]=and&FilterField[1]=OrtBereichReihe_Auto&FilterOperator[1]=equal-to&FilterValue[1]=7*X1
2. When I use the filter-creation page always (does not matter which field I am filtering on, using equal or like) the SQL is broken (as error shown above.

As I mentioned there seems to be an additional ")" - or in other words, a missing "(".
I notice, that after the WHERE criteria you seem to place a 1=1 - isn't there supposed to be an opening "(" ? When I place this, the SQL runs fine.
ec_filter.png
ec_filter.png (2.69 KiB) Viewed 3692 times
Searching through generated application files did bring up several findings of "WHERE 1=1", so I am not so sure about my suggestion. The thing I can not get my head around is, where the extra ")" comes from (or why a "(" is missing).

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by onoehring » 2020-12-03 12:36

Hi,

I regenerated the app into a new folder using NO extra code at all.
Yes, the filters do work here. I can define a filter using the filter page and set it and/or copy the permalink to that filter. Everything works - so, well, the problem seems to be related with some of my code ...

I am checking ... seems to have something to do with my Column-Based-Field-Permissions. I will keep you updated and once I find the error I will update the CBV-P tool.

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: 5.92 fatal filter issue for ALL users in TV

Post by onoehring » 2020-12-04 12:00

Hi,

I am still looking for the solution and have a question:
When I set a filter in the filter form and then check the $options->QueryWhere it has no value. Why?
a) Where (in which variable) does AG put the filter settings?

The problem right now is, that if there are filters are set in the filter form, my CVB-permissions work, when I add an extra "(". If there are NO filters set in the filter page, CVB fails with that extra "(". Thus I think AG adds a ")" (only when a filter is set) - but without the "(".

b) I need to know, where and when AG is adding some brackets to the $options->QueryWhere . Or when do I need to set "(".

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

QUICK FIX: Re: 5.92 fatal filter issue for ALL users in TV

Post by onoehring » 2020-12-04 12:09

Hi Ahmed,

I think I found the solution ...
peebee has reported this bug before and you found a fix ... but the code of datalist.php has unfortunately not been fixed in the latest AG version (please do).
Please see the solution (your own solution/fix) here:
viewtopic.php?f=2&t=3347&p=12525&hilit= ... ere#p11759

Olaf

Post Reply