Can't update SQL query

Bug reports
1

Hello, I'm trying to modify a SQL query from which I feed several questions and charts.

This SQL query, as is right now, produces an error since it includes a subquery in a SELECT statement that returns 2 elements for a particular record due to some misconfiguration in the source system.

I began investigating this error and found out the "dirty" record. I wanted to update the query to handle the cases in which this subquery returns +1 results as I don't have access to modify the persisted data.

On Monday 15, the query execution failed and showed the error message refering to the subquery with +1 results as expected.

However, now when I executed the query it only displayes the message Here's where your results will appear and if I modify the code and execute it again, the code simply reverts back to its previous state, even if I execute some script that should fail (like typing random characters).

I copied the query to another SQL query file but when it executes it displays the error message cannot read properties of undefined (reading 'rows')

I recorded a video with the steps I'm describing:
Steps

Thanks for your time

2

Hey Edwin

Not sure about the first part as it can be something cache related in regards to that question.
Does anything show up in Admin Settings → Troubleshooting → Logs in reference to this question.
Could you provide the information in Admin Settings → Troubleshooting → Help → Diagnostic Info?

On the error, since you’ve identified the dirty record, do you get different errors if you try to query just that record?
Do you get similar errors with the same query in other editors like DBeaver and Datagrip?

Thanks.

3

Hi, here's the Diagnostic Info:

{
  "browser-info": {
    "language": "es-419",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.23+9",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.23",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.23+9",
    "os.name": "Linux",
    "os.version": "5.10.219-208.866.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "postgres",
      "mysql",
      "bigquery-cloud-sdk",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "14.10"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.7.2"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2024-06-25",
      "tag": "v1.49.18",
      "hash": "fd3765d"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

As per the logs it shows:

{
:status :failed,
   :class clojure.lang.ExceptionInfo,
   :error "Error reducing result rows: Subquery returns more than 1 row",
   :stacktrace
   .... (long stacktrace)
:card_id 334,
 :context :question,
 :error "Subquery returns more than 1 row",
 :row_count 0,
 :running_time 0,
 :data {:rows [], :cols []}
}

The query produces the subquery +1 error as expected, however the SQL query file itself is like frozen and doesn't allow me to update the code to fix the +1 results error.

On the error, since you’ve identified the dirty record, do you get different errors if you try to query just that record?

Using the same SQL query file in Metabase, I can't update the code to try only with this record. I have to create a different SQL query file. The only error it produces is a subquery +1 results error.

Do you get similar errors with the same query in other editors like DBeaver and Datagrip?

Yes, the error arises from a data anomally that shouldn't have happend, but it did anyways. Problem is I want to modify the query to simply add a MAX() in the subquery to get any of the multiple records as it doesn't really matter what the record is, only that it exists

As it's shown in the video I shared, any modification I try on the SQL query file is discarded when I run the query and the Save button doesn't show up. The file seems to be locked somehow.