Bug 46198 - SQL: NULL not accepted in place of column name in SELECT statement
Summary: SQL: NULL not accepted in place of column name in SELECT statement
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Lionel Elie Mamane
QA Contact:
URL:
Whiteboard: target:3.6.0 target:3.5.1
Keywords:
Depends on:
Blocks: 46207 46206
  Show dependency treegraph
 
Reported: 2012-02-16 15:41 UTC by Jason S
Modified: 2012-03-24 09:52 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Jason S 2012-02-16 15:41:12 UTC
http://stackoverflow.com/questions/4770466/sql-dialect-in-openoffice-base/9315951#9315951

I'm familiar with SQL in SQLite and MySQL, but OpenOffice Base seems to be either very crippled, or I don't understand how to execute raw SQL.

I want to do (effectively) this:

INSERT INTO t2 SELECT NULL as id, t.foo, t.bar, '' as baz, 0 as quux 
  FROM MyTable t

All I can do is

SELECT t.foo, t.bar, '' as baz, 0 as quux FROM MyTable t

because the NULL seems to give Base confusion, as does the INSERT INTO T2 SELECT ... syntax.

Can anyone suggest how I need to fix this?
Comment 1 Lionel Elie Mamane 2012-02-16 19:45:02 UTC
Indeed, NULL is not accepted by the LibreOffice SQL parser in place of a column name in a SELECT statement. That's a bug. I'll try to get it fixed for 3.5.1, else for 3.5.2.

In the meantime, I think this syntax should work:

INSERT INTO t2 (foo, bar, baz, quux) SELECT t.foo, t.bar, '' as baz, 0 as quux FROM MyTable t

Where "(foo, bar, baz, quux)" refers to column names in the t2 table (in case they differ from the names in the SELECT statement).
Comment 2 Lionel Elie Mamane 2012-02-16 20:51:31 UTC
(In reply to comment #1)

> In the meantime, I think this syntax should work:
> 
> INSERT INTO t2 (foo, bar, baz, quux) SELECT t.foo, t.bar, '' as baz, 0 as quux FROM MyTable t

Ah no, "INSERT INTO (...) SELECT" is not supported at all. I had missed that you have *two* problems in my original reading. Cloned the bug for the second issue.
Comment 3 Not Assigned 2012-02-17 01:29:50 UTC
Lionel Elie Mamane commited a patch related to this issue to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6f3ebfc4c78da67f72befa3bde55dde4d77d5e3a

fdo#46198: NULL is a perfectly fine general_value
Comment 4 Drew Jensen 2012-02-18 11:06:26 UTC
EMPTY is the string recognized by the the built in parser - good to see it finally also recognizes NULL for those back ends that support it.
Comment 5 Not Assigned 2012-02-21 06:18:47 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-3-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3a61c9f1c51893b242ccf5bb20163b72721a2bf0&g=libreoffice-3-5

fdo#46198: NULL is a perfectly fine general_value


It will be available in LibreOffice 3.5.1.
Comment 6 Rainer Bielefeld Retired 2012-03-24 09:52:43 UTC
Fix is in 3.5, 3.6, there will be no 3.4.7, so I closed tis one. Please feel free to reopen if I did wrong.