Custom column empty with filter

Get Help
#1

Hello,

On a Table view, when I create a Custom Column via the Editor, and then I click on the Preview symbol, it displays the result as expected.
But as soon as I add a Filter just below, and click on Preview or Visualize, the column is empty.

Metabase_custom_column
Metabase_custom_column.png443×595 15.7 KB

Could you explain me why?
Is there a way to fix that?

Thank you.

#2

Hi @Noosymer
Post "Diagnostic Info" from Admin > Troubleshooting.

I cannot reproduce:

image
image.png1920×971 91.6 KB

But it sounds like you might be seeing this issue, though, not really with the details you're giving:
https://github.com/metabase/metabase/issues/14193

#3

Hi @flamber
Here is the result.

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.81 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.12+7",
    "java.vendor": "Eclipse Foundation",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.12",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.12+7",
    "os.name": "Linux",
    "os.version": "4.14.244-vps-grsec-zfs-classid",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "mysql"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "1.4.197 (2018-03-18)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "1.4.197 (2018-03-18)"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2021-10-06",
      "tag": "v0.41.0",
      "branch": "release-x.41.x",
      "hash": "c529fe2"
    },
    "settings": {
      "report-timezone": null
    }
  }
}
#4

@Noosymer Okay, I don't understand the problem. Can you include the SQL by clicking "View the SQL" in the Notebook editor.

#5

@flamber

Here is the SQL view:

SELECT `source`.`creation_date` AS `creation_date`, `source`.`job_name` AS `job_name`, `source`.`state` AS `state`, `source`.`job_id` AS `job_id`, `source`.`filtered_kwargs` AS `filtered_kwargs`, `source`.`Alert ID` AS `Alert ID`
FROM (SELECT `operate_network_script_job`.`args` AS `args`, `operate_network_script_job`.`creation_date` AS `creation_date`, `operate_network_script_job`.`job_name` AS `job_name`, `operate_network_script_job`.`state` AS `state`, `operate_network_script_job`.`job_id` AS `job_id`, `operate_network_script_job`.`filtered_kwargs` AS `filtered_kwargs`, substring(regexp_substr(`operate_network_script_job`.`args`, 'alertId'': ''([0-9a-f]+-){5}[0-9a-f]+'), 12, 50) AS `Alert ID` FROM `operate_network_script_job`) `source`
WHERE (`source`.`job_name` <> 'opsgenie.task_launcher'
    OR `source`.`job_name` IS NULL)
LIMIT 1048575
#6

@Noosymer I'm guessing that you don't have any data in your args column that will match the output.
You can run the query directly on your MySQL server as a test.

#7

@flamber But if I remove the filter, it works.

#8

@Noosymer Okay, what happens why you run the query directly on your MySQL?
There's a known issue with how MySQL behaves with substring, but only when using 0 as starting point.
https://github.com/metabase/metabase/issues/12445

I don't think it will be possible to know what is going on without you providing a full example with data, which others can reproduce.

#9

Hi @flamber

I've tested directly on my MySQL DB and it doesn't work either.
As soon as I specify the WHERE statement, the Alert ID column is empty and I don't understand why.

#10

@Noosymer If it doesn't work on MySQL either, then how is Metabase supposed to show something different? The results are being sent from MySQL to Metabase.
Metabase cannot do something that the database does not support or do.
It might be a bug in your MySQL version, so check for updates, but it's difficult to say, since I cannot reproduce, so it might just be your data or the query.

You can write queries manually in Metabase: https://www.metabase.com/docs/latest/users-guide/writing-sql.html

#11

@flamber yes I know that if it doesn't work on my MySQL DB directly, it can't work in Metabase, that's not what I wanted to say.
Just answering about the situation, if you had an idea about the query...
Thanks for your help !