Can't Run Query with UNION ALL

I have data base ,Vertica,
I create tableC & tableB with this value:

CREATE TABLE tst.tableC(id int,ts timestamptz,cl1_ varchar(30),cl2_ varchar(30),cl3_ varchar(30));
   INSERT INTO tst.tableC VALUES(1, '2020-12-22', 'value1', 'value2', 'value3');
   INSERT INTO tst.tableC VALUES(1, '2020-12-23', 'value4', 'value5', 'value6');
  INSERT INTO tst.tableC VALUES(1, '2020-12-24', 'value7', 'value8', 'value9');

 CREATE TABLE tst.tableB( id int, w1_ varchar(30),w2_ varchar(30),w3_ varchar(30));
  INSERT INTO tst.tableB VALUES(1, 'b1','b2', 'b3');

this Query don’t Run at all :

WITH
list1 AS (SELECT ‘id’ AS ’ ',‘ts’ AS ’ ',(SELECT w1_ FROM tst.tableB LIMIT 1) AS ’ ',
(SELECT w2_ FROM tst.tableB LIMIT 1) AS ’ ', (SELECT w3_ FROM tst.tableB LIMIT 1) AS ’ '),
list2 AS (SELECT c.id::varchar, c.ts::varchar , cl1_, cl2_, cl3_ FROM tst.tableC c inner join tst.tableB b on b.id=c.id),
list3 AS (SELECT * FROM list1 UNION ALL SELECT * FROM list2)
SELECT * FROM list3 ORDER BY 1 DESC;

also this one:

Select * from (SELECT ‘id’ AS ’ ',‘ts’ AS ’ ',(SELECT w1_ FROM tst.tableB LIMIT 1) AS ’ ',
(SELECT w2_ FROM tst.tableB LIMIT 1) AS ’ ', (SELECT w3_ FROM tst.tableB LIMIT 1) AS ’ ’
UNION ALL
SELECT c.id::varchar, c.ts::varchar , cl1_, cl2_, cl3_ FROM tst.tableC c inner join tst.tableB b on b.id=c.id
)d

If I run in the -vsql or Dbeaver it’s run ok.

Hi @reli
Please post “Diagnostic Info” from Admin > Troubleshooting.
And which version of Vertica, and which version of vertica-jdbc? Are you using the same JDBC driver for both Metabase and DBeaver?
When you say “don’t run at all” - what does that mean exactly? Are you getting an error, or does Metabase never send the query to Vertica, or …? Check the log for any errors - Admin > Troubleshooting > Logs.

hi, I run Vertica version 10.0.1-6,
and I using the same JDBC in Metabase and DBeaver.
Diagnostic Info

{
“browser-info”: {
“language”: “he-IL”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.8+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.8”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.8+10”,
“os.name”: “Linux”,
“os.version”: “5.3.0-1033-aws”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“vertica”
],
“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”: “2020-09-15”,
“tag”: “v0.36.6”,
“branch”: “release-0.36.x”,
“hash”: “cb258fb”
},
“settings”: {
“report-timezone”: null
}
}
}
“don’t run at all” I mean not responding, DOING SCIENCE…
I upluod png,
the log file.

@reli Okay, it looks like the query runs, so check the browser developer Network-tab to see the full request response. You might also have errors in the browser console. My guess is that Metabase doesn’t handle the columns that you are just calling space (AS ' '). Try giving them a name instead or another visible character like a dot (AS '.').

But when I test on 0.36.6, then I’m getting a stacktrace error, which in short says: Error reducing result rows: Input to deduplicate-cols-names does not match schema
While it just shows the “Doing Science” loading spinner forever.

If you upgrade to later releases (latest is 0.37.4), then you should see a visualization error like:
There was a problem with this visualization: e is undefined

I replace the ’ ’ with ‘.’ and it works ok, thank you!:grinning: