From mboxrd@z Thu Jan 1 00:00:00 1970 From: Myles English Subject: Bug: babel sql: no results are returned if the first would be NULL [7.5] Date: Tue, 5 Apr 2011 21:03:41 +0100 Message-ID: <20110405210341.792dc3dc@bill.home> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from [140.186.70.92] (port=38717 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1Q7CSA-0002GT-TO for emacs-orgmode@gnu.org; Tue, 05 Apr 2011 16:02:20 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1Q7CS9-0004U4-1f for emacs-orgmode@gnu.org; Tue, 05 Apr 2011 16:02:18 -0400 Received: from mail-ww0-f41.google.com ([74.125.82.41]:38008) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1Q7CS8-0004Tw-LB for emacs-orgmode@gnu.org; Tue, 05 Apr 2011 16:02:16 -0400 Received: by wwi18 with SMTP id 18so3077661wwi.0 for ; Tue, 05 Apr 2011 13:02:15 -0700 (PDT) List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: emacs-orgmode@gnu.org Hello, When returning results from a postgresql database, if the first value is NULL then no results are returned. Versions: orgmode git commit c01c2ad Fri Mar 18, postgresql 9.0.3. This works as expected: #+begin_src sql :engine postgresql :cmdline -d postgres SELECT 1 as A, NULL as B; #+end_src #+results: | a | b | | 1 | | #+begin_src sql :engine postgresql :cmdline -d postgres SELECT 1 as A, 1 as B; #+end_src #+results: | a | b | | 1 | 1 | When the first value is NULL then only the column names are returned: #+begin_src sql :engine postgresql :cmdline -d postgres SELECT NULL as A,1 as B; #+end_src #+results: | a | b | |---+---| whereas I would have expected this: #+begin_example | a | b | | | 1 | #+end_example A slightly unsatisfactory workaround using coalesce, is: #+begin_src sql :engine postgresql :cmdline -d postgres SELECT coalesce(NULL,'NULL') as A,1 as B; #+end_src #+results: | a | b | | NULL | 1 | Also, this may be unrelated, when trying to return tuples only, using "-t", this happens: #+begin_src sql :engine postgresql :cmdline -d postgres -t SELECT NULL as A,1 as B; #+end_src #+results: | | | | | | |---+---+---+---+---| | | | | | | Thanks, Myles Emacs : GNU Emacs 23.3.1 (x86_64-unknown-linux-gnu, GTK+ Version 2.22.1) of 2011-03-10 on bitzer.hoetzel.info Package: Org-mode version 7.5 current state: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D (setq org-log-done 'time org-export-preprocess-before-backend-specifics-hook '(org-inlinetask-export-handler) org-clock-in-switch-to-state 'bh/clock-in-to-next org-agenda-insert-diary-extract-time t org-export-latex-after-initial-vars-hook '(org-beamer-after-initial-vars) org-todo-keyword-faces '(("TODO" :foreground "red" :weight bold) ("NEXT" :foreground "blue" :weight bold) ("WAITING" :foreground "yellow" :weight bold) ("DONE" :foreground "forest green" :weight bold) ("MAYBE" :foreground "goldenrod" :weight bold) ("CANCELLED" :foreground "orangered" :weight bold)) org-speed-command-hook '(org-speed-command-default-hook org-babel-speed-command-hook) org-agenda-custom-commands '(("w" "Tasks waiting on something" tags "waiting/!" ((org-use-tag-inheritance nil) (org-agenda-todo-ignore-scheduled nil) (org-agenda-todo-ignore-deadlines nil) (org-agenda-todo-ignore-with-date nil) (org-agenda-overriding-header "Waiting tasks")) ) ("r" "Refile New Notes and Tasks" tags "LEVEL=3D2+refile" ((org-agenda-todo-ignore-with-date nil) (org-agenda-todo-ignore-deadlines nil) (org-agenda-todo-ignore-scheduled nil) (org-agenda-overriding-header "Refilable tasks")) ) ("N" "Notes" tags "note" ((org-agenda-overriding-header "Notes"))) ("n" "Next" tags-todo "-WAITING-CANCELLED/!NEXT" ((org-agenda-overriding-header "Next Tasks"))) ("u" "Unscheduled Next" tags-todo "-WAITING-CANCELLED-SCHEDULED=3D{.}/!NEXT" ((org-agenda-overriding-header "Unscheduled NEXT items: "))) ("p" "Projects" tags-todo "LEVEL=3D2-refile|LEVEL=3D1+refile/!-DONE-CANCELLED-WAITING-MAYBE" ((org-agenda-skip-function (quote bh/skip-non-projects)) (org-agenda-overriding-header "Projects")) ) ("o" "Other (Non-Project) tasks" tags-todo "LEVEL=3D2-refile|LEVEL=3D1+refile/!-DONE-CANCELLED-WAITING-MAYBE" ((org-agenda-skip-function (quote bh/skip-projects)) (org-agenda-overriding-header "Other non-project tasks")) ) ("A" "Archivable tasks" tags "LEVEL=3D2-refile/DONE|CANCELLED" ((org-agenda-overriding-header "Tasks to Archive"))) ("h" "Habits" tags-todo "STYLE=3D\"habit\"" ((org-agenda-todo-ignore-with-date nil) (org-agenda-todo-ignore-scheduled nil) (org-agenda-todo-ignore-deadlines nil) (org-agenda-overriding-header "Habits")) ) ("#" "Stuck Projects" tags-todo "LEVEL=3D2-refile|LEVEL=3D1+refile/!-DONE-CANCELLED" ((org-agenda-skip-func= tion (quote bh/skip-non-stuck-projects)) (org-agenda-overriding-header "Stuck projects")) ) ("c" "Select default clocking task" tags "LEVEL=3D2-refile" ((org-agenda-skip-function (quote (org-agenda-skip-subtree-if (quote notregexp) "^\\*\\* Organisation"))) (org-agenda-overriding-header "Set default clocking task with C-u C-u I")) ) ) org-agenda-files '("~/org/gtd.org" "~/org/phd.org" "~/org/rh.org" "~/org/home.org" "~/org/notes/refile.org" "~/org/diary.org" "~/org/thesisPlan/thesis_plan.org" "~/docs/relKpaper/org/model_runs.org" "~/org/papers/shm/linked_nodes.org" "~/org/papers/prov/prov.org") org-agenda-include-diary t org-babel-load-languages '((R . t) (emacs-lisp . t) (org . t) (sql . t) (python . t) (sh . t) (latex . t) (ledger . t)) org-clock-into-drawer "CLOCK" org-metaup-hook '(org-babel-load-in-session-maybe) org-export-preprocess-after-blockquote-hook '(org-special-blocks-make-special-cookies) org-after-todo-state-change-hook '(org-clock-out-if-current) org-babel-tangle-lang-exts '(("latex" . "tex") ("python" . "py") ("emacs-lisp" . "el")) org-clock-history-length 28 org-todo-state-tags-triggers '(("CANCELLED" ("cancelled" . t)) ("WAITING" ("waiting" . t)) ("MAYBE" ("waiting" . t)) (done ("waiting")) ("TODO" ("waiting") ("cancelled")) ("NEXT" ("waiting")) ("DONE" ("waiting") ("cancelled"))) outline-minor-mode-prefix "=03=0F" org-export-html-after-blockquotes-hook '(org-special-blocks-convert-html-special-cookies) org-export-blocks-postblock-hook '(org-exp-res/src-name-cleanup) org-habit-preceding-days 0 org-export-latex-format-toc-function 'org-export-latex-format-toc-default org-stuck-projects '("+project/-MAYBE-DONE" ("TODO" "STARTED") nil "\\") org-export-latex-after-blockquotes-hook '(org-special-blocks-convert-latex-special-cookies) org-clock-in-resume t org-agenda-diary-file "~/org/diary.org" org-tab-first-hook '(yas/org-very-safe-expand org-hide-block-toggle-maybe org-src-native-tab-command-maybe org-babel-hide-result-toggle-maybe) org-src-mode-hook '(org-src-babel-configure-edit-buffer org-src-mode-configure-edit-buffer) org-confirm-shell-link-function 'yes-or-no-p org-export-first-hook '(org-beamer-initialize-open-trackers) org-clock-persist 'history org-todo-keywords '((sequence "TODO(t!)" "NEXT(n!)" "|" "DONE(d!/!)") (sequence "WAITING(w@/!)" "MAYBE(m!)" "|" "CANCELLED(c@/!)")) org-agenda-before-write-hook '(org-agenda-add-entry-text) org-default-notes-file "~/org//notes.org" org-directory "~/org/" org-inlinetask-export nil org-babel-pre-tangle-hook '(save-buffer) org-cycle-hook '(org-cycle-hide-archived-subtrees org-cycle-hide-drawers org-cycle-show-empty-lines org-optimize-window-after-visibility-change) org-export-latex-classes '(("article" "\\documentclass[12pt,a4paper]{article}\n\\usepackage[utf8]{inputenc}\n\\us= epackage[T1]{fontenc}\n\\usepackage{graphicx}\n\\usepackage{longtable}\n\\u= sepackage{hyperref}" ("\\section{%s}" . "\\section*{%s}") ("\\subsection{%s}" . "\\subsection*{%s}") ("\\subsubsection{%s}" . "\\subsubsection*{%s}") ("\\paragraph{%s}" . "\\paragraph*{%s}") ("\\subparagraph{%s}" . "\\subparagraph*{%s}")) ("report" "\\documentclass[11pt]{report}\n\\usepackage[utf8]{inputenc}\n\\usepackage[= T1]{fontenc}\n\\usepackage{graphicx}\n\\usepackage{longtable}\n\\usepackage= {hyperref}" ("\\part{%s}" . "\\part*{%s}") ("\\chapter{%s}" . "\\chapter*{%s}") ("\\section{%s}" . "\\section*{%s}") ("\\subsection{%s}" . "\\subsection*{%s}") ("\\subsubsection{%s}" . "\\subsubsection*{%s}")) ("book" "\\documentclass[11pt]{book}\n\\usepackage[utf8]{inputenc}\n\\usepa= ckage[T1]{fontenc}\n\\usepackage{graphicx}\n\\usepackage{longtable}\n\\usep= ackage{hyperref}" ("\\part{%s}" . "\\part*{%s}") ("\\chapter{%s}" . "\\chapter*{%s}") ("\\section{%s}" . "\\section*{%s}") ("\\subsection{%s}" . "\\subsection*{%s}") ("\\subsubsection{%s}" . "\\subsubsection*{%s}")) ) org-publish-project-alist '(("orgfiles" :base-directory "~/org/notes/html/" :recursive t :base-extension "org" :publishing-directory "~/docs/html/currentResearch/" :publishing-function org-publish-org-to-html :headline-levels 3 :auto-preamble t :auto-postamble t) ("images1" :base-directory "~/org/RR/1D_horiz/img/" :recursive nil :base-extension "png" :publishing-directory "~/docs/html/currentResearch/1D_horiz/img/" :publishing-function org-publish-attachment) ("images2" :base-directory "~/org/RR/richards_M/img/" :recursive nil :base-extension "png" :publishing-directory "~/docs/html/currentResearch/richards_M/img/" :publishing-function org-publish-attachment) ("images3" :base-directory "~/org/RR/flux/img/" :recursive nil :base-extension "png" :publishing-directory "~/docs/html/currentResearch/flux/img/" :publishing-function org-publish-attachment) ("images4" :base-directory "~/org/RR/1D_horiz_dev/img/" :recursive nil :base-extension "png" :publishing-directory "~/docs/html/currentResearch/1D_horiz_dev/img/" :publishing-function org-publish-attachment) ("images5" :base-directory "~/org/RR/linkbm/img/" :recursive nil :base-extension "png" :publishing-directory "~/docs/html/currentResearch/linkbm/img/" :publishing-function org-publish-attachment) ("website" :components ("orgfiles" "images1" "images2" "images3" "images4" "images5")) ) org-export-preprocess-before-normalizing-links-hook '(org-remove-file-link-modifiers) org-mode-hook '((lambda nil (if (member "REFTEX" org-todo-keywords-1) (org-mode-article-modes))) (lambda nil (make-variable-buffer-local (quote yas/trigger-key)) (setq yas/trigger-key [tab]) (add-to-list (quote org-tab-first-hook) (quote yas/org-very-safe-expand)) (define-key yas/keymap [tab] (quote yas/next-field))) (lambda nil (flyspell-mode 1)) org-clock-load turn-on-font-lock #[nil "\300\301\302\303\304$\207" [org-add-hook change-major-mode-hook org-show-block-all append local] 5] #[nil "\300\301\302\303\304$\207" [org-add-hook change-major-mode-hook org-babel-show-result-all append local] 5] org-babel-result-hide-spec org-babel-hide-all-hashes) org-clock-out-remove-zero-time-clocks t org-font-lock-hook '(org-inlinetask-fontify) org-global-properties '(("Effort_ALL" . "0:10 0:30 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00")) org-refile-targets '((org-agenda-files :maxlevel . 5) (nil :maxlevel . 5) ("~/org/notes/allnotes.org" :maxlevel . 5)) org-ctrl-c-ctrl-c-hook '(org-babel-hash-at-point org-babel-execute-safely-maybe) org-confirm-elisp-link-function 'yes-or-no-p org-refile-use-outline-path 'file org-agenda-mode-hook '((lambda nil (hl-line-mode 1))) org-export-interblocks '((lob org-babel-exp-lob-one-liners) (src org-babel-exp-inline-src-blocks)) org-clock-out-hook '(bh/clock-out-maybe) org-refile-allow-creating-parent-nodes 'confirm org-occur-hook '(org-first-headline-recenter) org-from-is-user-regexp nil org-habit-following-days 0 org-table-use-standard-references '(quote from) org-drawers '("PROPERTIES" "LOGBOOK" "CLOCK") org-export-preprocess-before-selecting-backend-code-hook '(org-beamer-select-beamer-code) org-confirm-babel-evaluate nil org-modules '(org-bbdb org-bibtex org-docview org-gnus org-info org-jsinfo org-habit org-inlinetask org-irc org-mew org-mhe org-rmail org-special-blocks org-vm org-wl org-w3m) org-columns-default-format "%80ITEM(Task) %10Effort(Effort){:} %10CLOCKSUM" org-clock-report-include-clocking-task t org-export-latex-final-hook '(org-beamer-amend-header org-beamer-fix-toc org-beamer-auto-fragile-frames org-beamer-place-default-actions-for-lists) org-metadown-hook '(org-babel-pop-to-session-maybe) org-export-blocks '((src org-babel-exp-src-block nil) (comment org-export-blocks-format-comment t) (ditaa org-export-blocks-format-ditaa nil) (dot org-export-blocks-format-dot nil)) )