Skip to content

Weird behavior with select case when construct with GET parameter #818

Closed

Description

@nip1904

Introduction

If I use select with multiple case when construct I do not get the expected behavior. I tested with severla older versions but it has the same problem. Tried some workaround but this time I have no clue to mitigate the problem.
This is no special problem with one component and testing against a database works as expected.

To Reproduce

List of steps to reproduce the behavior. Include the sql file you are using and the eventual relevant parts of your database schema

select 
    'text'                       as component;
select 
   'The value of y: ' || $y      as contents;
select 
   case 
      when $x = 't1' 
         then 'value of x:  ' || $x || ' - value of y: ' || $y
      when $x = 't2' 
         then 'value of x:  ' || $x || ' - value of y: ' || $y
      else 
         'Unknown'
   end                           as contents,
   TRUE                          as break;
--
select
   'divider' as component;
--
select 
    'text'                       as component;
select 
   'The value of y is ' || $y    as contents;
select 
   case $x
      when 't1' 
         then 'value of x:  ' || $x || ' - value of y: ' || $y
      when 't2'     
         then 'value of x:  ' || $x || ' - value of y: ' || $y
      --when $z = 't3'
      -- then 'text t3: ' || $y
      else 
         'Unknown'
   end                           as contents,
   TRUE                          as break;

--
select
   'divider' as component;
-- 
select
   'debug' as component;
   select $x as x;
   select $y as y;
   --select $z as z;

Actual behavior

Different output - I attache some screenshots

your error message here

Screenshots

Image

Image

Image

Image

Expected behavior

A clear and concise description of what you expected to happen.

Version information

  • OS:
  • Database [e.g. SQLite, Postgres]
  • SQLPage Version [found when hovering the default footer of pages]:

Additional context

Add any other context about the problem here.

Activity

  1. lovasoa commented on Feb 20, 2025

    @lovasoa
    Collaborator

    Thanks for the report ! I can confirm the bug and I'll work on it.

    Minimal reproduction:

    select 
       case 
          when $x = 't1' 
             then 'value of x:  ' || $x || ' - value of y: ' || $y
          when $x = 't2' 
             then 'blah'
       end;

    and from the logs:

    [2025-02-20T14:45:39.075Z DEBUG sqlpage::webserver::database::execute_queries] Preparing statement: SELECT CASE WHEN CAST(? AS TEXT) = 't1' THEN 'value of x:  ' || CAST(? AS TEXT) || ' - value of y: ' || CAST(? AS TEXT) WHEN CAST(? AS TEXT) = 't2' THEN 'blah' END;
    [2025-02-20T14:45:39.075Z DEBUG sqlpage::webserver::database::execute_queries]  parameter 1: t1
    [2025-02-20T14:45:39.075Z DEBUG sqlpage::webserver::database::execute_queries]  parameter 2: t1
    [2025-02-20T14:45:39.075Z DEBUG sqlpage::webserver::database::execute_queries]  parameter 3: t1
    [2025-02-20T14:45:39.075Z DEBUG sqlpage::webserver::database::execute_queries]  parameter 4: debug_test
    

    the order of parameters is incorrect

  2. lovasoa commented on Feb 22, 2025

    @lovasoa
    Collaborator

    Ok, it was more work than expected, but the issue is finally fixed. Thank you very much for surfacing this issue, @nip1904. It would have been frustratingly hard to debug for final users.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingmysqlMySQL-specific issuessqlite

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions