Empty or incomplete results when using where statement #48612
Open
Description
Describe the bug
When querying one of our tables we see empty or incomplete results. It does not happen when we query using native SQL.
Logs
Only this log line is produced when running the query.
2024-10-11 15:46:59,622 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: completed] 28.0 ms (2 DB calls) App DB connections: 1/7 Jetty threads: 3/50 (4 idle, 0 queued) (102 total active threads) Queries in flight: 0 (0 queued); mysql DB 2 connections: 0/2 (0 threads blocked) {:metabase-user-id 1}
I see these logs in mysql:
2024-10-11T12:29:57.065333Z 2556348 Query set autocommit=0
2024-10-11T12:29:57.066064Z 2556348 Query SELECT * FROM QRTZ_SCHEDULER_STATE WHERE SCHED_NAME = 'MetabaseScheduler'
2024-10-11T12:29:57.067092Z 2556348 Query UPDATE QRTZ_SCHEDULER_STATE SET LAST_CHECKIN_TIME = 1728649797049 WHERE SCHED_NAME = 'MetabaseScheduler' AND INSTANCE_NAME = 'oxuyan-metabase1728648851136'
2024-10-11T12:29:57.068027Z 2556348 Query COMMIT
2024-10-11T12:29:57.073361Z 2556348 Query set autocommit=1
2024-10-11T12:29:58.653828Z 2556348 Query SELECT `session`.`user_id` AS `metabase-user-id`, `user`.`is_superuser` AS `is-superuser?`, `user`.`locale` AS `user-locale` FROM `core_session` AS `session` LEFT JOIN `core_user` AS `user` ON `session`.`user_id` = `user`.`id` WHERE (`user`.`is_active` = TRUE) AND (`session`.`id` = 'd9b71431-e08f-4042-b9c4-0ba4b5446c17') AND (`session`.`created_at` > DATE_ADD(NOW(), INTERVAL -20160 minute)) AND (`session`.`anti_csrf_token` IS NULL) LIMIT 1
2024-10-11T12:29:58.661158Z 2556348 Query SELECT * FROM `metabase_database` WHERE `id` = 2
2024-10-11T12:29:58.669329Z 2556348 Query SELECT `id`, `engine`, `name`, `dbms_version`, `settings`, `is_audit`, `details`, `timezone` FROM `metabase_database` WHERE `id` = 2
2024-10-11T12:29:58.679242Z 2558114 Query SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2024-10-11T12:29:58.679835Z 2558114 Query set @@SQL_SELECT_LIMIT=DEFAULT
2024-10-11T12:29:58.680290Z 2558114 Query SET @@session.time_zone = 'Asia/Baku'
2024-10-11T12:29:58.681443Z 2558114 Query set @@SQL_SELECT_LIMIT=2000
2024-10-11T12:29:58.682077Z 2558114 Query -- Metabase:: userID: 1 queryType: native queryHash: 6c734fbd22ba9172b573e3b570889cac829722d6ed7abc93489cf73ee024d1e8
select * from user_exams where user_id=2
2024-10-11T12:29:58.693594Z 2558114 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2024-10-11T12:29:58.694034Z 2556348 Query set autocommit=0
2024-10-11T12:29:58.696209Z 2556348 Query SAVEPOINT `ce66bfe6-f200-4e60-b1bf-5dc5ac0ad6f1`
2024-10-11T12:29:58.697282Z 2556348 Query UPDATE `query` SET `query` = '{\"database\":2,\"type\":\"native\",\"native\":{\"query\":\"select * from user_exams where user_id=2\",\"template-tags\":{}},\"middleware\":{\"js-int-to-string?\":true,\"userland-query?\":true,\"add-default-userland-constraints?\":true}}', `average_execution_time` = CAST(ROUND((0.9 * `average_execution_time`) + 2.0, 0) AS unsigned) WHERE (`query_hash` = _binary 'lsO�\"��r�s�p�����\"��z��H��>�$��') AND (`query` IS NULL)
2024-10-11T12:29:58.698607Z 2556348 Query COMMIT
2024-10-11T12:29:58.699211Z 2556348 Query set autocommit=1
2024-10-11T12:29:58.701056Z 2556348 Query set autocommit=0
2024-10-11T12:29:58.701622Z 2556348 Query SAVEPOINT `dd502213-2dc4-412a-88df-2b57dcc9255e`
2024-10-11T12:29:58.702252Z 2556348 Query UPDATE `query` SET `average_execution_time` = CAST(ROUND((0.9 * `average_execution_time`) + 2.0, 0) AS unsigned) WHERE `query_hash` = _binary 'lsO�\"��r�s�p�����\"��z��H��>�$��'
2024-10-11T12:29:58.703018Z 2556348 Query COMMIT
2024-10-11T12:29:58.703523Z 2556348 Query set autocommit=1
2024-10-11T12:29:58.704472Z 2556348 Query set autocommit=0
2024-10-11T12:29:58.704996Z 2556348 Query SAVEPOINT `7a40731a-dbb8-42aa-a09b-d922e0d5384c`
2024-10-11T12:29:58.706856Z 2556348 Query INSERT INTO `query_execution` (`hash`, `database_id`, `result_rows`, `started_at`, `executor_id`, `action_id`, `cache_hash`, `native`, `pulse_id`, `card_id`, `context`, `cache_hit`, `is_sandboxed`, `running_time`, `dashboard_id`) VALUES (_binary 'lsO�\"��r�s�p�����\"��z��H��>�$��', 2, 0, '2024-10-11 16:29:58.654512', 1, NULL, NULL, TRUE, NULL, NULL, 'ad-hoc', FALSE, FALSE, 20, NULL)
2024-10-11T12:29:58.709865Z 2556348 Query COMMIT
2024-10-11T12:29:58.714342Z 2556348 Query set autocommit=1
2024-10-11T12:29:59.031993Z 2556348 Query set autocommit=0
2024-10-11T12:29:59.033044Z 2556348 Query set @@SQL_SELECT_LIMIT=1
2024-10-11T12:29:59.033756Z 2556348 Query SELECT TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'MetabaseScheduler' AND TRIGGER_STATE = 'WAITING' AND NEXT_FIRE_TIME <= 1728649829014 AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME >= 1728648899015)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC
2024-10-11T12:29:59.035391Z 2556348 Query set @@SQL_SELECT_LIMIT=DEFAULT
2024-10-11T12:29:59.035878Z 2556348 Query COMMIT
To Reproduce
I run this query:
select * from user_exams where user_id = 2But it returns empty result instead of 53 rows.
select * from user_exams where user_id = 1Returns 1 result but there are 226 rows.
Expected behavior
It should return all the available data
Screenshots
Make sure to unmute the audio:
metabase.no.or.incomplete.results.bug.mp4
Severity
Well, it casts shadow to the query results in general
Metabase Diagnostic Info
{
"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/129.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"metabase-info": {
"databases": [
"mysql"
],
"run-mode": "prod",
"plan-alias": "",
"version": {
"date": "2024-10-02",
"tag": "v0.50.28",
"hash": "3179ef2"
},
"settings": {
"report-timezone": "Asia/Baku"
},
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "8.0.30"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.7.10"
}
}
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "17.0.8+7",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "17.0.8",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "17.0.8+7",
"os.name": "Linux",
"os.version": "5.15.0-71-generic",
"user.language": "en",
"user.timezone": "Asia/Baku"
}
}