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
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 662
Joined: 2019-05-21 22:42
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
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 662
Joined: 2019-05-21 22:42
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
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

User avatar
a.gneady
Site Admin
Posts: 1122
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: Calendar plugin: Organize your data into beautiful calendars.
:arrow: Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
:arrow: Summary Reports plugin: Build powerful reports and charts for your AppGini apps.
:arrow: Search Page Maker: Compose user-friendly yet powerful search pages for your AppGini apps.

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 662
Joined: 2019-05-21 22:42
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
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

User avatar
a.gneady
Site Admin
Posts: 1122
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: Calendar plugin: Organize your data into beautiful calendars.
:arrow: Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
:arrow: Summary Reports plugin: Build powerful reports and charts for your AppGini apps.
:arrow: Search Page Maker: Compose user-friendly yet powerful search pages for your AppGini apps.

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 662
Joined: 2019-05-21 22:42
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
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

User avatar
a.gneady
Site Admin
Posts: 1122
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: Calendar plugin: Organize your data into beautiful calendars.
:arrow: Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
:arrow: Summary Reports plugin: Build powerful reports and charts for your AppGini apps.
:arrow: Search Page Maker: Compose user-friendly yet powerful search pages for your AppGini apps.

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 662
Joined: 2019-05-21 22:42
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
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

User avatar
a.gneady
Site Admin
Posts: 1122
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: Calendar plugin: Organize your data into beautiful calendars.
:arrow: Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
:arrow: Summary Reports plugin: Build powerful reports and charts for your AppGini apps.
:arrow: Search Page Maker: Compose user-friendly yet powerful search pages for your AppGini apps.

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 662
Joined: 2019-05-21 22:42
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
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

Post Reply