AS keyword within sql statement not working

[sintaq83]

hi @CasN

At the Field Definition for Output Query, when i try to use AS keyword, the field's value is not populate accordingly. However, if I remove the AS keyword, the value populate just fine.

Sample my query statement: from_unixtime(bugtable.last_updated) AS UpdateOn

DB: MariaDB Type: Query User/Admin: User

[CasN]

Hi Sintaq83, are you creating a query or a script? It seems to be caused by the function somehow. I need to do some more testing. If you use a field with out function, the keyword works perfectly.

[sintaq83]

Hi Cas, Its a query

On Mon, 31 Jul 2023, 11:03 pm CasN, @.***> wrote:

Hi Sintaq83, are you creating a query or a script? It seems to be caused by the function somehow. I need to do some more testing. If you use a field with out function, the keyword works perfectly.

— Reply to this email directly, view it on GitHub https://github.com/mantisbt-plugins/Query/issues/1#issuecomment-1658554874, or unsubscribe https://github.com/notifications/unsubscribe-auth/ATAQCLAKJCRM4QKUINRQQ53XS7CLXANCNFSM6AAAAAA26HMR6U . You are receiving this because you authored the thread.Message ID: @.***>

[CasN]

It will work as long as you do not use capitals in the AS-value. So "from_unixtime(last_updated) as updated_on" will work fine where "from_unixtime(last_updated) as Updated_on" will fail. Have not yet been able to find out why that is. I keep thinking about it but at least for now you know how to deal with this.

[sintaq83]

Hi @CasN

I can confirmed your resolution works. Weird 😅

Anyway, thanks for your swift help

On Tue, 1 Aug 2023, 7:43 pm CasN, @.***> wrote:

Closed #1 https://github.com/mantisbt-plugins/Query/issues/1 as completed.

— Reply to this email directly, view it on GitHub https://github.com/mantisbt-plugins/Query/issues/1#event-9975501982, or unsubscribe https://github.com/notifications/unsubscribe-auth/ATAQCLGEP6WYFW3TOZ6AFYTXTDTXTANCNFSM6AAAAAA26HMR6U . You are receiving this because you authored the thread.Message ID: @.***>

[dregad]

Have not yet been able to find out why that is.

This is because MantisBT database API forces column names to lowercase

So you need to apply strtolower here https://github.com/mantisbt-plugins/Query/blob/6d34d1118f19445b5a6803b63c4b434d02ee3547/pages/exec_query.php#L108

[CasN]

Updated this in version 2.15

[dregad]

Not fixed.

I meant $content .= $row2[strtolower( trim( $name ) )] ;

[CasN]

Changed accordingly in version 2.16

[dregad]

Tested OK